记一次 Laravel 多表关联查询的出现的问题，刚开始以为是 Laravel 的 Bug，最终得到结论，原来是我读书太少！！！

再受到 @leo 的启发，又重读了ORM模型关联的文档。发现了whereHas的用法，于是改写我的昨天的代码实现，发现这才是特么优雅啊。
代码如下:

$data = Question::where('type', 1)->whereHas('labels', function ($query) {
                $query->whereIn('label_id', [1, 2, 3, 4, 5, 6, 7, 8, 9]);
            })->with(['labels'])->get();

结果是满足我的需求的，并且其SQL是这样的:
但是发现前几次查询第一条SQL的时间消耗很高，分别是3.5、2.5等依次降低，最后平均在0.65左右。所以得出结论是，MySQL可能是在首次查询是做了相应的缓存。
测试的结果都在在这里，至于你怎么选择，得看你的了。

数据结果

array:1 [
0 => array:10 [
"id" => 10
"title" => "Ex minus magnam sed quo modi velit. Quis ab consequatur dolor iste tempora accusamus."
"type" => 1
"comment" => null
"star" => 2
"creator" => 15
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"labels" => array:2 [
0 => array:6 [
"id" => 5
"name" => "北京化工大学"
"level" => 3
"upid" => 2
"creator" => 1
"pivot" => array:2 [
"question_id" => 10
"label_id" => 5
]
]
1 => array:6 [
"id" => 30
"name" => "第九章"
"level" => 3
"upid" => 21
"creator" => 1
"pivot" => array:2 [
"question_id" => 10
"label_id" => 30
]
]
]
]
]

第1次的SQL打印

array:2 [
0 => array:3 [
"query" => "select * from `questions` where `type` = ? and exists (select * from `labels` inner join `question_labels` on `labels`.`id` = `question_labels`.`label_id` where `questions`.`id` = `question_labels`.`question_id` and `question_labels`.`label_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?)) and `questions`.`deleted_at` is null"
"bindings" => array:10 [
0 => 1
1 => 1
2 => 2
3 => 3
4 => 4
5 => 5
6 => 6
7 => 7
8 => 8
9 => 9
]
"time" => 3.35
]
1 => array:3 [
"query" => "select `labels`.*, `question_labels`.`question_id` as `pivot_question_id`, `question_labels`.`label_id` as `pivot_label_id` from `labels` inner join `question_labels` on `labels`.`id` = `question_labels`.`label_id` where `question_labels`.`question_id` in (?)"
"bindings" => array:1 [
0 => 10
]
"time" => 0.39
]
]

当进行N次之后，打印SQL语句时，发现time时间0.64，可能是由于MySQL做了数据缓存（至于其中缘由不得而知）。

array:2 [
0 => array:3 [
"query" => "select * from `questions` where `type` = ? and exists (select * from `labels` inner join `question_labels` on `labels`.`id` = `question_labels`.`label_id` where `questions`.`id` = `question_labels`.`question_id` and `question_labels`.`label_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?)) and `questions`.`deleted_at` is null"
"bindings" => array:10 [
0 => 1
1 => 1
2 => 2
3 => 3
4 => 4
5 => 5
6 => 6
7 => 7
8 => 8
9 => 9
]
"time" => 0.64
]
1 => array:3 [
"query" => "select `labels`.*, `question_labels`.`question_id` as `pivot_question_id`, `question_labels`.`label_id` as `pivot_label_id` from `labels` inner join `question_labels` on `labels`.`id` = `question_labels`.`label_id` where `question_labels`.`question_id` in (?)"
"bindings" => array:1 [
0 => 10
]
"time" => 0.32
]
]

-------华丽分割线 2017年11月21日8点更新

背景

因为在做一个考试系统，涉及到题目的搜索。当在做到题目搜索时，有这样的需求。查询一个题目的类型，同时查询标签属性，获取到二者的交集。
题目标签有多个所以设计了题目与标签的关联表question_labels，因为一个题目只能是一种类型，所以他在原始表中questions。
而这时就遇到了多表联查，发现了一个问题，不过最后了解了其原理后，最终还是解决了这个问题。
（其实用DB多表联查可以很快解决这个问题）
所以，本着为后来人做一个先例的态度，写下了此篇文章。

