Query builder result not the same as SQL query result

Hello, I'm having some issues with Laravel's query builder, I don't have the same results when I run the query in phpMyAdmin using pure SQL.

Here's my query with Laravel :

$filterResult = DB::table('users')
        ->where(function ($query) use ($request) {
            $string = $request->get('search');
            $query->where('name','like','%'.$string.'%')
                ->orWhere('surname','like','%'.$string.'%')
                ->orWhere(DB::raw('CONCAT(surname, " ", name)'),'like','%'.$string.'%')
                ->orWhere(DB::raw('CONCAT(name, " ", surname)'),'like','%'.$string.'%');
        })
        ->whereNotExists(function ($query) use ($request) {
            $customer_id = (int)$request->get('customerId');
            $query->from('customer_profile_user')
                ->select(DB::raw(1))
                ->where('customer_profile_user.customer_id','=',$customer_id)
                ->where('customer_profile_user.user_id','=','users.id');
        })
        ->whereNotExists(function ($query) use ($request) {
            $customer_id = (int)$request->get('customerId');
            $query->from('admins')
                ->select(DB::raw(1))
                ->where('admins.customer_id','=',$customer_id)
                ->where('admins.user_id','=','users.id');
        })
        ->whereNotExists(function ($query) {
            $query->from('admins')
                ->select(DB::raw(1))
                ->where('admins.customer_id','=',1)
                ->where('admins.user_id','=','users.id');
        })
        ->where('users.id', '!=', Auth::user()->id);

I tried using toSql() to see if the query was good and it seemed to be okay.

Here's the SQL query :

select * from users where 
(name like "%ni%" or surname like "%ni%" or CONCAT(surname, " ", name) like "%ni%" or CONCAT(name, " ", surname) like "%ni%")
and not exists (select 1 from customer_profile_user where customer_profile_user.customer_id = 1 and customer_profile_user.user_id = users.id)
and not exists (select 1 from admins where admins.customer_id = 1 and admins.user_id = users.id)
and not exists (select 1 from admins where admins.customer_id = 1 and admins.user_id = users.id)
and users.id != 4

The problem seems to be in the and not exists parts.

Any ideas ? Thanks.

Tray2

3 years ago

Level 73

Looks like you have a bit of duplcation in your code.

I think this is the SQL you want.

SELECT
  *
FROM
  users
WHERE
  (name LIKE "%ni%" OR surname LIKE "%ni%") --This should be enough no need to contatenate and compare
  AND NOT EXISTS (
    SELECT
      1
    FROM
      customer_profile_user
    WHERE
      customer_profile_user.customer_id = 1
      AND customer_profile_user.user_id = users.id
  )
  AND NOT EXISTS ( -- one of these should be enough, no need for two,
    SELECT
      1
    FROM
      admins
    WHERE
      admins.customer_id = 1
      AND admins.user_id = users.id
  )
  AND users.id != 4

KLassiux

3 years ago

Level 1

@tray2 Thanks for your answer, I changed my query like that :

$filterResult = DB::table('users')
->where(function ($query) use ($request) {
    $string = $request->get('search');
    $query->where('name','like','%'.$string.'%')
        ->orWhere('surname','like','%'.$string.'%')
        ->orWhere(DB::raw('CONCAT(surname, " ", name)'),'like','%'.$string.'%')
        ->orWhere(DB::raw('CONCAT(name, " ", surname)'),'like','%'.$string.'%');
})
->whereNotExists(function ($query) use ($request) {
    $customer_id = (int)$request->get('customerId');
    $query->from('customer_profile_user')
        ->select(DB::raw(1))
        ->where('customer_profile_user.customer_id','=',$customer_id)
        ->where('customer_profile_user.user_id','=','users.id');
})
->whereNotExists(function ($query) use ($request) {
    $customer_id = (int)$request->get('customerId');
    $query->from('admins')
        ->select(DB::raw(1))
        ->where(function ($query) use ($customer_id) {
            $query->where('admins.customer_id','=',$customer_id)
                ->orWhere('admins.customer_id','=',1);
        })
        ->where('admins.user_id','=','users.id');
})
->where('users.id', '!=', Auth::user()->id)
->get();

But still the same result

KLassiux

3 years ago

Level 1

admins.customer_id must not be equal to 1 or the value of $customer_id

Laravel returns me this query :

select * from `users` where (`name` like %john% or `surname` like %john% or CONCAT(surname, " ", name) like %john% or CONCAT(name, " ", surname) like %john%) and not exists (select 1 from `customer_profile_user` where `customer_profile_user`.`customer_id` = 1 and `customer_profile_user`.`user_id` = users.id) and not exists (select 1 from `admins` where (`admins`.`customer_id` = 5 or `admins`.`customer_id` = 1) and `admins`.`user_id` = users.id) and `users`.`id` != 4

In this case, $customer_id is equal to 5 but it could be anything (1 included)

I really have no idea why it works with SQL but not with the query builder...

KLassiux

3 years ago

Level 1

Okay my bad, i forgot that where treats the 3rd param as a string, instead of doing :

->where('customer_profile_user.user_id','=','users.id');

I needed to do :

->where('customer_profile_user.user_id','=',DB::raw('users.id'));

Please or to participate in this conversation.