记一次Laravel多表关联查询，刚开始以为是Laravel的B

2017-11-20 本文已影响4192人王宝花

再受到大神的启发后，又重读了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的查询，只要命中索引，其效率是超高的。

记一次Laravel多表关联查询，刚开始以为是Laravel的B

背景

遇到的问题

先说结论

项目介绍

Laravel 中的 Model 定义

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

1. Controller中的查询

2. 困惑产生

3. 思绪迸发

4. 试验其他方法

5. 再次试验

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

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

猜你喜欢

热点阅读