Moment For Technology

Laravel-model implements complex query statements

Posted on June 23, 2022, 12:11 p.m. by Miss Anne Moore
Category: The back-end Tag: The back-end

The article directories

Query tips

Let's start by introducing a few Laravel syntax sugars that can help us quickly obtain the desired query results and improve coding efficiency.

Sometimes we want to retrieve not a row or rows, but the value of a particular field. Of course, you can query a row and retrieve the value of the specified field from the result object, but Laravel provides a more convenient syntax:

$id = $id; $email = DB::table('users')-where('name', $name)-value('email');Copy the code

In this way, the value method returns the value of the specified field without additional judgment and extraction operations.

If you want to determine whether a field value exists in a database, you can use the exists method quickly:

$exists = DB::table('users')-where('name', $name)-exists();
Copy the code

Return true if it exists, false otherwise. DoesntExist () is the opposite of this method.

You've probably had the experience of constructing an associative array with a primary key ID value and a field value as the value of a given query result from a database. Before, you might have had to iterate over the query result to construct an array. In Laravel, we simply call pluck on the query builder: pluck

$users = DB::table('users')-where('id', '', 10)-pluck('name', 'id');
Copy the code

The query returns the following results:

Notice that when we pass the parameter to the pluck method, the key field comes after and the value field comes before.

In addition, sometimes the result set returned from the database is too large to be processed at one time, which may exceed the PHP memory limit. In this case, we can use the chunk method to divide the result set into multiple blocks and return them for processing successively:

$names = []; DB::table('users')-orderBy('id')-chunk(5, function ($users) use ($names) { foreach ($users as $user) { $names[] = $user-name; }});Copy the code

The above code indicates that users are sorted by the ascending ID field, then the result set is returned five at a time, and the user names are placed in the $NAMES array. Print $names as follows:

Aggregation function

In the development of background management system, it is often necessary to conduct statistics, sum, calculate the average value, minimum value, maximum value, etc., corresponding method names are count, sum, AVG, min, Max:

$num = DB::table('users')-count(); $sum = DB::table('users')-sum('id'); $avg = DB::table('users')-avg('id'); $min = DB::table('users')-min('id'); $Max = DB::table('users')- Max ('id'); # Maximum 9Copy the code

Advanced Where query

We have already used the WHERE method to build a query clause, and here we systematically walk through the various constructs of the WHERE query clause.

The basic query

The basic query

The most basic WHERE query clause is a simple query using the WHERE method:

DB::table('posts')-where('views', 0)-get(); # here equals can omit the DB: : table (" posts ") -  where (' views' and '', 0) -  get (); DB::table('posts')-where('views', '', 0)-get();Copy the code

The first parameter represents the field name, the second parameter represents the operator (all SQL operators are supported), and the third parameter represents the comparison value.

Like the query

Sometimes we might do fuzzy queries on fields, especially for string matches:

DB: : table (" posts ") -  where (' title ', 'like', 'Laravel college %') -  get ();Copy the code

And the query

What if there are multiple WHERE conditions? In the query builder, this can be done easily with method chains:

DB::table('posts')-where('id', '', 10)-where('views', '', 0)-get();
Copy the code

Where id 10 and views 0 where id 10 and views 0 Alternatively, we can do the same thing by passing in an array parameter:

DB::table('posts')-where([
    ['id', '', 10],
    ['views', '', 0]
])-get();
Copy the code

The or query

Queries with or conditions are also common in everyday queries, which can be implemented in the query builder using the orWhere method:

DB::table('posts')-where('id', '', 10)-orWhere('views', '', 0)-get();
Copy the code

Where id 10 or views 0; where id 10 or views 0; where id 10 or views 0

Between the query

In some queries involving numbers and times, the BETWEEN statement can come in handy to retrieve records in a specified range. In the query builder, we can implement the Between query with the whereBetween method:

DB::table('posts')-whereBetween('views', [10, 100])-get();
Copy the code

Get the database record for where View between 10 and 100. In contrast, there is a whereNotBetween method that gets database records that are not in a specified range:

DB::table('posts')-whereNotBetween('views', [10, 100])-get();
Copy the code

WHERE views not between 10 and 100

You can see that the BETWEEN statement can be replaced by an and/or query, but it's much simpler to use between.

In the query

IN queries are also common, such as when we need to query for field values that are a subset of a set of sequences. IN queries can be implemented using the groupby method:

DB::table('posts')-whereIn('user_id', [1, 3, 5, 7, 9])-get();
Copy the code

