1

I have this SQL raw query that perfectly executes in mysql but i want to convert it to laravel querybuilder. When i do toSql(); it returns me the exact raw query. But when i get(); it returns me an syntax error.

My raw query which perfectly executes in sql and in laravel blade


            $items = \DB::select('
            SELECT users.name, users.email, users.phone, users.parent_id, users.section_id, finances.amount, finances.description, schoolyears.name, finances.date 
            from users 
            JOIN finances on users.id = finances.user_id 
            JOIN schoolyears on users.school_id = schoolyears.school_id 
            where finances.date=
            (select max(date) from finances f where finances.user_id=f.user_id)
            ');

Converted raw sql query to querybuilder that returns an syntax error

 $items = \DB::table('users')
                    ->join('finances', 'users.id','=','finances.user_id')
                    ->join('schoolyears', 'users.school_id','=','schoolyears.school_id')
                    ->select('users.name as uname','users.email','users.phone','users.section_id','finances.amount','finances.description','schoolyears.name','finances.date')
                    ->where('finances.date', '=' ,(\DB::raw("select max(`date`) from finances f where finances.user_id=f.user_id")))
                    ->get();

Error code

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in 
your SQL syntax; check the manual that corresponds to your MariaDB server 
version for the right syntax to use near 'select max(`date`) from finances f 
where finances.user_id=f.user_id' at line 1 (SQL: select `users`.`name` as 
`uname`, `users`.`email`, `users`.`phone`, `users`.`section_id`, 
`finances`.`amount`, `finances`.`description`, `schoolyears`.`name`, 
`finances`.`date` from `users` inner join `finances` on `users`.`id` = 
`finances`.`user_id` inner join `schoolyears` on `users`.`school_id` = 
`schoolyears`.`school_id` where `finances`.`date` = select max(`date`) from 
finances f where finances.user_id=f.user_id)
Improve this question
1
  • 3
    share the error Commented Nov 14, 2019 at 9:28

2 Answers 2

Reset to default
1

Try this : brackets were missing in raw query, so it is giving error.

->where('finances.date', '=' ,(\DB::raw("(select max(`date`) from finances f where finances.user_id=f.user_id)")))

Ex.

$items = \DB::table('users')
                    ->join('finances', 'users.id','=','finances.user_id')
                    ->join('schoolyears', 'users.school_id','=','schoolyears.school_id')
                    ->select('users.name as uname','users.email','users.phone','users.section_id','finances.amount','finances.description','schoolyears.name','finances.date')
                    ->where('finances.date', '=' ,(\DB::raw("(select max(`date`) from finances f where finances.user_id=f.user_id)")))
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This works for me sir. Can you explain your answer to me please?
0

Try the following:

 $items = \DB::table('users')
                    ->join('finances', 'users.id','=','finances.user_id')
                    ->join('schoolyears', 'users.school_id','=','schoolyears.school_id')
                    ->select('users.name as uname','users.email','users.phone','users.section_id','finances.amount','finances.description','schoolyears.name','finances.date')
                    ->where('finances.date', '=' ,"(" . (\DB::raw("select max(`date`) from finances f where finances.user_id=f.user_id")) . ")")
                    ->get()->toArray();
Improve this answer

4 Comments

returns an empty collection sir ` Collection {#552 ▼ #items: [] } `
And what it is supposed to return?
the fields that i selected sir.
its the same sir. it returns the same empty array. Ive got the answer already from @yasin patil. Thanks for your answers sir

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.