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

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

$data = Question::where('type', 1)->whereHas('labels', function ($query) {
                $query->whereIn('question_labels.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，所以命中主键索引效率是最高的。

项目介绍

表设计

题目表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
  ]
]

image.png

[图片上传失败...(image-497b5-1511191380200)]

数据打印

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" => []
  ]
]

image.png

而当我把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')

image.png

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

image.png

结论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和结果

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的查询，只要命中索引，其效率是超高的。

最后编辑于：2017.11.21 08:40:26

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,590评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 86,808评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,151评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,779评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,773评论 5赞 367
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,656评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,022评论 3赞 398
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,678评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 41,038评论 1赞 299
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,659评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,756评论 1赞 330
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,411评论 4赞 321
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,005评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,973评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,203评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,053评论 2赞 350
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,495评论 2赞 343

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

背景

遇到的问题

先说结论

项目介绍

Laravel 中的 Model 定义

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

1. Controller中的查询

2. 困惑产生

3. 思绪迸发

4. 试验其他方法

5. 再次试验

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

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

推荐阅读更多精彩内容