Laravel 5 Eloquent Query syntax issue when selecting MAX value from column

Question

I am five days into Laravel and after hours of watching Jeffrey Way I decided to delve into building an app to learn. I am migrating an old app across to L5 as a way to learn.

I just tried this syntax which worked without errors but returned an empty record, so incorrect output. The old SQL returns one record for the inputs below.

What is still different between the old SQL and the new L5 Query that the results are different? Thanks!

I am trying to replicate this SQL:

SELECT DISTINCT
    TBA.*, 
    TBB.*,                      
    MAX(TBA.mv_a_m_loss) as mv_a_m_loss
FROM TBA,
    TBB,
    TBC
WHERE TBB.id = 1
AND TBC.id = 1
AND TBB.conductor_temp = TBA.conductor_temp
AND TBB.conductor_size_mm_sq = TBA.conductor_size_mm_sq
AND TBA.mv_a_m_loss < 50
AND TBC.id = TBB.id
AND TBC.id = TBA.id

Here's my Laravel 5 Eloquent syntax:

$temp = DB::table('TBC')
    ->join('TBB', 'TBC.id', '=', 'TBB.id')
    ->join('TBA', 'TBC.id', '=', 'TBA.id')
    ->where('TBC.TBA_id', '=', 1)
    ->where('TBC.TBB_id', '=', 1)
    ->where('TBA.mv_a_m_loss', '<', 50)
    ->where('TBB.conductor_temp', '=', 'TBA.conductor_temp')
    ->where('TBB.conductor_size_mm_sq', '=', 'TBA.conductor_size_mm_sq')
    ->distinct()->get(['TBA.*', 'TBB.*', DB::raw('MAX(TBA.mv_a_m_loss) AS max_mv_a_m_loss')]);
dd($temp);

The result for the old SQL is a single record, the L5 result is a NULL record.

qust a quick question (because its a bit hard to read) why do not you use any associations? would be way much easier, and generatign the max value as well — user1130272
– user1130272, Commented Mar 26, 2015 at 10:32
Sorry @Levente Nagy, I have classes for each table with relationships defined. Is this what you mean? — TheRealPapa
– TheRealPapa, Commented Mar 26, 2015 at 10:34
Thanks @Levente Nagy but this is where I am stuck. How to leverage the relationships when there are a few tables to link at once? — TheRealPapa
– TheRealPapa, Commented Mar 26, 2015 at 10:42

Jarek Tkaczyk · Accepted Answer · 2015-03-26 12:25:11Z

1

Not getting into whether you should or not use relationships, but here's the solution for you:

->whereRaw('TBB.conductor_temp = TBA.conductor_temp')
->whereRaw('TBB.conductor_size_mm_sq = TBA.conductor_size_mm_sq')

You could use select(x,'=', DB::raw(..)) as well, no difference.

Mind that the value passed to where method is bound by PDO. That said, it is always compared as string, not as a table column.

answered Mar 26, 2015 at 12:25

Jarek Tkaczyk

81.4k27 gold badges167 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheRealPapa Over a year ago

AWESOME! Many thanks! I now need to go figure out the relationships way. Thanks again!

Collectives™ on Stack Overflow

Laravel 5 Eloquent Query syntax issue when selecting MAX value from column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related