Laravel-具有join和concat的Querybuilder [英] Laravel - Querybuilder with join and concat
问题描述
我正在尝试从用户表中拉出与users_groups数据透视表中的特定组匹配的所有用户.我正在使用来自Cartalyst btw的Sentry 2.
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.
这可以使所有姓和名串联在一起的用户.
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.
语法错误
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)
推荐答案
Logan的答案使我朝着正确的方向开始.我还必须删除所有用户".前缀,因为我想它已经在调用用户模型了.此查询有效:
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.
这篇关于Laravel-具有join和concat的Querybuilder的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!