php - 带有原始 SQL 语句的 Laravel querybuilder 返回语法错误

Question

我有这个在 mysql 中完美执行的 SQL 原始查询，但我想将它转换为 laravel querybuilder。当我执行 toSql(); 时，它会返回准确的原始查询。但是当我 get(); 时，它返回一个语法错误。

在 sql 和 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)
            ');

将原始 sql 查询转换为返回语法错误的查询生成器

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

错误代码

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)

Answer 1

试试这个:原始查询中缺少括号，所以它给出了错误。

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

例如

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