带有一些约束的 Eloquent 嵌套关系 [英] Eloquent Nested Relation with Some Constraint

问题描述

我有以下三个表:

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

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

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

所以，C表的数据属于B表的数据，A表的数据.现在，我想查询 C，同时还从 B 和 A 检索数据，下面的代码可以很好地解决问题.

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();

现在的问题是，我想用一些约束来查询 C.这些约束之一是A 的id.我尝试了以下方法:

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();

但似乎 Eloquent 会检索 C 中的所有行，甚至不考虑约束，除非它执行查询以检索表 A 中的数据.我该如何解决这个问题?我是否需要在 C 中添加另一个字段，即 A_id，并将 $constraint 与该字段匹配?

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?

推荐答案

您将 with() 方法与 SQL 的 JOIN 混淆，这种情况经常发生.

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

>

首先介绍一下背景

当你使用 Foo::with('bar')->where_something(1) 时，Laravel 将首先加载 Foo 然后，基于 Foo.bar_id，它将加载Bar.它的目的是告诉 Laravel 在组合查询上预先加载模型的依赖项，从而大大提高这些模型的迭代性能.

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

当您向 with() 添加条件时，您只会限制这些依赖项的预加载，而不是第一个查询.

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

要实现您想要的，您需要使用 ->join().

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.*');

我已经包含了 with()，因为我不知道您是否需要访问 $c->b->a.如果你不这样做，而你只需要 $c 数据，你可以删除 with() 因为它会不必要地查询 B 和 A.

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.

这篇关于带有一些约束的 Eloquent 嵌套关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！