07/09/2018, 08:35
Laravel: Eloquent relationships (P2)
Ở bài trước mình đã giới thiệu đến các bạn cách để lấy ra danh sách categories kèo theo mỗi category là một post mới nhất. Còn bài này cũng với bài toán tương tự, nhưng không phải lấy là 1 post nữa mà là N post. Bài toán bắt đầu trở nên phức tạp hơn trước, chúng ta bắt đầu nhé: 1. Vấn đề Input ...
Ở bài trước mình đã giới thiệu đến các bạn cách để lấy ra danh sách categories kèo theo mỗi category là một post mới nhất. Còn bài này cũng với bài toán tương tự, nhưng không phải lấy là 1 post nữa mà là N post. Bài toán bắt đầu trở nên phức tạp hơn trước, chúng ta bắt đầu nhé:
1. Vấn đề
Input:
- Cho 2 bảng: Posts (id, title,..) và Comments (id, post_id, content,..) có quan hệ 1-N.
Output:
- Lấy ra danh sách posts, với mỗi post kèm theo N comment mới nhất.
Bảng Posts và Comments ở bài này cũng tương tự như bảng categories vs posts ở bài trước nhé.
2. Thực hiện
- Trước tiên, ta sẽ viết thử query sql, sử dụng biến để nhóm các comment thành các nhóm theo post_id:
mysql> set @group = 0; Query OK, 0 rows affected (0.00 sec) mysql> select id, created_at, post_id, @group := post_id as grp from comments order by post_id limit 10; +-----+---------------------+---------+-----+ | id | created_at | post_id | grp | +-----+---------------------+---------+-----+ | 344 | 2014-08-17 21:25:46 | 1 | 1 | | 320 | 2014-08-17 21:25:45 | 1 | 1 | | 4 | 2014-08-17 21:25:26 | 1 | 1 | | 72 | 2014-08-17 21:25:29 | 1 | 1 | | 158 | 2014-08-17 21:25:37 | 2 | 2 | | 423 | 2014-08-17 21:25:50 | 2 | 2 | | 59 | 2014-08-17 21:25:29 | 2 | 2 | | 227 | 2014-08-17 21:25:40 | 2 | 2 | | 308 | 2014-08-17 21:25:45 | 3 | 3 | | 34 | 2014-08-17 21:25:28 | 3 | 3 | +-----+---------------------+---------+-----+ 10 rows in set (0.00 sec)
- Lưu ý bạn không thể đặt alias là group được vì bị trùng với key của MySql, ở đây mình dùng grp, bạn có thể dùng alias khác tùy thích.
- Tiếp theo ta cần đánh rank cho các comments:
mysql> set @rank = 0; Query OK, 0 rows affected (0.00 sec) mysql> select id, created_at, post_id, @rank := @rank+1 as rank, @group := post_id as grp from comments order by post_id limit 10; +-----+---------------------+---------+------+-----+ | id | created_at | post_id | rank | grp | +-----+---------------------+---------+------+-----+ | 344 | 2014-08-17 21:25:46 | 1 | 1 | 1 | | 320 | 2014-08-17 21:25:45 | 1 | 2 | 1 | | 4 | 2014-08-17 21:25:26 | 1 | 3 | 1 | | 72 | 2014-08-17 21:25:29 | 1 | 4 | 1 | | 158 | 2014-08-17 21:25:37 | 2 | 5 | 2 | | 423 | 2014-08-17 21:25:50 | 2 | 6 | 2 | | 59 | 2014-08-17 21:25:29 | 2 | 7 | 2 | | 227 | 2014-08-17 21:25:40 | 2 | 8 | 2 | | 308 | 2014-08-17 21:25:45 | 3 | 9 | 3 | | 34 | 2014-08-17 21:25:28 | 3 | 10 | 3 | +-----+---------------------+---------+------+-----+ 10 rows in set (0.00 sec)
- Thêm if else để các rank được đánh theo từng group:
mysql> set @rank = 0, @group = 0; Query OK, 0 rows affected (0.00 sec) mysql> select id, created_at, post_id, @rank := IF(@group=post_id, @rank+1, 1) as rank, @group := post_id as grp from comments order by post_id limit 10; +-----+---------------------+---------+------+-----+ | id | created_at | post_id | rank | grp | +-----+---------------------+---------+------+-----+ | 344 | 2014-08-17 21:25:46 | 1 | 1 | 1 | | 320 | 2014-08-17 21:25:45 | 1 | 2 | 1 | | 4 | 2014-08-17 21:25:26 | 1 | 3 | 1 | | 72 | 2014-08-17 21:25:29 | 1 | 4 | 1 | | 158 | 2014-08-17 21:25:37 | 2 | 1 | 2 | | 423 | 2014-08-17 21:25:50 | 2 | 2 | 2 | | 59 | 2014-08-17 21:25:29 | 2 | 3 | 2 | | 227 | 2014-08-17 21:25:40 | 2 | 4 | 2 | | 308 | 2014-08-17 21:25:45 | 3 | 1 | 3 | | 34 | 2014-08-17 21:25:28 | 3 | 2 | 3 | +-----+---------------------+---------+------+-----+ 10 rows in set (0.00 sec)
- Đến đây việc lấy ra N comments mới nhất cho mỗi post khá là dễ dàng, ta chỉ việc thêm điều kiện where rank <= N :
mysql> select * from ( -> select id, created_at, post_id, @rank := IF(@group=post_id, @rank+1, 1) as rank, @group := post_id as grp -> from comments, (select @rank := 0, @group := 0) as vars -> order by post_id asc, created_at desc -> ) as comments where rank <= 2 limit 10; +-----+---------------------+---------+------+-----+ | id | created_at | post_id | rank | grp | +-----+---------------------+---------+------+-----+ | 344 | 2014-08-17 21:25:46 | 1 | 1 | 1 | | 320 | 2014-08-17 21:25:45 | 1 | 2 | 1 | | 423 | 2014-08-17 21:25:50 | 2 | 1 | 2 | | 227 | 2014-08-17 21:25:40 | 2 | 2 | 2 | | 308 | 2014-08-17 21:25:45 | 3 | 1 | 3 | | 83 | 2014-08-17 21:25:30 | 3 | 2 | 3 | | 428 | 2014-08-17 21:25:51 | 4 | 1 | 4 | | 351 | 2014-08-17 21:25:47 | 5 | 1 | 5 | | 276 | 2014-08-17 21:25:43 | 5 | 2 | 5 | | 444 | 2014-08-17 21:25:51 | 6 | 1 | 6 | +-----+---------------------+---------+------+-----+ 10 rows in set (0.01 sec) - Ở đây ta lấy ra mỗi post 2 comment, và thay vì dùng set @rank = 0, @group = 0; để set biến rank, group ta có thể dùng select @rank := 0, @group := 0, kết quả là tương đương nhau
- Và cuối ta sẽ biến query trên thành Eloquent trong laravel để dùng ở nhiều chỗ khác nhau, ta cần viết scope trong BaseModel
<?php class BaseModel extends Eloquent { /** * query scope nPerGroup * * @return void */ public function scopeNPerGroup($query, $group, $n = 10) { // queried table $table = ($this->getTable()); // initialize MySQL variables inline $query->from( DB::raw("(SELECT @rank:=0, @group:=0) as vars, {$table}") ); // if no columns already selected, let's select * if ( ! $query->getQuery()->columns) { $query->select("{$table}.*"); } // make sure column aliases are unique $groupAlias = 'group_'.md5(time()); $rankAlias = 'rank_'.md5(time()); // apply mysql variables $query->addSelect(DB::raw( "@rank := IF(@group = {$group}, @rank+1, 1) as {$rankAlias}, @group := {$group} as {$groupAlias}" )); // make sure first order clause is the group order $query->getQuery()->orders = (array) $query->getQuery()->orders; array_unshift($query->getQuery()->orders, ['column' => $group, 'direction' => 'asc']); // prepare subquery $subQuery = $query->toSql(); // prepare new main base QueryBuilder $newBase = $this->newQuery() ->from(DB::raw("({$subQuery}) as {$table}")) ->mergeBindings($query->getQuery()) ->where($rankAlias, '<=', $n) ->getQuery(); // replace underlying builder to get rid of previous clauses $query->setQuery($newBase); } }
- Sử dụng scope trong Post model:
<?php class Post extends BaseModel { /** * Get latest 5 comments from hasMany relation. * * @return IlluminateDatabaseEloquentRelationsHasMany */ public function latestComments() { return $this->comments()->latest()->nPerGroup('post_id', 5); } /** * Post has many Comments * * @return IlluminateDatabaseEloquentRelationsHasMany */ public function comments() { return $this->hasMany('Comment'); } }
- Lấy ra 5 comments đối với mỗi post:
[1] > $posts = Post::with('latestComments')->get(); // object(IlluminateDatabaseEloquentCollection)( // // ) [2] > DB::getQueryLog(); // array( // 0 => array( // 'query' => 'select * from `posts`', // 'bindings' => array( // // ), // 'time' => 1.46 // ), // 1 => array( // 'query' => 'select * from (select `comments`.*, @rank := IF(@group = post_id, @rank+1, 1) as rank_643b92db067a46e286c8e914151584a1, @group := post_id as group_643b92db067a46e286c8e914151584a1 from (SELECT @rank:=0, @group:=0) as vars, comments order by `post_id` asc, `created_at` desc) as comments where `rank_643b92db067a46e286c8e914151584a1` <= ? and `comments`.`post_id` in (?, ?, ... ?, ?)', // 'bindings' => array( // 0 => 5, // 1 => '1', // 2 => '2', ... // 150 => '150' // ), // 'time' => 12.6 // ) // )
- Trên đây là kỹ thuật đánh rank trong mysql khá hay, ngoài trường hợp mình giới thiệu ở trên bạn cũng có thể áp dụng nó không chỉ lấy top 5 comments mới nhất mà lấy top 5 comments nhiều có nhiều like nhất chẳng hạn. Hay như trong dự án gần đây của mình còn có yêu cầu: hiển thị danh sách comments theo số like giảm dần, sao cho mỗi post chỉ có tối đa 3 comments (bảng comments với bảng likes có quan hệ 1-N) cũng hoàn toàn có thể áp dụng được phương pháp này.
