带有一些约束的 Eloquent 嵌套关系
我有以下三个表:
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.
相关文章