Query builder not working as expected

$queryUnread = $queryRead = \App\UserActivity::select($select)->orderBy('users_activities.id', 'desc');                     
                
//All Unread notifications        
$notificationsRead = $queryRead->whereNull('activities_notifiers.read_at')->get();
       
//All Read notifications
$notificationsUnread = $queryUnread->whereNotNull('activities_notifiers.read_at')->limit(5)->get();

The above doest work separately. $queryUnread executes below query

select * from activities_notifiers where read_at is null and read_at is not null order by id desc limit 5

The $queryUnread is having both whereNull and whereNotNull condition. What is the correct way of using builder especially when we have to build query based on different condition?

tykus

6 years ago

Best Answer

Level 104

There is only one query instance, and you are modifying it. SImply clone the query to get another instance of the original query:

$queryUnread = \App\UserActivity::select($select)->orderBy('users_activities.id', 'desc');      

 $queryRead = clone $queryUnread;

1 like

bobbybouwmann

6 years ago

Level 88

You have to build two completely separated queries.

$notificationsRead = \App\UserActivity::select($select)
    ->orderBy('users_activities.id', 'desc')
    ->whereNull('activities_notifiers.read_at')
    ->get();
       
$notificationsUnread = \App\UserActivity::select($select)
    ->orderBy('users_activities.id', 'desc')
    ->whereNotNull('activities_notifiers.read_at')
    ->limit(5)
    ->get();

In your example the query is build by reference. Basically $queryUnread and $queryRead point to the same reference. Because of this you see both statements in the final query.

mihirpatel83

6 years ago

Level 1

$queryUnread = $queryRead = \App\UserActivity::select($select)->orderBy('users_activities.id', 'desc');

I assume above would be creating two separate instance. Seems like a lot is left to know how Laravel works :)

Thank you @tykus @bobbybouwmann

tykus

6 years ago

Level 104

Both approaches result in two Builder instances, yes. If the base query \App\UserActivity::select($select)->orderBy('users_activities.id', 'desc') was a little more complex, then one might make a stronger argument for cloning

1 like

Please or to participate in this conversation.