Laravel - Querybuilder with joining and concat

Question

I am trying to pull all users from the user table that correspond to a specific group in the users_groups pivot table. I am using Sentry 2 from Cartalyst.

This works to get all users with a combined first and last name.

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

It would be helpful to evaluate any push in the right direction.

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) 

Answer 1

Logan's answer began in the right direction. I also had to delete all the "users". prefixes, since it already called the Users model, I suppose. This request 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 comes across this, they will find guidance with this issue.

Answer 2

You do not need to select in DB :: raw (), see example

 User::select( 'id', DB::raw('CONCAT(first_name," ",last_name) as full_name') ) ->orderBy('full_name') ->lists('full_name', 'id'); 

Answer 3

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

Answer 4

With Query Builder, you can:

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