我有 3 个表：
建筑物：
+-----+-------------+-----------+--------------------------+------------+---------------+----------------------+---------------------+
| id | order_flag | type | key | unit_cost | unit_produce | created_at | updated_at |
+-----+-------------+-----------+--------------------------+------------+---------------+----------------------+---------------------+
| 1 | 10 | resource | building1 | 10 | 0 | 2015-01-30 08:54:44 | 2015-01-30 08:54:44 |
| 2 | 20 | resource | building2 | 5 | 0 | 2015-01-30 08:54:44 | 2015-01-30 08:54:44 |
| 3 | 30 | resource | building3 | 10 | 0 | 2015-01-30 08:54:44 | 2015-01-30 08:54:44 |
| 4 | 10 | basic | building4 | 0 | 25 | 2015-01-30 08:54:44 | 2015-01-30 08:54:44 |
+-----+-------------+-----------+--------------------------+------------+---------------+----------------------+---------------------+
building_user：
+-----+--------------+----------+--------+----------------------+---------------------+
| id | building_id | user_id | level | created_at | updated_at |
+-----+--------------+----------+--------+----------------------+---------------------+
| 1 | 2 | 12 | 3 | 2015-01-30 08:52:57 | 2015-01-30 08:55:37 |
| 2 | 4 | 12 | 1 | 2015-01-30 08:53:53 | 2015-01-30 08:53:53 |
| 3 | 1 | 12 | 2 | 2015-01-30 08:54:08 | 2015-01-30 08:55:10 |
+-----+--------------+----------+--------+----------------------+---------------------+
building_require：
+-----+--------------+-------------+--------+----------------------+---------------------+
| id | building_id | require_id | level | created_at | updated_at |
+-----+--------------+-------------+--------+----------------------+---------------------+
| 1 | 1 | 4 | 1 | 2015-01-30 08:54:44 | 2015-01-30 08:54:44 |
| 2 | 3 | 1 | 5 | 2015-01-30 08:54:44 | 2015-01-30 08:54:44 |
+-----+--------------+-------------+--------+----------------------+---------------------+
我在建筑模型中写下这种关系：
# Building Require another Building(s)
public function requires() {
return $this->belongsToMany('Building', 'building_require', 'building_id', 'require_id')->withPivot(array(
'level',
'updated_at',
'created_at'
));
}
public function users() {
return $this->belongsToMany('User')->withPivot(array('level'));
}
现在我想从桌子建筑中选择所有建筑物.. 以下限制：
如果building_require表中没有来自建筑物的条目，则此建筑物可以在结果中。
如果此建筑物在building_require表中有一个条目，则必须检查building_user表中是否有条目，其中building_id与building_required表中的required_id相同。
（示例 building_id 1 需要building_id 4，building_id 3 需要building_id 1 的条目。
除此之外（这是困难的部分），还必须检查building_user表中的条目是否具有级别值>= 作为building_require表中定义的级别。
所以总结一下：
在 这种情况building_id 1 需要在 building_user 中输入条目，其中 building_id 是 4 并且级别至少为 1。building_id 3 需要输入 building_user其中building_id为 1，级别至少为 5。 所以 - building_id 1 和 3 有一个必需的建筑物。建筑与 ID 2 和 4 在结果中没有限制。 在这种情况下，仅允许使用 building_id 3。因为building_user表中building_id 1 的水平只有 2，而不是至少 5。
对我来说，这是非常困难的，我认为这不是最好的解决方案，可悲的是，没有检查水平值：
# all buildings there has a required building:
$allRequiredBuildingIds = DB::table('building_require')->lists('building_id');
# if user has buildings, this buildings must remove from $allRequiredBuildingIds
if(Sentry::getUser()->buildings()->count() > 0) {
# list all building_id 's of the user_building table
$userBuildingIds = Sentry::getUser()->buildings()->lists('building_id');
# list all building_id's of the buildings there are required and already stored in the building_user table
$userBuildingRequiredIds = DB::table('building_require')->whereIn('require_id', $userBuildingIds)->lists('building_id');
# there are all the building_id's which are not allowed to display
$allRequiredBuildingIds = array_values(array_diff($allRequiredBuildingIds,$userBuildingRequiredIds));
}
# if there are buildings there are not allowed to display?
if(count($allRequiredBuildingIds) > 0) {
$buildings = Building::whereType($type)->whereNotIn('id', $allRequiredBuildingIds)->paginate(10);
}else{
$buildings = Building::whereType($type)->paginate(10);
}
有人可以帮助我，优化它并集成水平检查吗？我的头砰砰直跳。
我有一个理论认为这可能有效，但我还没有测试它。我认为关键是只在 wherehas 关闭中使用内部的 Join，从而允许 Eloquent 构建器保持活动状态。
Building::doesntHave('requires')
->orWhereHas('users', function($q) {
$q->join('building_require', 'building_user.building_id', '=', 'building_require.required_id')
->where('building_user.level', '>=', 'building_require.level');
})->get();
它将生成如下所示的查询：
select * from `buildings` where (
select count(*) from `building_require` as `x` where `x`.`building_id` = `buildings`.`id`
) < 1 or (
select count(*) from `users`
inner join `building_user` on `users`.`id` = `building_user`.`user_id`
inner join `building_require` on `building_user`.`building_id` = `building_require`.`required_id`
where `building_user`.`building_id` = `buildings`.`id`
and `building_user`.`level` >= building_require.level
) >= 1
不幸的是，这不会解决问题。它将返回 building_require.required_id 建筑物，而不是 building_require.building_id 建筑物。我设法在原始SQL中找出正确的查询，但我不确定如何在Eloquent中修复它：
SQLFiddle
select * from `buildings` where (
select count(*) from `building_require` as `x` where `x`.`building_id` = `buildings`.`id`
) < 1 or (
select count(*) from `users`
inner join `building_user` on `users`.`id` = `building_user`.`user_id`
inner join `building_require` on `building_user`.`building_id` = `building_require`.`required_id`
where building_require.building_id = buildings.id
and `building_user`.`level` >= building_require.level
) >= 1;