samedi 23 mai 2020

Laravel query "only if" condition

  1. Users(User/Organization/Group) has many stories as creator.
  2. Stories has many parts of stories.
  3. Users(User/Organization/Group) has subscribers (Another User/Organization/Group).
  4. Part of stories may be private.

How select all stories where has parts where private == false and private == true only if auth()->user() (User/Organization/Group) is subscriber of story creator (Another User/Organization/Group).

//Stories table
create table stories
(
    id           bigint unsigned auto_increment primary key,
    creator_id   int unsigned                   not null,
    creator_type varchar(255)                   not null,
    created_at   timestamp                      null,
    updated_at   timestamp                      null
)

//Stories parts table
create table stories_parts
(
    id             bigint unsigned auto_increment          primary key,
    story_id       int                                     not null,
    private        tinyint
    created_at     timestamp                               null,
    updated_at     timestamp                               null
)

//User has subscribers (Another User/Organization/Group)
create table user_subscribers
(
    user_id         bigint unsigned not null,
    subscriber_id   bigint unsigned not null,
    subscriber_type varchar(255)    not null
)

//Organization has subscribers (Another User/Organization/Group)
create table organization_subscribers
(
    organization_id bigint unsigned not null,
    subscriber_id   bigint unsigned not null,
    subscriber_type varchar(255)    not null
)

//Group has subscribers (Another User/Organization/Group)
create table group_subscribers
(
    organization_id bigint unsigned not null,
    subscriber_id   bigint unsigned not null,
    subscriber_type varchar(255)    not null
)


via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire