Eloquent Nested Relation with Some Constraint

Question

I have the following three tables:

A
-------------
| id | name |
-------------

B
--------------------
| id | A_id | name |
--------------------

C
--------------------
| id | B_id | name |
--------------------

So, the data in table C belongs to the data in table B which belongs to the data in table A. Now, I want to query C, while also retrieving data from B and A and the following code does the trick just fine.

C::with('B.A')->get();

The problem now, is that I want to query C with some constraints. One of these constraints is the id of A. I've tried the following:

C::with(array('B.A' => function ($query)
{
    $query->where('id', '=', $constraint);
}))->get();

But it seems that Eloquent will retrieve all the rows in C without even taking the constraint into account, except when it's executing the query to retrieve data in table A. How do I get around this problem? Do I need to add another field in C, that is A_id, and match $constraint against that field?

Answer 1

You are confusing the with() method with SQL's JOIN and that happens a lot.

First a little background

When you use Foo::with('bar')->where_something(1), Laravel will first load the Foo and then, based on Foo.bar_id, it will load the Bar. It serves the purpose of telling Laravel to eager load dependencies of your model on a combined query, greatly improving performance of iterations on those models.

If you don't use it, the following queries should be executed:

SELECT * FROM foos WHERE foos.something = 1;
SELECT * FROM bars WHERE bars.id = 30;
SELECT * FROM bars WHERE bars.id = 57;
SELECT * FROM bars WHERE bars.id = 134;
SELECT * FROM bars WHERE bars.id = 1096;

If you use it, on the other hand:

SELECT * FROM foos WHERE foos.something = 1;
SELECT * FROM bars WHERE bars.id IN (30, 57, 134, 1096); // Eager loading

When you add a condition to that with(), you are only constraining the eager loading of those dependencies, and not the first query.

Now to your answer

To achieve what you want, you'll need to use ->join().

C::with(array('b', 'b.a'))
 ->join('b', 'b.id', '=', 'c.b_id')
 ->join('a', 'a.id', '=', 'b.a_id')
 ->where('a.id', '=', $ID)
 ->get('c.*');

I've included the with(), because I didn't know if you would need to access $c->b->a. If you don't, and you just need $c data, you can remove the with() since it will query for B's and A's unnecessarily.