php Laravel - 带有连接和连接的查询构建器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26306178/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Laravel - Querybuilder with join and concat
提问by MDS
Im trying to pull all users from the users table who match a certain group in the users_groups pivot table. Im using Sentry 2 from Cartalyst btw.
我试图从用户表中提取与 users_groups 数据透视表中某个组匹配的所有用户。顺便说一句，我使用的是 Cartalyst 的 Sentry 2。
This works to get all users with first and last name concatenated.
这可以让所有用户的名字和姓氏连接起来。
User::select(DB::raw('CONCAT(last_name, ", ", first_name) AS full_name'), 'id')
->where('activated', '=', '1')
->orderBy('last_name')
->lists('full_name', 'id');
when i try to change it to also filter users who do not belong to a certain group I get a syntax error.
当我尝试将其更改为也过滤不属于某个组的用户时，我收到语法错误。
User::select(DB::raw('SELECT CONCAT(user.last_name, ", ", user.first_name) AS user.full_name'), 'user.id', 'users_groups.group_id', 'users_groups.user_id')
->join('users_groups', 'user.id', '=', 'users_groups.user_id')
->where('user.activated', '=', '1')
->where('users_groups.group_id', '=', $group)
->orderBy('user.last_name')
->lists('user.full_name', 'user.id');
Any nudge in the right direction would be greatly appreciated.
任何朝着正确方向的推动将不胜感激。
EDIT: syntax error
编辑：语法错误
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in
your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'SELECT CONCAT(user.last_name, ", ",
user.first_name) AS user.full_name, `user`.`' at line 1 (SQL: select SELECT
CONCAT(user.last_name, ", ", user.first_name) AS user.full_name, `user`.`id`,
`users_groups`.`group_id`, `users_groups`.`user_id` from `users` inner join
`users_groups` on `user`.`id` = `users_groups`.`user_id` where
`users`.`deleted_at` is null and `user`.`activated` = 1 and
`users_groups`.`group_id` = 9 order by `user`.`last_name` asc)
回答by MDS
Logan's answer got my started in the right direction. I also had to remove all of the 'user.' prefixes since it was calling the Users model already I suppose. This query worked:
洛根的回答让我朝着正确的方向开始。我还必须删除所有“用户”。前缀，因为我想它已经在调用用户模型了。此查询有效：
User::select(DB::raw('CONCAT(last_name, ", ", first_name) AS full_name'), 'id')
->join('users_groups', 'id', '=', 'users_groups.user_id')
->where('activated', '=', '1')
->where('users_groups.group_id', '=', $group)
->orderBy('last_name')
->lists('full_name', 'id');
Thanks everyone! Hopefully if someone else runs into this they will find guidance with this question.
谢谢大家！希望如果其他人遇到这个问题，他们会找到有关此问题的指导。
回答by Majbah Habib
You do not need to select in DB::raw(), See the example
您不需要在 DB::raw() 中选择，请参阅示例
User::select(
'id',
DB::raw('CONCAT(first_name," ",last_name) as full_name')
)
->orderBy('full_name')
->lists('full_name', 'id');
回答by Logan Bailey
In your second example you have DB::raw('SELECT ...'). You need to remove the 'SELECT' keyword.
在您的第二个示例中，您有DB::raw('SELECT ...'). 您需要删除“SELECT”关键字。
回答by NinoMarconi
With the Query Builder you can do:
使用查询生成器，您可以：
DB::table('users')->join('...')->where('...')
->lists(DB::raw('CONCAT(firstname, " ", lastname)'), 'id')
DB::table('users')->join('...')->where('...')
->lists(DB::raw('CONCAT(firstname, " ", lastname)'), 'id')