遇到的问题

当使用

with('labels', function ($query) {  
$query->whereIn('question_labels.label_id', [1,2,3,4,5,6,7,8,9]); 
})

这个查询的时候，得到了结果与我预想的不太一样，它取到的结果不是交集，而是基于Question的附加了关联关系的集合。也就是，不符合whereIn('question_labels.label_id', [1,2,3,4,5,6,7,8,9])这个条件的数据依然还是存在。

先说结论

with的意义是，在主数据的结果集下，再查询其附属关系，并将其附加到主数据结果集上，所以刚刚的查询结果是正确的。
那么whereIn('question_labels.label_id', [1,2,3,4,5,6,7,8,9])这个条件有什么意义呢?查询符合条件属性并附加到原数据集合上。
所以，笔者这时得出的最终解决方式是

先进行联合查询获取结果，然后再依据结果集读取关联关系。
附上代码
$data = Question::where('type', 1)
select('questions.*')
->leftJoin('question_labels', 'questions.id', '=', 'question_labels.question_id')
->whereIn('question_labels.label_id', [1,2,3,4,5,6,7,8,9,10,11,12,13])
->with(['options'])
->get();
效率问题，第一次关联查询不可避免。第二次Laravel会使用whereIn加载关联关系，whereIn条件是ID，所以命中主键索引效率是最高的。

file

项目介绍

表设计
- 题目表questions

字段	含义
id	主键
title	题目内容
type	类型：1为单选，2为多选

标签表labels

字段	含义
id	主键
name	标签名称

题目与标签的关联表question_labels

字段	含义
id	主键
question_id	题目ID
label_id	标签ID

Laravel 中的 Model 定义

QuestionModel（定义的关联关系）

public function labels()
{
return $this->belongsToMany(Label::class, 'question_labels');
}

遇到问题，解决问题的过程，以及代码、截图等

1. Controller中的查询

$data = Question::where('type', 1)
->with(['labels' => function ($query) {
 $query->whereIn('question_labels.label_id', [1,2,3,4,5,6,7,8,9]);
}])->get();

查询单选题，并且属性包含1,2,3,4,5,6,7,8,9的属性题目

SQL打印

array:2 [
0 => array:3 [
"query" => "select * from `questions` where `type` = ? and `questions`.`deleted_at` is null"
"bindings" => array:1 [
0 => 1
]
"time" => 0.56
]
1 => array:3 [
"query" => "select `labels`.*, `question_labels`.`question_id` as `pivot_question_id`, `question_labels`.`label_id` as `pivot_label_id` from `labels` inner join `question_labels` on `labels`.`id` = `question_labels`.`label_id` where `question_labels`.`question_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and `question_labels`.`label_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?)"
"bindings" => array:21 [
0 => 1
1 => 2
2 => 3
3 => 8
4 => 9
5 => 10
6 => 13
7 => 15
8 => 16
9 => 17
10 => 20
11 => 21
12 => 1
13 => 2
14 => 3
15 => 4
16 => 5
17 => 6
18 => 7
19 => 8
20 => 9
]
"time" => 0.49
]
]

