Laravel Query Builder 复杂查询案例：子查询实现分区查询 partition b

littleGrow 发布于2019-06-28 18:18 / 2753人阅读

摘要：案例案例在文章列表中附带上前条评论，在获取文章列表时同时把每个文章的前条评论一同查询出来。这是典型分区查询案例，需要根据表中的字段进行分区，同时根据条件进行排序，把符合条件的前条是数据取出来。查询语句中定义变量以及函数的使用如何构建子查询。

案例

案例：Laravel 在文章列表中附带上前10条评论?，在获取文章列表时同时把每个文章的前10条评论一同查询出来。

这是典型分区查询案例，需要根据 comments 表中的 post_id 字段进行分区，同时根据条件进行排序，把符合条件的前 N 条是数据取出来。

在其他数据库(Oracle, SQL Server，Vertica) 包含了 row_number partition by 这样的函数，能够比较容易的实现。

比如在 SQL Server 中:

SELECT * FROM (
SELECT *, row_number() OVER (partition by post_id ORDER BY created_at desc) rank FROM comments where post_id in (1,2,3,4,5) 
) b where rand < 11;

在 mysql 中要复杂一些，我们先来看看上面案例中实现需求的几种解决办法。

解决办法 方法1：

在 blade 中要显示评论数据的地方 post->comments()->limit(10)

问题：如果取了 20 条 Post 数据，就会有 20 条取 comments 的 sql 语句，会造成执行的 sql 语句过多。

不是非常可取，主要问题会造成 SQL 语句过多，对数据库服务器产生压力，不过这里可以使用缓存来改进，但是不在本文章讨论范围里。

方法2：

直接通过 with 把 Post 的所有 comments 数据都取出来，在 blade 中 post->comments->take(10)

问题：Laravel 会预先把文章所有的评论数据查询出来，如果文章的评论数据非常多，可能会造成内存泄漏。

方法3：

$posts = Post::paginate(15);

$postIds = $posts->pluck("id")->all();

//找出符合条件的 comments ，同时定义 @post, @rank 变量，这里没有用 all,get 等函数，此时并不会执行 SQL 语句。
$sub = Comment::whereIn("post_id",$postIds)->select(DB::raw("*,@post := NULL ,@rank := 0"))->orderBy("post_id");

//把上面构造的 sql 查询作为子表进行查询，根据 post_id 进行分区的同时 @rank 变量不断+1
$sub2 = DB::table( DB::raw("({$sub->toSql()}) as b") )
            ->mergeBindings($sub->getQuery())
            ->select(DB::raw("b.*,IF (
            @post = b.post_id ,@rank :=@rank + 1 ,@rank := 1
        ) AS rank,
        @post := b.post_id"));

//取出符合条件的前10条comment
$commentIds = DB::table( DB::raw("({$sub2->toSql()}) as c") )
            ->mergeBindings($sub2)
        ->where("rank","<",11)->select("c.id")->pluck("id")->toArray();

$comments = Comment::whereIn("id",$commentIds)->get();

$posts = $posts->each(function ($item, $key) use ($comments) {
    $item->comments = $comments->where("post_id",$item->id);
});

会产生三条sql

select * from `posts` limit 15 offset 0;

select `c`.`id` from (select b.*,IF (
@post = b.post_id ,@rank :=@rank + 1 ,@rank := 1
) AS rank,
@post := b.post_id from (select *,@post := NULL ,@rank := 0 from `comments` where `post_id` in ("2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16") order by `post_id` asc) as b) as c where `rank` < "11";

select * from `comments` where `id` in ("180", "589", "590", "3736");

知识点

toSql() 方法的作用是为了获取不带有 binding 参数的 SQL, 也就是说带问号的 SQL

getQuery() 方法的作用是为了获取 binding 参数并代替 toSql() 获得SQL的问号，从而得到完整的SQL

raw() 的作用是直接把 SQL 套进 Laravel 的查询构造器中。

mysql 查询语句中定义变量 @post := NULL ,@rank := 0 以及 IF 函数的使用

如何构建子查询。

为什么不直接用原生 SQL 语句来实现？

这里之所以坚持使用 Laravel Query Builder 来实现，可以有效防止 SQL 注入，并且和 ORM 的 Model 对象关联起来。

如果大家还有更多类似这种复杂的需求，欢迎大家投稿。

讨论交流

服务器托管混合云子查询 sql 股票复杂查询关联子查询 odps子查询

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/28136.html

［MySQL］MySQL分区与传统的分库分表

摘要：传统的分库分表传统的分库分表都是通过应用层逻辑实现的，对于数据库层面来说，都是普通的表和库。分区也存在与传统分表一样的问题，可扩展性差。子分区子分区是分区表中每个分区的再次分割。查询优化分区的目的是为了传统的分库分表传统的分库分表都是通过应用层逻辑实现的，对于数据库层面来说，都是普通的表和库。分库分库的原因首先，在单台数据库服务器性能足够的情况下，分库对于数据库性能是没有影响...

xuexiangjys 2019-08-01 13:24 评论0 收藏0
Laravel核心解读--Database(四) 模型关联

摘要：为关联关系设置约束子模型的等于父模型的上面设置的字段的值子类实现这个抽象方法通过上面代码看到创建实例时主要是做了一些配置相关的操作，设置了子模型父模型两个模型的关联字段和关联的约束。不过当查询父模型时，可以预加载关联数据。 Database 模型关联上篇文章我们主要讲了Eloquent Model关于基础的CRUD方法的实现，Eloquent Model中除了基础的CRUD外还有一个...

gekylin 2019-07-01 12:26 评论0 收藏0
写一个“特殊”的查询构造器 - (四、条件查询：复杂条件)

摘要：复杂的条件在的条件查询中，不只有这些基本的子句，还有等复杂一些的子句。这篇我们就来讲一下查询构造器如何构造这些复杂的查询语句。复杂的条件在 SQL 的条件查询中，不只有 where、or where 这些基本的子句，还有 where in、where exists、where between 等复杂一些的子句。而且即使是 where 这种基础的子句，也有多个条件的多种逻辑组合。这篇...

baoxl 2019-07-01 12:29 评论0 收藏0
Oceanbase新版本复合分区添加分区操作

Oceanbase新版本复合分区添加分区操作 img{ display:block; margin:0 auto !important; width:100%; } body{ width:75%; ...

不知名网友 2023-01-11 11:02 评论0 收藏2700
20 个 Laravel Eloquent 必备的实用技巧

摘要：看起来是一个简单的机制，但是在底层，有很多半隐藏的函数和鲜为人知的方式来实现更多功能。在这篇文章中，我将演示几个小技巧。另外，在里也有些和时间相关的预定义方法通过关系排序一个复杂一点的技巧。幸运的是，确实有这样的方法。 showImg(https://segmentfault.com/img/bV8L5s?w=1240&h=634); Eloquent ORM 看起来是一个简单的机制，...

clasnake 2019-06-28 18:45 评论0 收藏0