Laravel raw sql query

Question

select CONCAT(app_users.first_name) as display_name,COUNT(NewLeads.id)
from `app_users`
LEFT JOIN (SELECT app_leads.id, app_leads.owner_user_id from app_leads JOIN app_lead_version ON app_leads.id=app_lead_version.lead_id WHERE `app_leads`.`flag` = '1' and `app_leads`.`created_at` >= "2021-06-27 00:00:00" and `app_leads`.`created_at` <= "2021-06-28 23:59:59" ) as NewLeads ON NewLeads.owner_user_id=app_users.id
where `app_users`.`display_name` in ('Sam', 'Ash', 'Dan', 'Paul Thengilan', 'Scott')
group by `app_users`.`display_name`
order by `app_users`.`display_name` asc



$result = User::select(DB::raw('CONCAT(app_users.first_name) as display_name'),DB::raw('Count(app_leads.id) as new'))
            ->leftjoin('leads',function($join)use($time){
                $join->leftjoin('lead_version','lead_version.lead_id','=','leads.id');
                $join->on('leads.flag','=',DB::raw("'1'"));
                $join->on('leads.created_at','>=',DB::raw('"'.date("Y-m-d",strtotime($time)).' 00:00:00"'));
                $join->on('leads.created_at','<=',DB::raw('"'.date("Y-m-d",strtotime($time)).' 23:59:59"'));
                $join->on('leads.flag','=',DB::raw("'1'"));
                $join->on('leads.owner_user_id','=','users.id');

            })->whereIn('users.display_name',$user_display)->groupBy('users.display_name')->orderBy('users.display_name')->get()->toArray();

This is my raw sql query and i want to convert it into laravel sql query below is the converted laravel query but when i run both the query i get different result can anyone help me out that what is wrong with my laravel sql query

score 0 · Answer 1 · edited Jul 04 '21 at 08:20

0

One way you could debug this situation is to remove the

->get()->toArray()

from $result and log/dump the value of $result->toSql().

If you could add that output to the question it might be easier to help you.

edited Jul 04 '21 at 08:20

Toby Allen

10,997
11
73
124

answered Jul 03 '21 at 10:08

Nuno Peixoto

144
1
10

select CONCAT(app_users.first_name) as display_name, Count(app_leads.id) as new from `app_users` left join `app_leads` on `app_leads`.`flag` = '1' and `app_leads`.`created_at` >= "2019-06-27 00:00:00" and `app_leads`.`created_at` <= "2019-06-27 23:59:59" and `app_leads`.`flag` = '1' and `app_leads`.`owner_user_id` = `app_users`.`id` where `app_users`.`display_name` in (?, ?, ?, ?, ?) and `app_users`.`deleted_at` is null group by `app_users`.`display_name` order by `app_users`.`display_name` asc this is the query when i user toSql() method. – Gautam Jul 03 '21 at 14:21
I encourage you to use a SQL formatter (e.g. https://www.dpriver.com/pp/sqlformat.htm) to compare both queries and see the differences. I believe your mistake is on the second ->leftJoin, it should use a closure as well. – Nuno Peixoto Jul 03 '21 at 16:22

Laravel raw sql query

1 Answers1