gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-29 05:01:13 27 4
gpt4 key购买 nike

我有这个在 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)

最佳答案

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

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

关于php - 带有原始 SQL 语句的 Laravel querybuilder 返回语法错误,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/58853352/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com