The corresponding WHERE clause is WHERE user_id in (1, 3, 5, 7, 9). When using this method, be careful that the second argument passed to the department cannot be an empty array, or an error will be reported.

Similarly, as a counterpoint, there is a whereNotIn method that represents the query condition as opposed to having by. Change the having method in the above code to whereNotIn, which corresponds to the query clause where user_id not in (1, 3, 5, 7, 9).

Null query

A NULL query is a query that determines whether a field is empty, and the Laravel query builder provides a whereNull method to implement this query:

DB::table('users')-whereNull('email_verified_at')-get();
Copy the code

The corresponding WHERE query clause is WHERE email_verified_at is NULL. Similarly, this method has an equivalent whereNotNull method, for example, To implement the WHERE email_verified_at is not NULL query, you can do this:

DB::table('users')-whereNotNull('email_verified_at')-get();
Copy the code

Date query

For everyday queries, the query builder provides us with a wealth of methods, covering from year, month and day to specific times:

DB::table('posts')-whereYear('created_at', '2018')-get(); DB: # years: the table (" posts ") -  whereMonth (' created_at ', '11') -  get (); DB: # month: the table (" posts ") -  whereDay (' created_at ', '28') -  get (); # a month how many days the DB: : table (" posts ") -  whereDate (' created_at ', '2018-11-28') -  get (); # date the DB: : table (" posts ") -  whereTime (' created_at ', 'then') -  get (); # timeCopy the code

These methods also support orWhereYear, orWhereMonth, orWhereDay, orWhereDate, and orWhereTime.

Field equality query

Sometimes, instead of comparing fields to specific values, we compare the fields themselves, and the query builder provides whereColumn methods to do this:

DB::table('posts')-whereColumn('updated_at', '', 'created_at')-get();
Copy the code

The corresponding WHERE query clause is WHERE updated_AT created_AT.

JSON query

MySQL 5.7, MySQL 5.7, MySQL 5.7, MySQL 5.7, MySQL 5.7, MySQL 5.7, MySQL 5.7, MySQL 5.7, MySQL 5.7, MySQL 5.7, MySQL 5.7

DB::table('users')
    -where('options-language', 'en')
    -get();
Copy the code

If the property field is an array, it is also supported to include the array with the whereJsonContains method:

DB::table('users')
    -whereJsonContains('options-languages', 'en_US')
    -get();
    
DB::table('users')
    -whereJsonContains('options-languages', ['en_US', 'zh_CN'])
    -get();
Copy the code

Advanced query

Parameters of the group

In addition to these regular WHERE queries, the query builder also supports more complex query statements. Consider the following SQL statement:

select * from posts where id = 10 or (views  0 and created_at  '2018-11-28 14:00');
Copy the code

It looks like we can't solve this query construction with the methods we learned earlier, so we need to introduce a more complex construction method, which is to introduce anonymous functions (similar to the complex join condition construction in join query) :

DB::table('posts')-where('id', '=', 10)-orWhere(function ($query) {
    $query-where('views', '', 0)
        -whereDate('created_at', '', '2018-11-28')
        -whereTime('created_at', '', '14:00');
})-get();
Copy the code

The $query variable passed in this anonymous function is also an instance of the query builder. This approach to query construction, called "parameter grouping," can be used to build queries in complex parenthesized WHERE query clauses.

WHERE EXISTS

In addition, we can build a WHERE EXISTS query using the whereExists method provided by the query builder:

DB::table('users')
    -whereExists(function ($query) {
        $query-select(DB::raw(1))
            -from('posts')
            -whereRaw('posts.user_id = users.id');
    })
-get();
Copy the code

The corresponding SQL statement is:

select * from `users` where exists (select 1 from `posts` where posts.user_id = users.id);
Copy the code

Used to query users who have published articles.

The subquery

Sometimes, we will use subqueries to associate different tables. Consider the following SQL statement:

select * from posts where user_id in (select id from users where email_verified_at is not null);
Copy the code

For this SQL statement, we can implement the subquery provided by the query builder:

$users = DB::table('users')-whereNotNull('email_verified_at')-select('id');
$posts = DB::table('posts')-whereInSub('user_id', $users)-get();
Copy the code

IN addition to the IN query, a normal WHERE query can also use a subquery, the corresponding method is whereSub, but the subquery is not as efficient as the join query, so let's explore the use of join queries IN the query builder.

Join queries

Related terms

