DB::table('users')->leftJoin('installs', 'users.id', '=', 'installs.usuario_id')->whereNull('installs.usuario_id')->where('installs.station_id', '=', 223)->select(DB::raw('first_name || " " ||last_name as name'), 'users.id')->get();

That creates the SQL string:

select first_name || " " ||last_name as name, `users`.`id` from `users` left join `installs` on `users`.`id` = `installs`.`usuario_id` where `installs`.`usuario_id` is null and `installs`.`station_id` = ?

not working

Exception

SQLSTATE[42703]: Undefined column: 7 ERROR: no existe la columna « » LINE 1: select first_name || " " ||last_name as name, "users"."id" f... ^ (SQL: select first_name || " " ||last_name as name, "users"."id" from "users" left join "installs" on "users"."id" = "installs"."usuario_id" where "installs"."usuario_id" is null and "installs"."station_id" = ?) (Bindings: array ( 0 => 223, ))

why do you need to concat strings with mysql, relegate that work to php

only use mysql for retrieving data, not for manipulation

Hi arcollector,

I have to fill a select with the query result list and use, the perform a similar query:

$combobox = User::select(DB::raw("(first_name || ' ' || last_name) as name"),'id')->lists('name', 'id');

what is the best way to do what I need?

my approach would be this

$combobox = array_build( User::all(), function( $key, $value ) {
     return [ $key, $value->first_name . ', ' . $value->last_name ];
} ) );

in terms of performance my approach is slowest than yours, but I have abstracted this piece of logic, so whatever I need to use it or modified it, I only need to do do once, instead on every query.

I like your approach, but I find the part where

LEFT JOIN installs AS t2 ON (t1.id = t2.usuario_id AND t2.station_id = 223 )
WHERE t2.usuario_id IS NULL

User::leftJoin( 'installs', 'installs.user_id', '=', 'users.id' )
    ->where( 'installs.station_id', 223 )
    ->whereNull( 'installs.user_id' )
    ->get();

I get nothing, and if I get sql result in postgres

object(Illuminate\Database\Eloquent\Collection)#835 (1) { ["items":protected]=> array(0) { } } 

check if the column names are the correct ones in the query

also, call to dd( DB::getQueryLog() ) to see all the queries that has been executed

the name of the columns was corrected, I've never used the dd( DB::getQueryLog() );

and I'm using it so:

$combobox = User::leftJoin( 'installs', 'installs.usuario_id', '=', 'users.id' )
    				->where( 'installs.station_id','=', 223 )
    				->whereNull( 'installs.usuario_id' )
    				->get();
					
dd( DB::getQueryLog() );		

and I get

array(5) { [0]=> array(3) { ["query"]=> string(44) "select * from "users" where "id" = ? limit 1" ["bindings"]=> array(1) { [0]=> int(10) } ["time"]=> float(11.78) } [1]=> array(3) { ["query"]=> string(52) "select * from "throttle" where "user_id" = ? limit 1" ["bindings"]=> array(1) { [0]=> int(10) } ["time"]=> float(0.62) } [2]=> array(3) { ["query"]=> string(47) "select * from "stations" where "id" = ? limit 1" ["bindings"]=> array(1) { [0]=> string(3) "223" } ["time"]=> float(2.27) } [3]=> array(3) { ["query"]=> string(58) "select * from "installs" where "installs"."station_id" = ?" ["bindings"]=> array(1) { [0]=> int(223) } ["time"]=> float(0.66) } [4]=> array(3) { ["query"]=> string(154) "select * from "users" left join "installs" on "installs"."usuario_id" = "users"."id" where "installs"."station_id" = ? and "installs"."usuario_id" is null" ["bindings"]=> array(1) { [0]=> int(223) } ["time"]=> float(2.85) } } 

maybe there are no records matching that query, try it with mysql command line

select * from `users` left join `installs` on `installs`.`usuario_id` = `users`.`id` where `installs`.`station_id` = 233 and `installs`.`usuario_id` is null

with the query you indicate me not spewing any results