Detailed explanation of ThinkPHP5 combined with of associated query multi condition query and aggregated query instance

This article gives examples of ThinkPHP5 union (association) queries, multi-condition queries, and aggregation queries. Share it for your reference, as follows:

1. Join (associate) queries

1. List of items

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. Joint 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:

The query criteria are placed in an array as parameters of the where function, but if there are parameters greater than or less than such conditions, the array cannot be assigned values.

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:

Multiple SQL query statements are used as where parameters, thus supporting the greater than less condition.

$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:

Placing 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. Aggregate max functions

1. You can return the newly inserted upgrade package as follows, but only the field create_time will be returned, and the field of the whole 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. Therefore, you can use the following to find out the latest inserted record and return the whole record field, first do order sorting, and then find the first 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();

For more readers interested in thinkPHP related contents, please check the topics of this site: "ThinkPHP Introduction Tutorial", "thinkPHP Template Operation Skills Summary", "ThinkPHP Common Methods Summary", "codeigniter Introduction Tutorial", "CI (CodeIgniter) Framework Advanced Tutorial", "Zend FrameWork Framework Introduction Tutorial" and "PHP Template Technology Summary".

I hope this article is helpful to the PHP programming based on ThinkPHP framework.