Before introducing join queries, you need to be familiar with several types of SQL join queries. SQL join queries usually fall into the following categories:

  • Inner join: Use comparison operators to compare tables and query data matching join conditions, which can be subdivided into equivalent join and unequal join

    • Equivalent connection (=) : such asselect * from posts p inner join users u on p.user_id = u.id
    • Unequal join (, , , etc.) : such asselect * from posts p inner join users u on p.user_id u.id
  • External links:

    • Left join: Returns all rows in the left table. If a row in the left table does not match a row in the right table, the corresponding column in the right table returns a null value, as shown in the following figureselect * from posts p left join users u on p.user_id = u.id
    • Right join: In contrast to the left join, all rows in the right table are returned. If a row in the right table does not match a row in the left table, the corresponding column in the left table returns a null value in the result, as inselect * from posts p right join users u on p.user_id = u.id
    • Full join: Returns all rows in the left and right tables. When a row does not match a row in another table, a column in the other table returns a null value, such asselect * from posts p full join users u on p.user_id = u.id
  • Cross join: Also called the Cartesian product, without the WHERE clause, returns the cartesian product of the two tables being joined, the number of rows equal to the product of the rows of the two tables, or the number of rows matching if there is a WHERE clause. Select * from posts P cross join users u on p.usser_id = U. id

If the text is too abstract, the icon is clear:

Note: When writing SQL statements, OUTER can be omitted.

Create and populate the Posts table

For the purposes of the following demonstration, we will create a new posts data table and first create the corresponding migration file:

php artisan make:migration create_posts_table --create=posts
Copy the code

CreatePostsTable; CreatePostsTable;

public function up() { Schema::create('posts', function (Blueprint $table) { $table-increments('id'); $table -  string (' title ') -  comment (' title '); $table -  text (' content ') -  comment (' content '); $table-integer('user_id')-unsigned()-default(0); $table -  integer (' views') -  unsigned () -  default (0) -  comment (' views'); $table-index('user_id'); $table-timestamps(); }); }Copy the code

Run PHP Artisan Migrate to create posts tables. Then create a model class for the table:

php artisan make:model Post
Copy the code

Next, we create a model factory for this model class:

php artisan make:factory PostFactory --model=Post
Copy the code

Write the database model factory/factories/PostFactory. PHP code is as follows:

? php use Faker\Generator as Faker; $factory-define(\App\Post::class, function (Faker $faker) { return [ 'title' = $faker-title, 'content' = $faker-text, 'user_id' = mt_rand(1, 15), 'views' = $faker-randomDigit ]; });Copy the code

Then create the fill class for the Posts table:

php artisan make:seeder PostsTableSeeder
Copy the code

In the newly generated padding class PostsTableSeeder in the Database/Seeds directory, call the model factory to populate the data table:

?php

use Illuminate\Database\Seeder;

class PostsTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        factory(\App\Post::class, 30)-create();
    }
}
Copy the code

This way, we can populate the posts data table with the following Artisan command:

php artisan db:seed --class=PostsTableSeeder
Copy the code

In the connection

Let's first look at how inner joins are implemented in the query builder, using equivalent joins as an example:

$posts = DB::table('posts')
    -join('users', 'users.id', '=', 'posts.user_id')
    -select('posts.*', 'users.name', 'users.email')
    -get();
Copy the code

This code is easy to understand compared to the previous SQL example of equivalent join, which corresponds to the SQL statement:

select posts.*, users.name, users.email from posts inner join users on users.id = posts.user_id;
Copy the code

In the query builder we implement inner joins (both equivalent and unequal joins) through the join method. The result of the above query through the query builder is:

[root@posts] [root@posts] [root@posts] [root@posts] [root@posts] [root@posts] [root@posts] Select ('posts.*', 'users.name as username', 'users.email').

Left connection

A leftJoin, also known as a left outer join, can be implemented in the query builder using the leftJoin method:

$posts = DB::table('posts')
    -leftJoin('users', 'users.id', '=', 'posts.user_id')
    -select('posts.*', 'users.name', 'users.email')
    -get();
Copy the code

The corresponding SQL statement is:

select posts.*, users.name, users.email from posts left join users on users.id = posts.user_id;
Copy the code

In this case, because each posts.user_id has a corresponding Users record, the above query results are consistent with the equivalent join query results.

The right connection

A rightJoin, also known as a right outer link, can be implemented in the query builder through the rightJoin method:

$posts = DB::table('posts')
    -rightJoin('users', 'users.id', '=', 'posts.user_id')
    -select('posts.*', 'users.name', 'users.email')
    -get();
Copy the code

The corresponding SQL statement is as follows:

select posts.*, users.name, users.email from posts right join users on users.id = posts.user_id;
Copy the code

