Explain ThinkPHP6 joint table aggregation query from examples

A few days ago, a friend asked about an aggregation query of a joint table. Thinking that this problem may be difficult for many novices to deal with, I would like to share it now.

We have two data tables:

bus table:

##user table:

Requirements: Count the number of people getting on the bus

How to achieve it?

Step One: Join Tables

#This scenario definitely requires joining two data tables. Let’s not consider statistics first. Join two data tables.

 $data=Db::name('user')->alias('a')->join('bus b','a.user_id=b.user_id')->select()->toArray();

alias is an alias, join is the data table of the joint table, and there is a joint table condition a.user_id=b.user_id, so that we can obtain the data of the joint table of the two data tables.

Step 2: Aggregation query

Before doing the aggregation query, let’s take a look at the official Manual tutorial.

Because we ultimately want to obtain the number of statistics, we first determine to use the count() method, so we modify the query statement:

$data=Db::name('user')->alias('a')->field('count(b.user_id) AS c'')->join('bus b','a.user_id=b.user_id')->select()->toArray();

Among them c, is an alias.

We need to perform an aggregate query based on the user_id field. Statistics are based on this field, so we must be group(user_id), that is, grouping according to the user_id field.

We continue to modify the query statement:

$data=Db::name('user')->alias('a')->field('count(b.user_id) AS c')->join('bus b','a.user_id=b.user_id')->group('a.user_id')->select()->toArray();

In this way, we achieve our final query result.

The third point: Pay attention to the situation

In the above query statement, if mysql is version 5.7, you need to pay special attention. For example, if you add a.* to the field in mysql5.7, an error will be reported:

 $data=Db::name('user')->alias('a')->field('a.*,count(b.user_id) AS c')->join('bus b','a.user_id=b.user_id')->group('a.user_id')->select()->toArray();
 [object Object]

Why is there such an error?

MYSQL5.7 has restrictions on sql_mode for better performance.

ONLY_FULL_GROUP_BY: For GROUP BY aggregation operation, if the column in SELECT does not appear in GROUP BY, then this SQL is illegal because the column is not in the GROUP BY clause, which is an error. location.

We can modify the mysql configuration:

Modify /etc/my.cnf and delete only_full_group_by in sql_mode=

In this way, an aggregation query of a joint table is realized. When we encounter this problem, don't be impatient. According to the final needs, we will split it step by step and implement it step by step.

The above is the detailed content of Explain ThinkPHP6 joint table aggregation query from examples. For more information, please follow other related articles on the PHP Chinese website!