Query Builder Table Alias

I want to run this SQL (simplified than the real case):

UPDATE mytable mytbl
SET col = (SELECT id FROM othertable WHERE  mytbl.aaa = bbb)

tried this using query builder

DB::table('mytable', 'mytbl')
->update([  'col' => DB::table('othertable')->select('id')->whereRaw('mytbl.aaa = bbb')->first()  ])

got QueryException: Column not found: 1054 Unknown column 'mytbl.aaa' in 'where clause'... (I have checked there is aaa column in mytable)

Any idea how to fix it?

Anyway, how to get the sql from query builder update? tried to add ->toSql() but got error Call to a member function toSql() on int.

tykus

5 years ago

Level 104

DB::table('mytable as mytbl')

However, this DB::table('othertable')->select('id')->whereRaw('mytbl.aaa = bbb')->first() will return a stdClass instance, so you probably want value('id') rather than select('id')->first():

DB::table('mytable', 'mytbl')
   ->update([  'col' => DB::table('othertable')->whereRaw('mytbl.aaa = bbb')->value('id')]);

tykus

5 years ago

Level 104

Anyway, how to get the sql from query builder update? tried to add ->toSql() but got error Call to a member function toSql() on int.

update returns an int (number of rows changed) - so toSql() makes no sense.

What error message are you getting without toSql?

coder222

5 years ago

Level 1

tried to change it to value('id') but still got the same error, it seems not recognizing the alias from mytable since it's outside of the second DB::table... I guess.

coder222

5 years ago

Level 1

What error message are you getting without toSql?

Column not found: 1054 Unknown column 'mytbl.aaa' in 'where clause'

tykus

5 years ago

Level 104

it seems not recognizing the alias from mytable since it's outside of the second DB::table... I guess

Ah, yes. Why do you need an alias in any case?

coder222

5 years ago

Level 1

because that column belongs to the first table, without alias still got the same error (Unknown column) which is correct since the second table doesn't have that column.

Anyway, I want to use DB::raw for the second sql, but the problem is I need to specify the database name of the table in the real case like this:

DB::table('dbname.othertable')->select('id')

change this to

DB::raw('select id from dbname.othertable')

doesn't work ...

coder222

5 years ago

Level 1

Ha...got it working by using DB::raw and adding backticks:

DB::raw('select id from `dbname`.`othertable` where mytbl.aaa = bbb)

not sure why I just got this idea after posting here, not one hour before...

at least I can continue...

Thanks everyone!

Please or to participate in this conversation.