Can't really tell what's going on without DB structure, got any relationships done between the tables?

If you use with I think you don't need to join. try

$deliveries = Delivery::with(array('order' => function($query)
{
     $query->where('orders.user_id', $customerID);
     $query->orderBy('orders.created_at', 'DESC');
}))
    ->orderBy('date')
    ->get();

@bbashy My DB relationships are perfectly fine, this is not what my question is about. My structure is as follows (I'm skipping some columns to simplify example):

• users: (id, firstname, lastname)
• products: (id, name)
• orders: (id, user_id, product_id)
• deliveries: (id, order_id, date)

@iClosedz Thanks, but it doesn't work as requested. It gets ALL deliveries and then orders by user_id. What I need is to obtain only those deliveries that are connected with orders with particular user_id.

@ekaitzastiz I've read it and it's not what I need. I don't want to put constraints to limit connected rows, but to limit deliveries results according to connected table constraint.

"with()" will return the related products, if there aren't, then null will be returned. So it will return all the deliveries with the products. Also the deliveries that has no products. Maybe you want only deliveries that has at least one product, the whereHas() or another methods are available (I think, usually, there aren't deliveries with product, but maybe you want deliveries with or without products, for example just a letter..). Few days ago someone ask for the same.....

WhereHas(), with().... are OK, but don't get a lot of deliveries raw because it will do a large whereIn() query. I that case maybe is better to use join, leftjoin, rightjoin... Or if you you use them, use them with paginate(X), that way the whereIn() array wil have X values.

I tried this:

$deliveries = Delivery::with('order.product')
	->whereHas('order', function($query) use ($customerID) {
		$query->whereUserId($customerID);
	})
	->orderBy('date')
	->get();

But then, with() doesn't work at all and this is the one and only query ran in the request:

select * from `ffk_deliveries` where (select count(*) from `ffk_orders` where `ffk_deliveries`.`order_id` = `ffk_orders`.`id` and `user_id` = ?) >= 1 order by `date` asc

So I think you need to use raw sql.

just do it with join. (if you use soft deletes then remember to put a whereNull() in the joined table because using join doesn't introduce it automatically)

Delivery::select( DB::raw('products.id as product_id,deliveries.*')
->join('orders', 'deliveries.order_id', '=', 'orders.id')->where('orders.user_id',$customerID)
->orderBy('deliveries.date')->orderBy('orders.created_at', 'DESC')->get();


// DB::raw('fields_you_want_and_rename_field_that_are_repeated_to_prevent_ambiguos')

Here a more complex query I have in a project, just to see how complex you can do it if you work on it, if you try and try:

        $chats = $this->model
            ->select(
                DB::raw(
                    'users.id as user_id,chats.id as id,chats.user1_id,chats.user2_id,chats.updated_at,chats.displayed,users.last_name,users.first_name,users2.first_name as fn,users2.last_name as ln,users2.id as target_user_id'
                )
            )
            ->join(
                'chats',
                function ($join) {
                    $join->on('users.id', '=', 'chats.user1_id');
                    $join->orOn('users.id', '=', 'chats.user2_id');
                }
            )
            ->whereNull('chats.deleted_at')
            ->where('users.id', '=', $this->auth->user()->id)
            ->join(
                'users as users2',
                function ($join) {
                    $join->on('users2.id', '=', 'chats.user1_id');
                    $join->orOn('users2.id', '=', 'chats.user2_id');
                }
            )
            ->where('users2.id', '<>', $this->auth->user()->id);

        if ($archived)
            $chats = $chats->whereNotIn('chats.displayed', array($this->auth->user()->id, 0));
        else
            $chats = $chats->whereIn('chats.displayed', array($this->auth->user()->id, 0));


        return $chats->orderBy('chats.updated_at', 'desc')
            ->paginate(10);

I hope you will find solution...

Maybe I am missing what you are trying to do but did you set up the relationships in the model using hasOne, hasMany, belongsTo or any of those?

If not that is the way to do it.

<?php

class Users extends Eloquent{
    //DATABASE TABLE
    protected $table = 'User';

    public function orders() {
        return $this->hasMany('Orders', 'user_id', 'id')->where('this', '=', 'that');
    }  
}


class Orders extends Eloquent{
    //DATABASE TABLE
    protected $table = 'Orders';

    public function deliveries() {
       // this might be $this->hasOne... depends on what you need
        return $this->hasMany('Deliveries', 'order_id', 'id')->where('other', '!=', 'thing');
    }  
}

//get a user's deliveries for an order
$the_deliveries = Order::where('user_id', '=' $user_id)->where('order_id', '=', $order_id)->with('deliveries')->orderBy('aField');

IanSirkit said:

Maybe I am missing what you are trying to do but did you set up the relationships in the model using hasOne, hasMany, belongsTo or any of those?

Have you read my topic?

kokokurak said:

IanSirkit said:

Maybe I am missing what you are trying to do but did you set up the relationships in the model using hasOne, hasMany, belongsTo or any of those?

Have you read my topic?

Yup and I don't see where you use model relationships to join and I think that is the solution you want to try.

Why not use the hasManyThrough relationship.

indside your User model :

public function deliveries()
{
    return $this->hasManyThrough('Delivery', 'Order', 'user_id', 'order_id');
}

user_id and order_id arguments can be left out if you follow standard naming convention.

Then to access a users deliveries you should be able to do

$user = User::with('deliveries')->where('id', $customerId)->get();

or

$user = User::find($customerId);
$deliveries = $user->deliveries;

IanSirkit said: Yup and I don't see where you use model relationships to join and I think that is the solution you want to try.

I'm using with(), so it's obvious my relationships are all set. Otherwise I'd get an error.

I decided to build query for now. Here's what I got:

$deliveries = Delivery::join('orders', 'deliveries.order_id', '=', 'orders.id')
	->join('products', 'orders.product_id', '=', 'products.id')
	->where('orders.user_id', $customerID)
	->orderBy('deliveries.date', 'DESC')
	->get(array(
		'deliveries.id',
		'deliveries.date',
		'products.code as product'
	));

And here's the result:

select `ffk_deliveries`.`id`,
       `ffk_deliveries`.`date`,
       `ffk_products`.`code` as `product`
from `ffk_deliveries`
inner join `ffk_orders`
on `ffk_deliveries`.`order_id` = `ffk_orders`.`id`
inner join `ffk_products`
on `ffk_orders`.`product_id` = `ffk_products`.`id`
where `ffk_orders`.`user_id` = ?
order by `ffk_deliveries`.`date` desc

It works, but if there's any cleaner way of doing it (pure Eloquent) please let me know :)

$users = App\User::with(['posts' => function ($query) { $query->where('title', 'like', '%first%'); }])->get();

https://laravel.com/docs/5.2/eloquent-relationships

easy and clean, just the way it should be=)

@giyama That query will return all 'users' and then conditionally load their 'posts'. If a given post doesn't pass the constraint then that 'post' is assigned null in the returned 'user' model.

Keeping with the users and posts example what is needed here is to conditionally load users based on the post id. I haven't found any better more Eloquent solution either but am very interested as well.

@mitchjam If I am understanding the question correctly, you are looking for the whereHas() method.

Given the example @giyama provided:

$users = App\User::whereHas(
        'posts', function ($query) {
            $query->where('title', 'like', '%first%');
        }
    )
    ->with('posts')
    ->get();

The whereHas() causes only users who have a post that matches the closure's where() condition to be returned, and the subsequent with() call returns all of the posts for those users. (You could also extrapolate the logic to include only the post where('title', 'like', '%first%') if that's what you wanted.)