baig772 baig772 - 1 month ago 11
MySQL Question

Laravel - Mysql Where Not In join

I have three tables i.e.

users
,
galleries
,
orders
. the structure is simple. both
galleries
and
orders
have a column named
client_id
acting as a foreign key for
users

I need a result set with the list of only those
users
that are present in
galleries
but not in
orders
.
I have tried following but it gives me all the users that are present in
orders


$users = DB::table('galleries')
->join('users', 'users.id', '=', 'galleries.client_id')
->join('orders', 'orders.client_id', '=', 'users.id')
->select('galleries.client_id', 'galleries.path', 'users.first_name', 'users.last_name', 'orders.order_number')
->where('galleries.session_id', null)
->where('galleries.is_video', '1')
->where('galleries.is_thumb', '0')
->get();
dd($users);


The query produced by upper code is

select
`galleries`.`client_id`,
`galleries`.`path`,
`users`.`first_name`,
`users`.`last_name`,
users.id,
`orders`.`order_number`
from
`galleries`
inner join
`users`
on
`users`.`id` = `galleries`.`client_id`
inner join
`orders`
on
`orders`.`client_id` = `users`.`id`
where
`galleries`.`session_id` is null
and
`galleries`.`is_video` = '1'
and
`galleries`.`is_thumb` = '0'

Answer

How about this? Not sure if it works:

DB::table('galleries')
    ->join('users', 'users.id', '=', 'galleries.client_id')
    ->select('galleries.client_id', 'galleries.path',
             'users.first_name', 'users.last_name')
    ->where('galleries.session_id', null)
    ->where('galleries.is_video', '1')
    ->where('galleries.is_thumb', '0')
    ->whereNotIn('client_id', function($query) {
        $query->select('client_id')
              ->from('orders');
    })
    ->get();

I've removed the orders.order_number because according to your requirement, you mentioned select those galleries which the user doesn't have an order. If there is no order for the client, how to join the orders?

I simply provided an additional whereNotIn which it reads as "where the client_id doesn't exist in the orders table".