user13955 user13955 - 4 months ago 27
PHP Question

Eloquent ORM: Complex left join insists on using value as column name

I have the following Eloquent Query in one of my Models:

return self::where('sequence', '=', $sequence)->where('interval', '=', $minutes)
->leftJoin('wallet', 'wallet_stats.wallet_id', '=', 'wallet.id')
->leftJoin('balance', function($join) use ($uid)
{
$join->on('balance.wallet_id', '=', 'wallet_stats.wallet_id')
->on('balance.user_id', '=', $uid);
})
->orderBy('volume', 'ASC')->get(['symbol', 'name', 'volume', 'start_price', 'end_price']);


The problem I'm having with this is the following error message:


Oops! SQLSTATE[42S22]: Column not found: 1054 Unknown column '2' in
'on clause' (SQL: select
symbol
,
name
,
volume
,
start_price
,
end_price
from
wallet_stats
left join
wallet
on
wallet_stats
.
wallet_id
=
wallet
.
id
left join
balance
on
balance
.
wallet_id
=
wallet_stats
.
wallet_id
and
balance
.
user_id
=
2
where
sequence
= 0 and
interval
= 1440
order by
volume
asc)


For some reason Eloquent insists on using the value of $uid (in the 2nd "on" join condition) as a column name rather than a literal value.

Does anybody know how to get around that and have it accept a literal value in such a join specification?

Answer

I can't quite seem to replicate your error however this is what I've used in past instances. Giedrius Kiršys' suggestion in the comment on your question is also good.

 return self::where('sequence', '=', $sequence)->where('interval', '=', $minutes)
        ->leftJoin('wallet', 'wallet_stats.wallet_id', '=', 'wallet.id')
        ->leftJoin('balance', function($join) use ($uid)
        {
            $join->on('balance.wallet_id', '=', 'wallet_stats.wallet_id')
                 ->on('balance.user_id', '=', \DB::raw($uid));
        })
        ->orderBy('volume', 'ASC')->get(['symbol', 'name', 'volume', 'start_price', 'end_price']);

This assumes you haven't already imported DB, if you have get rid of the \