记一次 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.52.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

项目介绍

  1. 表设计
    • 题目表questions
字段 含义
id 主键
title 题目内容
type 类型:1为单选,2为多选
  • 标签表labels
字段 含义
id 主键
name 标签名称
  • 题目与标签的关联表question_labels
字段 含义
id 主键
question_id 题目ID
label_id 标签ID

Laravel 中的 Model 定义

  1. 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![](

数据打印

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

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

本作品采用《CC 协议》,转载必须注明作者和本文链接
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《G01 Go 实战入门》
从零开始带你一步步开发一个 Go 博客项目,让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。
讨论数量: 3
leo

whereHas

6年前 评论

这样?

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

试试这样行不

4年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!