Laravel Query Builder 复杂查询案例:子查询实现分区

2019-10-11 12:06 来源:未知

案例

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

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

在其他数据库(OracleSQL ServerVertica) 包含了 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 中要复杂一些,我们先来看看上面案例中实现需求的几种解决办法。

还有Comment类的定义

最后在php中对这两个语句进行分别查询10次,

方法2:

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

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

定义Model类

上面的语句采用的是JOIN,mysql的论坛上有人使用

知识点

  1. toSql() 方法的作用是为了获取不带有 binding 参数的 SQL, 也就是说带问号的 SQL
  2. getQuery() 方法的作用是为了获取 binding参数并代替 toSql() 获得SQL的问号,从而得到完整的SQL
  3. raw() 的作用是直接把 SQL 套进 Laravel 的查询构造器中。
  4. mysql 查询语句中定义变量 @post := NULL ,@rank := 0 以及 IF 函数的使用
  5. 如何构建子查询。

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

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

第二章数据表,表名是comment,存储了博客文章的评论,数据如下:

后来请教了baidu,得到如下代码

解决办法

这里以MySQL数据为例,当然你也完全可以实现一套Sqlite数据库的接口。

我测试了一下,需要0.5秒,速度也不错,但是跟上面的语句还是有很大差距

方法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');

这三个接口分别负责了三种查询:遍历查询,条件查询,按编号查询,其实这三种接口的设计并不是最科学的,甚至get方法不过是where的一种特殊形式,但是这样的设计并不影响我们工程,甚至也有助于理解,我们后期会对这段代码做改动。

在mysql中带了随机取数据的函数,在mysql中我们会有rand()函数,很多朋友都会直接使用,如果几百条数据肯定没事,如果几万或百万时你会发现,直接使用是错误的。下面我来介绍随机取数据一些优化方法。

方法1:

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

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

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

post和comment是一对多的关系,每一篇博客文章对应了多条评论,每一条评论只属于一篇文章。

mysql> select RAND();
    -> 0.5925
mysql> select RAND(20);
    -> 0.1811
mysql> select RAND(20);
    -> 0.1811
mysql> select RAND();
    -> 0.2079
mysql> select RAND();
    -> 0.7888

您可能感兴趣的文章:

  • ThinkPHP关联模型操作实例分析
  • PHP实现MVC开发得最简单的方法——模型
  • ThinkPHP实例化模型的四种方法概述
  • thinkphp视图模型查询提示ERR: 1146:Table ''db.pr_order_view'' doesn''t exist的解决方法
  • 最新的php 文件上传模型,支持多文件上传
  • PHP开发框架Laravel数据库操作方法总结
  • PHP的Yii框架中使用数据库的配置和SQL操作实例教程
  • PHP的Laravel框架结合MySQL与Redis数据库的使用部署
  • 实现PHP框架系列文章(6)mysql数据库方法
  • ThinkPHP框架分布式数据库连接方法详解

SELECT * FROM table_name ORDER BY rand() LIMIT 5;

1.类和数据表,一方修改会导致另一方的修改,只要数据表结构不定下来,业务逻辑的开发几乎没法开工

SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;

之所以在Model类里就完成了SQL的拼接,就是希望在子类中不必重复再写SQL。

RAND()
RAND(N)

第一张数据表,表名是post,存储了博客文章,数据如下:

感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!

Model类的设计之前,我们先定义好三个接口

后者花费时间 0.015130 秒

图片 1

前者花费时间 0.147433 秒

然后是Post类的定义

注意事项 查看官方手册,也说rand()放在ORDER BY 子句中会被执行多次,自然效率及很低。

模型与数据库

SELECT * FROM `table`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))   (SELECT MIN(id) FROM `table`))) 
ORDER BY id LIMIT 1;


SELECT *
FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) (SELECT MIN(id) FROM `table`)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;
$post=new PostModel();
$post::all();
$arr=$post::get('1');
var_dump($arr);

$comment=new CommentModel();
$arr=$comment::get('2');
var_dump($arr);

你不能在一个ORDER BY子句用RAND()值使用列,因为ORDER BY将重复计算列多次。然而在MySQL3.23中,你可以做: SELECT * FROM table_name ORDER BY RAND(),这是有利于得到一个来自SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000的集合的随机样本。注意在一个WHERE子句里的一个RAND()将在每次WHERE被执行时重新评估。

