case

Case study: Laravel attaches the top 10 comments to his list of articles? At the same time, the first 10 comments of each article are queried together when getting the list of articles.

This is a typical case of partition query, which needs to be partitioned according to the post_id field in the comments table, and sorted according to the conditions. The first N items that meet the requirements are data.

Other databases (Oracle, SQL Server, Vertica) contain functions such as row_number partition by, which can be easily implemented.

For example, in 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;

In mysql, it's a bit more complicated. Let's first look at several solutions to the requirements in the above case.

Solution

Method 1:

Post - > comments () - > limit (10) where comment data is displayed in blade

Method 2:

Take out all comments data of Post directly through with and post - > comments - > take (10) in blade.

Method 3:

$posts = Post::paginate(15);

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

//Find qualified comments and define them at the same time @post, @rank Variables, no use here all,get Equal function, which is not executed at this time SQL Sentence.
$sub = Comment::whereIn('post_id',$postIds)->select(DB::raw('*,@post := NULL ,@rank := 0'))->orderBy('post_id');

//The sql query constructed above is queried as a sub-table and partitioned according to post_id. @rank Variable constant+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'));

//Take out the qualified front10 strip 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);
});

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

Knowledge points

1. The purpose of the toSql() method is to obtain SQL without binding parameters, that is, SQL with question marks.
2. The getQuery() method is used to get the binding parameter and get the question mark of SQL instead of toSql(), so as to get the complete SQL.
3. The function of raw() is to nest SQL directly into Laravel's query constructor.
4. Define variables @post:= NULL, @rank:= 0 in mysql query statements and use of IF functions
5. How to construct subqueries.

Why not use native SQL statements directly?

The reason why we insist on using Laravel Query Builder is that we can effectively prevent SQL injection and associate it with ORM's Model object.