![](

数据打印

array:12 [
0 => array:10 [
"id" => 1
"title" => "Vel totam dicta sint dignissimos laboriosam molestias quis. Ipsam sed dolores dolorem omnis modi fugit et."
"type" => 1
"comment" => null
"star" => 3
"creator" => 7
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"labels" => []
]
1 => array:10 [
"id" => 2
"title" => "Quas quia quia omnis iusto aperiam in voluptas iste. Ducimus aut vitae quam distinctio. Est assumenda inventore alias tempore quo veritatis molestias."
"type" => 1
"comment" => null
"star" => 5
"creator" => 9
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"labels" => []
]
2 => array:10 [
"id" => 3
"title" => "Facilis autem non explicabo ut repellat. Quas expedita quaerat pariatur distinctio non. Quos dolore suscipit eum eum et quis veritatis dolor."
"type" => 1
"comment" => null
"star" => 3
"creator" => 15
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"labels" => []
]
3 => array:10 [
"id" => 8
"title" => "Et non non rerum praesentium quia odio et blanditiis. Voluptas accusamus ut praesentium occaecati. Qui quia porro accusamus qui ipsa illum."
"type" => 1
"comment" => null
"star" => 4
"creator" => 13
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"labels" => []
]
4 => array:10 [
"id" => 9
"title" => "Cupiditate nemo delectus alias ratione incidunt quis omnis. Dolores quia veniam et at."
"type" => 1
"comment" => null
"star" => 4
"creator" => 11
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"labels" => []
]
5 => array:10 [
"id" => 10
"title" => "Ex minus magnam sed quo modi velit. Quis ab consequatur dolor iste tempora accusamus."
"type" => 1
"comment" => null
"star" => 2
"creator" => 15
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"labels" => array:1 [
0 => array:9 [
"id" => 5
"name" => "北京化工大学"
"level" => 3
"upid" => 2
"creator" => 1
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"pivot" => array:2 [
"question_id" => 10
"label_id" => 5
]
]
]
]
6 => array:10 [
"id" => 13
"title" => "Omnis veritatis ad sint corrupti. Exercitationem nihil dicta cum sapiente ut."
"type" => 1
"comment" => null
"star" => 3
"creator" => 13
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"labels" => []
]
7 => array:10 [
"id" => 15
"title" => "Autem nihil minima culpa eum enim. Odit reprehenderit nobis possimus blanditiis qui quam. Eveniet magni sint sint voluptatem et quo."
"type" => 1
"comment" => null
"star" => 5
"creator" => 1
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"labels" => []
]
8 => array:10 [
"id" => 16
"title" => "Saepe eum quaerat voluptatem. Quo autem vel distinctio esse deserunt ratione et. Voluptas similique eveniet ut asperiores ipsam a repellendus vitae."
"type" => 1
"comment" => null
"star" => 5
"creator" => 6
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"labels" => []
]
9 => array:10 [
"id" => 17
"title" => "Et voluptate exercitationem maxime hic adipisci. Porro illo iure sit dolor blanditiis sed a voluptate. Quasi vero labore adipisci nihil nostrum dolorem."
"type" => 1
"comment" => null
"star" => 3
"creator" => 3
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"labels" => []
]
10 => array:10 [
"id" => 20
"title" => "Earum quia et fuga maxime et. Id nihil quisquam officia omnis iste."
"type" => 1
"comment" => null
"star" => 1
"creator" => 1
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"labels" => []
]
11 => array:10 [
"id" => 21
"title" => "Earum quia et fuga maxime et. Id nihil quisquam officia omnis iste.123123"
"type" => 1
"comment" => null
"star" => 1
"creator" => 1
"deleted_at" => null
"created_at" => null
"updated_at" => null
"labels" => []
]
]

而当我把sql粘贴到navicat上的时候，他的返回结果是这样的。

select `labels`.*, `question_labels`.`question_id` as `pivot_question_id`, `question_labels`.`label_id` as `pivot_label_id` from `labels` INNER join `question_labels` on `labels`.`id` = `question_labels`.`label_id` where `question_labels`.`question_id` in ('1', '2', '3', '8', '9', '10', '13', '15', '16', '17', '20', '21') and `question_labels`.`label_id` in ('1', '2', '3', '4', '5', '6', '7', '8', '9')

2. 困惑产生

这就让我产生了极大的困惑，为什么关联查询的数据是这样的呢？

3. 思绪迸发

后来我想明白了：

我的搜索时基于Question Model的，发送的两条SQL中，
第一条SQL是查询了符合TYPE=1条件的QUESTION，LARAVEL对结果做了相应的缓存。（因为是基于QUESTION的MODEL，并且我使用了with(Relation)的语法）。
第二条Sql使用with(realtion)的语法是在给第一次的结果加载关联关系，所以这个问题就迎刃而解了。

4. 试验其他方法

可能很多人说了，你可以这样啊，先把Label关联关系查询出来：

 $data = Question::labels()->whereIn('id', [1,2,3,4,5,6,7,8,9])->get();

BUT IT DOESN'T WORK!（不起任何作用）
Non-static method App\\Models\\Question::labels() should not be called statically
结论label()这种方式必须要要有一个Question的查询结果集才能加载关联关系，否则即报错。

5. 再次试验

还有人说，你的反向来查？但是不起作用。因为只是将Question的结果集换了Label的结果集，显然是不可取的。

6. 所以我的解决方式是：

$data = Question::where('type', 1)
->select('questions.*')
->leftJoin('question_labels', 'questions.id', '=', 'question_labels.question_id')
->whereIn('question_labels.label_id', [1,2,3,4,5,6,7,8,9,10,11,12,13])
->with(['options', 'possessors', 'labels'])
->get();

options选项、possessors拥有人、labels标签属性

7. 终于得到了想要的SQL和结果

SQL

array:4 [
0 => array:3 [
"query" => "select `questions`.* from `questions` left join `question_labels` on `questions`.`id` = `question_labels`.`question_id` where `type` = ? and `question_labels`.`label_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and `questions`.`deleted_at` is null"
"bindings" => array:14 [
  0 => 1
  1 => 1
  2 => 2
  3 => 3
  4 => 4
  5 => 5
  6 => 6
  7 => 7
  8 => 8
  9 => 9
  10 => 10
  11 => 11
  12 => 12
  13 => 13
]
"time" => 0.52
]
1 => array:3 [
"query" => "select * from `question_options` where `question_options`.`question_id` in (?)"
"bindings" => array:1 [
  0 => 10
]
"time" => 0.32
]
2 => array:3 [
"query" => "select * from `question_possessors` where `question_possessors`.`question_id` in (?) and `question_possessors`.`deleted_at` is null"
"bindings" => array:1 [
  0 => 10
]
"time" => 0.29
]
3 => array:3 [
"query" => "select `labels`.*, `question_labels`.`question_id` as `pivot_question_id`, `question_labels`.`label_id` as `pivot_label_id` from `labels` inner join `question_labels` on `labels`.`id` = `question_labels`.`label_id` where `question_labels`.`question_id` in (?)"
"bindings" => array:1 [
  0 => 10
]
"time" => 0.38
]
]

Response Data

array:1 [
0 => array:12 [
"id" => 10
"title" => "Ex minus magnam sed quo modi velit. Quis ab consequatur dolor iste tempora accusamus."
"type" => 1
"comment" => null
"star" => 2
"creator" => 15
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"options" => []
"possessors" => array:4 [
0 => array:7 [
"id" => 2
"question_id" => 10
"creator" => 11
"possessor" => 20
"deleted_at" => null
"created_at" => "2017-11-20 13:57:20"
"updated_at" => "2017-11-20 13:57:20"
]
1 => array:7 [
"id" => 11
"question_id" => 10
"creator" => 19
"possessor" => 1
"deleted_at" => null
"created_at" => "2017-11-20 13:57:20"
"updated_at" => "2017-11-20 13:57:20"
]
2 => array:7 [
"id" => 12
"question_id" => 10
"creator" => 1
"possessor" => 9
"deleted_at" => null
"created_at" => "2017-11-20 13:57:20"
"updated_at" => "2017-11-20 13:57:20"
]
3 => array:7 [
"id" => 20
"question_id" => 10
"creator" => 10
"possessor" => 6
"deleted_at" => null
"created_at" => "2017-11-20 13:57:20"
"updated_at" => "2017-11-20 13:57:20"
]
]
"labels" => array:2 [
0 => array:9 [
"id" => 5
"name" => "北京化工大学"
"level" => 3
"upid" => 2
"creator" => 1
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"pivot" => array:2 [
  "question_id" => 10
  "label_id" => 5
]
]
1 => array:9 [
"id" => 30
"name" => "第九章"
"level" => 3
"upid" => 21
"creator" => 1
"deleted_at" => null
"created_at" => "2017-11-20 13:57:19"
"updated_at" => "2017-11-20 13:57:19"
"pivot" => array:2 [
  "question_id" => 10
  "label_id" => 30
]
]
]
]
]

最终实现了搜索
其原理是通过join查询先联查出最终的结果集，然后在结果集中再次加载关联关系（possessors所有人，options题目选项，labels题目标签）。
不过可以看到，第一次的联表查询必不可少，第2、3、4条SQL是加载关联关系，无论数据多少，它使用的是WhereIn的查询，只要命中索引，其效率是超高的。