谈到数据库类,上述的写法仍不是最好的,因为我们可以使用单例模式来保证DB类只有一次初始化,来节省硬件资源的开销,但这不是本节的主题,我们把设计模式放在之后来谈。 

但是这样会产生连续的5条记录。解决办法只能是每次查询一条,查询5次。即便如此也值得,因为15万条的表,查询只需要0.01秒不到。

我们可以在控制器的方法中写这样的代码来完成调用数据

SELECT *
FROM `table`
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` )
ORDER BY id LIMIT 1;
class Model implements IModel{
 public static $table;

 public static $db;
 public function __construct(){
  self::$db=new MySQL();
 }

 public static function get($id){
  return self::where('id',$id);
 }

 public static function where($condition,$value){
  $sql=sprintf("select * from %s where %s='%s'",self::$table,$condition,$value);
  return self::$db->Query($sql);
 }

 public static function all(){
  $sql=sprintf("select * from %s",self::$table);
  return self::$db->Query($sql);
 }
}

您可能感兴趣的文章:

  • mysql随机查询若干条数据的方法
  • MySQL取出随机数据
  • MYSQL随机抽取查询 MySQL Order By Rand()效率问题
  • MySQL查询随机数据的4种方法和性能对比
  • SQL 随机查询 包括(sqlserver,mysql,access等)
  • 数据库查询排序使用随机排序结果示例(Oracle/MySQL/MS SQL Server)
  • 从MySQL数据库表中取出随机数据的代码
  • mysql获取随机数据的方法
  • MySQL中随机生成固定长度字符串的方法
  • php随机取mysql记录方法小结

先写一个DB抽象类,规定类需要实现的方法

返回在范围0到1.0内的随机浮点值。如果一个整数参数N被指定,它被用作种子值。

什么是模型?

完整查询语句是:

class CommentModel extends Model{
 public $commentid;
 public function __construct(){
  parent::__construct();
  parent::$table='comment';
 }
}

网上基本上都是查询max(id) * rand()来随机获取数据。

class PostModel extends Model{ 
 public $postid;
 public function __construct(){
  parent::__construct();
  parent::$table='post';
 }
}

以上的sql语句最后一条,本人实际测试通过,100W数据,瞬间出结果。

假如要开发一个博客系统,我们先设计两个Model和两张数据表

rand在手册里是这么说的:

abstract class DB{

 private $IP;
 private $user;
 private $pwd;
 private $name;
 private $connection;

 abstract public function Execute($sql);
 abstract public function Query($sql);
}

执行效率需要0.02 sec.可惜的是,只有mysql 4.1.*以上才支持这样的子查询.

2.获取数据时会牵涉很多SQL语句的拼接,如果数据结构变动,这些SQL需要改写

我们发现,这样的代码很简洁,但是问题也随之而来,我们SQL查询时候,还有很多复杂的联表查询如join操作,如此,拼接SQL还是不可避免的,这个复杂的问题,我们放在后面解决。

我们的WEB系统一定会和各种数据打交道,实际开发过程中,往往一个类对应了关系数据库的一张或多张数据表,这里就会出现两个问题。

图片 2

class MySQL extends DB{

 public function MySQL(){

  /*Config*/
  $this->IP='*';
  $this->ServerID='*';
  $this->ServerPassword='*';
  $this->DataBaseName='*';
  /*End of Config*/

  $this->connection=mysqli_connect($this->IP,$this->ServerID,$this->ServerPassword,$this->DataBaseName);

  if(!$this->connection){
   die('Could not connect'.$this->connection);
  }

  mysqli_query($this->connection,'set names utf8');
 }

 public function Execute($sql){
  return mysqli_query($this->connection,$sql); 
 }

 public function Query($sql){
  $result=mysqli_query($this->connection,$sql);
  $arr=array();
  while($row=mysqli_fetch_array($result)){
   $arr[]=$row;
  }
  return $arr;
 }
 public function Close(){
  mysqli_close($this->connection);
 }
}
interface IModel{
 public static function all();
 public static function get($id);
 public static function where($condition,$value);
}
TAG标签:
版权声明:本文由彩民之家高手论坛发布于彩民之家高手论坛,转载请注明出处:Laravel Query Builder 复杂查询案例:子查询实现分区