CODEWITHSUNDEEP
phpmysqllaravel
user12110431
user12110431

Reputation:

Laravel querybuilder with raw SQL statement returns syntax error

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)

Upvotes: 1

Views: 212

Answers (2)

Yasin Patel
Yasin Patel

Reputation: 5731

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)")))

Upvotes: 1

Martin Dimitrov
Martin Dimitrov

Reputation: 1304

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();

Upvotes: 0

Related Questions