Laravel - Querybuilder与join和concat

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透视表中匹配某个组的users表中拉出所有用户。我正在使用来自Cartalyst的哨兵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)

4 个解决方案

#1

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.

谢谢大家!如果有人遇到这种情况，他们会在这个问题上找到指导。

#2

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

#3

In your second example you have DB::raw('SELECT ...'). You need to remove the 'SELECT' keyword.

在第二个示例中，有DB: raw('SELECT…')。您需要删除'SELECT'关键字。

#4

With the Query Builder you can do:

使用查询构建器，您可以做到:

DB::table('users')->join('...')->where('...') ->lists(DB::raw('CONCAT(firstname, " ", lastname)'), 'id')

DB:表(“用户”)- >加入(…)- >(…)- >列表(DB:生(“CONCAT(firstname、”“lastname)”),“id”)

#1