Detailed examples of ThinkPHP5 union (association) query, multi-condition query and aggregation query

This article mainly introduces ThinkPHP5 joint (association) query, multi-condition query and aggregation query. It summarizes and analyzes common query operation techniques of thinkPHP5 in the form of examples. Friends in need can refer to the following

The examples of this article are described It supports ThinkPHP5 joint (association) query, multi-condition query and aggregation query. Share it with everyone for your reference, the details are as follows:

1. Union (association) query

1. Project table

DROP TABLE IF EXISTS `darling_project`;
CREATE TABLE `darling_project` (
 `project_id` int(32) NOT NULL AUTO_INCREMENT,
 `project_name` varchar(20) NOT NULL,
 `create_time` int(32) NOT NULL,
 PRIMARY KEY (`project_id`),
 UNIQUE KEY `project_id` (`project_id`),
 UNIQUE KEY `project_name` (`project_name`)
);

2. Version number table

DROP TABLE IF EXISTS `darling_version`;
CREATE TABLE `darling_version` (
 `version_id` int(32) NOT NULL AUTO_INCREMENT,
 `project_id` int(32) NOT NULL,
 `version_name` varchar(128) NOT NULL,
 `create_time` int(32) NOT NULL,
 PRIMARY KEY (version_id),
 UNIQUE KEY `version_id` (`version_id`)
);

3. Union query

$where=array(
  "version_id"=>$_POST['version_id']
);
$Project_version = model('Project')->join("darling_version","darling_version.project_id = darling_project.project_id")->where($where)->find();

2. Multi-condition query

Method 1:

Put the query conditions into the array As a where function parameter, but if there are conditional parameters such as greater than or less than, values ​​cannot be assigned in the array.

Example 1:

$where=array(
  "version_name"=>$version_name,
  "project_name"=>$project_name
);
$userdata=$this->where($where)->find();

Example 2:

$where=array(
   "version_name"=>$version_name,
   "project_name"=>$project_name
);
$userdata=$this->where($where)->select();

Example 3:

$where=array(
  "version_id"=>$version_id
);
$version_name = model("Version")->where($where)->field("version_name")->find();

Method 2:

Use multiple SQL query statements as where parameters, so that conditions such as greater than and less than are supported .

$package = model('admin/Package')
->where("project_id=".$Project_version['project_id']." and version_id=".$Project_version['version_id']." and status>1")
->order('create_time desc')
->find();

Method 3:

Put SQL query statements into multiple where functions

$package = model('admin/Package')
->where("project_id=".$Project_version['project_id'])
->where("version_id=".$Project_version['version_id'])
->where("status>1")
->order('create_time desc')
->find();

3. Aggregation max function

1. The latest inserted upgrade package can be returned as follows, but only the create_time field will be returned. Fields of the entire record cannot be returned.

$package = model('admin/Package')
->where("project_id=".$Project_version['project_id'])
->where("version_id=".$Project_version['version_id'])
->where("status>1")->max(create_time)

2. So you can use the following to find the latest inserted record and return the entire record field. First do order sorting, and then find the first one Record.

$package = model('admin/Package')
->where("project_id=".$Project_version['project_id'])
->where("version_id=".$Project_version['version_id'])
->where("status>1")
->order('create_time desc')
->find();

##Related recommendations:

ThinkPHP framework based on PDO method to connect database operation example

Detailed explanation of distributed database connection method of ThinkPHP framework

thinkPHP simple method to implement multiple subquery statements

The above is the detailed content of Detailed examples of ThinkPHP5 union (association) query, multi-condition query and aggregation query. For more information, please follow other related articles on the PHP Chinese website!