How to scope the orWhere query to the original relationship w/o manually writing it

The union query works great & is correct but seems quite slower.

$first = auth()->user()
                         ->notifications()
                          ->where('data->matter_id', 7);
                            
        $notifications = auth()->user()
                            ->notifications()
                            ->whereJsonContains('data->matters', 7)
                            ->union($first)->get();

How could I get the same result but in one orWhere? this won't have the requisite clauses added like the first where clause on the relationship without manually writing them in

$notifications = auth()->user()->notifications()
                            ->where('data->matter_id', 7)
                            ->orWhere(function($query) {
                                $query->where([
                                    ['notifiable_id', auth()->user()->id],
                                    ['notifiable_type', 'App\User'],
                                    ])
                                    ->whereNotNull('notifiable_id')
                                    ->whereJsonContains('data->matters', 7);
                            })->get();

The manual query is about 0.7ms the union is around 30ms so considerably slower. Seems like over 20x slower and that probably gets worse as the table grows larger

select
  *
from
  `notifications`
where
  `notifications`.`notifiable_id` = 960f462f-6a54-42ef-971d-ea3699992dda
  and `notifications`.`notifiable_id` is not null
  and `notifications`.`notifiable_type` = App\User
  and json_unquote(json_extract(`data`, '$."matter_id"')) = 7
  or (
    (
      `notifiable_id` = 960f462f-6a54-42ef-971d-ea3699992dda
      and `notifiable_type` = App\User
    )
    and `notifiable_id` is not null
    and json_contains(`data`, 7, '$."matters"')
  )
order by
  `created_at` desc

This is what the second manual query builds

trin

5 years ago

Level 5

If we are talking about mysql performance, we need to forget about sweet php code. like about orWhere. on a large table is very slow (exception is orWhere on one field). like about JSON field type. can u post here EXPLAIN both of queries? I can tell you what awaits you under load.

edit: u added query ) great

and notifications.notifiable_id is not null in both case useless
order by created_at desc useless, use order by id desc. same result, more performance (if u have incremental id, of course)
as i says, try to not use JSON field type.

classic solution on your case is UNION like

(
	SELECT *, 'personal' AS norification_type FROM `notifications` WHERE
	`notifications`.`notifiable_id` = '960f462f-6a54-42ef-971d-ea3699992dda' AND
	`notifications`.`notifiable_type` = 'App\User' AND
	JSON_EXTRACT(data, "$.matter_id") = '7'
)
UNION
(
	SELECT *, 'group' AS norification_type FROM `notifications` WHERE
	`notifications`.`notifiable_id` = '960f462f-6a54-42ef-971d-ea3699992dda' AND
	`notifications`.`notifiable_type` = 'App\User' AND
	JSON_CONTAINS(`data`, 7, "$.matters")
)
ORDER BY id DESC
LIMIT 1000

Please or to participate in this conversation.