Laravel query "only if" condition

Question

Users(User/Organization/Group) has many stories as creator.
Stories has many parts of stories.
Users(User/Organization/Group) has subscribers (Another User/Organization/Group).
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
)

What do you mean with "where private == false and private == true". Where does this private comes from? — guizo
– guizo, Commented May 23, 2020 at 14:46
->whereHas('parts', function($query){$query->where('private', false)}) and i need where private == true if auth user is subscriber of creator — Strange Tin
– Strange Tin, Commented May 23, 2020 at 14:53

guizo · Accepted Answer · 2020-05-23 19:50:26Z

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.

edited May 23, 2020 at 19:50

answered May 23, 2020 at 15:57

guizo

3,2352 gold badges23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Strange Tin Over a year ago

$query - Eloquent. story doesn't exist

guizo Over a year ago

Does your stories_parts model have a story() belongsTo relationship method?

Strange Tin Over a year ago

Yes, Part has Story, Story has Creator

guizo Over a year ago

You are right. I cannot call the relationship directly from query builder. I updated my answer.

Strange Tin · Accepted Answer · 2020-05-23 19:23:02Z

       //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);

Collectives™ on Stack Overflow

Laravel query "only if" condition

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related