Laravel query "only if" condition

Question

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 subscriptions (Another User/Organization/Group)
create table user_subscriptions
(
    user_id         bigint unsigned not null,
    subscription_id   bigint unsigned not null,
    subscription_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
)

Answer 1

UPDATE

    $stories = Stories::whereHas('parts', function($q) {
        $isSubscriber = $q->whereHas('story.creator.subscribers', function($q) {
            return $q->where('id', \Auth::id());
        })->get()->isNotEmpty();

        return $q->where('private', $isSubscriber);
    });

I am executing some queries inside whereHas closure.

    $stories = Story::whereHas('parts', function($query) {
        $subscribers = $query->story
            ->creator
            ->subscribers()
            ->get();

        $userIsSubscriber = $subscribers->contains(function($subscriber) {
            return $subscriber->id === Auth::id();
        });

        return $query->where('private', $userIsSubscriber);
    });

The problem here is that this can become resource expensive as it queries subscribers on each story part. Maybe you could use eager loading but I do not know exactly how your relations are implemented.

Answer 2

       //In Story model
       public function parts(): HasMany
       {
           return $this->hasMany(Part::class, 'story_id')
            ->join('stories', 'stories.id', '=', 'parts.story_id');
       }  

      ///////

       $rawQuery = '(
                CASE WHEN EXISTS (
                        SELECT * FROM `user_subscriptions`
                            WHERE `user_subscriptions`.`subscription_id` = `stories`.`creator_id`
                            AND `user_subscriptions`.`subscription_type` = `stories`.`creator_type`
                            AND `user_subscriptions`.`user_id` = ' . auth()->id() . '
                ) THEN `parts`.`private` IN (0,1)
                ELSE `parts`.`private` IN (0) END
            )';

        $stories = Story::latest('updated_at')
            ->whereHas('parts', static function ($query) use ($rawQuery) {
                $query->whereRaw($rawQuery);
            })
            ->where('status', true)
            ->with([
                'creator',
                'parts' => static function (HasMany $query) use ($rawQuery) {
                    $query->whereRaw($rawQuery);
                }
            ])->paginate(20);