Laravel 5.2 left join one to many only row with column of highest value

Question

I am trying to do a left join using eloquent on a one to many relationship. I would only like to get the row with the highest value in the sort_order column.

So far my query looks like this:

Package::select('packages.*')
        ->leftJoin('package_routes', 'package_routes.package_id', '=', 'packages.id')
        ->leftJoin('package_route_items', function($join){
            $join->on('package_route_items.package_route_id', '=', 'package_routes.id')
                ->where(???);
        })->...//do more stuff to query here

I'm stuck on the where clause, if I should even be using a where at all.

Please provide your table definition as from your question its unclear from which table you need highest row and which table has sort_order column — M Khalid Junaid
– M Khalid Junaid, Commented Dec 22, 2017 at 6:53

Community · Accepted Answer · 2017-05-23 11:53:57Z

0

Since i don't know all the query where conditions i'll try to simplify (you will probably have to adjust column names and syntax

add use DB; at beginning of your controller/model

$data = DB::table('packages')
->select('packages.*, package_route_items.company_id, package_routes.company_id')
    ->join('package_routes', 'package_routes.package_id', '=', 'packages.id')
    ->join('package_route_items.package_route_id', '=', 'package_routes.id')
    ->orderBy('packages.value_column', 'DESC')->first();

That is what i have understand so far from your description. It's not tested but i think it should work with probably minor editing.

Similar question like yours is here, and here (hope it helps you even more than mine answer).

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Jun 2, 2016 at 18:41

KuKeC

4,6405 gold badges34 silver badges62 bronze badges

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

5 Comments

Adam Over a year ago

Call to undefined method Illuminate\Database\Query\JoinClause::orderBy() unfortunately

Adam Over a year ago

Ah apologies, I've also edited my question to make my intentions clear. I need to carry on adding joins to the query and then eventually order by something else. Sorry for the confusion!

KuKeC Over a year ago

I'm a bit confused now, can u elaborate a bit about tables and desired output or maybe some info so i can help you with making proper query?

Adam Over a year ago

Sure, so I was originally creating an eloquent query but now I need to be able to order the whole thing on a company name. Now the company itself is a foreign key in package_route_items, and I need to get the last package_route_items.company_id in the list in order to see the packages final destination. So the schema is Packages > has one PackageRoute > has many PackageRouteItems > has one company_id I also need to get another company_id on PackageRoute (the start point) and a few other bits of meta data from the Package.

Adam Over a year ago

To add to that, I'm first making the joins like you can see in the question, and then once I have ordered them correctly I'm hydrating the models with a ->with(...relationships here...)

Collectives™ on Stack Overflow

Laravel 5.2 left join one to many only row with column of highest value

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related