Shulou(Shulou.com)06/01 Report--

This article mainly introduces how Laravel sorts according to the number of items in the association model, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

Introduction

In laravel, we use models to manipulate database tables and use relational models such as hasOne belongTo hasMany to establish relationships between different models. This is perfectly manageable for simple query operations.

But the real business requirements are often full of variables, today we will talk about a requirement, sorted according to the number of associated models, how to write code.

Study time

We use examples to explain, the first is the table structure, in order to simplify the operation, only the main fields are listed. The first are the two fields of the hackathons table:

Id

Name

Begins

Ends

Description

Then there are the fields of the user table:

Id

Name

There is also an associated table hackathon_user field:

Hackathons_id

User_id

Well, the basic data has been established, and then we use laravel's model to manipulate the database tables. First declare the Hackathons model:

Class Hackathon extends Model {protected $fillable = ['name',' begins', 'ends',' description']; protected $table = 'hackathons';public function owner () {return $this- > belongsToMany (' User', 'hackathon_owner');} public function participants () {return $this- > belongsToMany (' User');} public function type () {return $this- > belongsToMany ('Type');}}

One of the Type models is not listed because it has nothing to do with the issues discussed in this article. Then define the model of the associated table:

Class HackathonParticipant extends Model {protected $fillable = ['hackathon_id',' user_id']; protected $table = 'hackathon_user';public function user () {return $this- > belongsTo (' User', 'user_id');} public function hackathon () {return $this- > belongsTo (' Hackathon', 'hackathon_id');}}

This is the basic class we need, and then let's talk about how to sort it. If you don't think about performance, or if the amount of data filtered is not enough for us to think about, you can return the entire result dataset first, and then use the laravel collection collection for sorting and manipulation. The code is written like this:

$hackathons = Hackathon::with ('participants')-> get ()-> sortBy (function ($hackathon) {return $hackathon- > participants- > count ();})

Note that after using get to get all the result datasets, the sortBy mode of the Collection class is called, and the default is ascending order. There is nothing wrong with this method when the amount of data is small.

If you have a large amount of data, often hundreds of thousands of pieces, then the above get method, absolutely can not be used, which will greatly increase the pressure of data transmission between MySQL and the server. Therefore, we need to try our best to solve this problem at the MySQL level.

Aggregate queries are also the strengths of relational databases, so what we need is to implement aggregate queries in laravel.

Laravel 5.3 and above, we can use withCount to query the aggregation of associated classes, with only one line of code:

Hackathon::withCount ('participants')-> orderBy (' participants_count', 'desc')-> paginate (10)

Note that the field names of the statistics classes automatically generated by laravel cannot be misspelled.

Another way is not to use the association model, but to use table joins to bypass the association relationships defined by the model. We post the implementation code directly:

$hackathons = Hackathon::leftJoin ('hackathon_user','hackathon.id','=','hackathon_user.hackathon_id')-> selectRaw (' hackathon.*, count (hackathon_user.hackathon_id) AS `count`)-> groupBy ('hackathon.id')-> orderBy (' count','DESC')-> paginate (5)

The only thing to pay attention to is the field of groupBy and the aggregate function specified by select.

Thank you for reading this article carefully. I hope the article "how to sort Laravel according to the number of items in the association model" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.