In this case, not every user has a corresponding posts record, so some posts records are empty:

Other join statements

The above three types of join statements are relatively common. The query builder does not provide a separate method to support full join, but does support crossJoin. The corresponding method crossJoin is similar to the above types of query, so it will not be demonstrated separately here.

More complex connection conditions

Sometimes, your join query criteria may be complex, such as the following:

select posts.*, users.name, users.email from posts inner join users on users.id = posts.user_id and users.email_verified_at is not null where posts.views  0;
Copy the code

At this point, we can construct the above query statement by assembling the conditions of the join query using anonymous functions:

$posts = DB::table('posts')
    -join('users', function ($join) {
        $join-on('users.id', '=', 'posts.user_id')
            -whereNotNull('users.email_verified_at');
    })
    -select('posts.*', 'users.name', 'users.email')
    -where('posts.views', '', 0)
    -get();
Copy the code

We can call all the Where query clauses on the $Join instance of the anonymous function to assemble the join query conditions we need. The above query will filter out the results of the corresponding user's mailbox which is not verified and the number of articles viewed is 0:

The joint query

The query builder also supports merging multiple query results using the Union method:

$posts_a = DB::table('posts')-where('views', 0);
$posts_b = DB::table('posts')-where('id', '=', 10)-union($posts_a)-get();
Copy the code

Select * from iviews = 0 where id = 10;

The corresponding SQL statement is:

(select * from `posts` where `id` = 10) union (select * from `posts` where `views` = 0)
Copy the code

In addition, the query builder also supports a UNION ALL query. The corresponding method is unionAll, which differs from UNION in that it allows duplicate records. If you change the UNION method in the above code to unionAll, you will find a duplicate record in the query result:

The sorting

The query builder provides an orderBy method for this purpose. For example, if we want to sort the list of articles in reverse orderBy the time they were created, we can do this:

$users = DB::table('posts')
    -orderBy('created_at', 'desc')
    -get();
Copy the code

The corresponding SQL statement is as follows:

select * from `posts` order by `created_at` desc;
Copy the code

If the sort is in ascending order, it can be implemented like this:

DB::table('posts')-orderBy('created_at')-get();
Copy the code

The default collation is ascending, so the second argument asc can be omitted.

The query builder also supports random sorting via the inRandomOrder method:

DB::table('posts')-inRandomOrder()-get();
Copy the code

Note: You can use random sorting for small result sets. Do not use random sorting for large result sets because of poor performance.

grouping

The query builder also provides the groupBy method to group result sets:

$posts = DB::table('posts')
    -groupBy('user_id')
    -selectRaw('user_id, sum(views) as total_views')
    -get();
Copy the code

The SQL statement corresponding to the above code is:

select user_id, sum(views) as total_views from `posts` group by `user_id`;
Copy the code

Count the total number of views per user from the user_id dimension:

If we wanted to further filter the group results, we could use the HAVING method. For example, to filter out the group results with a total view of 10 or more, we could do this:

$posts = DB::table('posts')
    -groupBy('user_id')
    -selectRaw('user_id, sum(views) as total_views')
    -having('total_views', '=', 10)
    -get();
Copy the code

The corresponding SQL statement is:

select user_id, sum(views) as total_views from `posts` group by `user_id` having `total_views` = 10;
Copy the code

The query result is as follows:

paging

Another common query scenario in daily development is paging queries, and there are two ways to do this in the query builder.

The first is paging through a combination of skip method and take method. The parameter passed in SKIP means the number of records to start from, and the parameter passed in take means the number of records to get at one time:

$posts = DB::table('posts')-orderBy('created_at', 'desc')
    -where('views', '', 0)
    -skip(10)-take(5)
    -get();
Copy the code

The corresponding SQL statement is:

select * from `posts` where `views`  0 order by `created_at` desc limit 5 offset 10;
Copy the code

The query filters and sorts by query criteria and sort criteria, and returns five records starting with record 10.

The other method is to use the combination of offset method and limit method to perform paging query. Offset means the number of records to start from, and limit means the number of records to obtain at a time, which is similar to the use of SKIP and take:

$posts = DB::table('posts')-orderBy('created_at', 'desc')
    -where('views', '', 0)
    -offset(10)-limit(5)
    -get();
Copy the code

The corresponding SQL statement is as follows:

select * from `posts` where `views`  0 order by `created_at` desc limit 5 offset 10;
Copy the code

The underlying SQL statement ends up executing exactly the same, so you can do it either way.

Search
About
mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.