ThinkPHP talents, how to implement a very complex SQL query using the Chain Method of the TP framework?

{Code...} attachment is Database SQL backup. Let's take a look. This should be a very complicated SQL statement. Client running result database backup: 7xiqxd.com2.z0.glb.qiniucdn.comdeliver. SQL .zip

SELECT 'app _ plan '. *, 'app _ agreement '. *, 'app _ customer '. *, 'app _ product '. *, 'app _ product_category '. *, @ total plan primary volume: = (select sum ('app _ operation '. 'Send _ weight ') from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Is _ del '= 0) as 'Plan _ total_send_weight', @ total planned actual amount: = (select sum ('app _ operation '. 'receive _ weight ') from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Is _ del '= 0) as 'Plan _ total_receive_weight', @ total planned route loss: = IFNULL (select sum ('app _ operation '. 'Send _ weight '-'app _ operation '. 'receive _ weight ') from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Is _ del' = 0 AND 'app _ operation '. 'Operation _ status' = 2), 0) as 'Plan _ total_lost_quantity ', @ total planned amount in transit: = IFNULL (select sum ('app _ operation '. 'Send _ weight ') from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Is _ del' = 0 AND 'app _ operation '. 'Operation _ status' = 1), 0) as 'Plan _ onway_quantity ', @ planned loss exceeding: = IFNULL (SELECT sum ('app _ operation '. 'Send _ weight '-'app _ operation '. 'receive _ weight '-'app _ plan '. 'rational _ loss') from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' and 'app _ operation '. 'Is _ del' = 0 AND 'app _ operation '. 'Operation _ status' = 2 AND ('send _ weight'-'receive _ weight')> 'app _ plan '. 'rational _ loss'), 0) AS plan_over_lost, @ excessive damage deduction: = truncate (IFNULL (SELECT sum ('app _ operation '. 'Send _ weight '-'app _ operation '. 'receive _ weight '-'app _ plan '. 'rational _ loss') * 'app _ plan '. 'Plan _ deduct_operation') from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' and 'app _ operation '. 'Is _ del' = 0 AND 'app _ operation '. 'Operation _ status' = 2 AND ('send _ weight'-'receive _ weight')> 'app _ plan '. 'rational _ loss'), 0), 2) AS plan_total_punish_numbers, @ total number of scheduled vehicles: = IFNULL (select count (*) from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Is _ del '= 0), 0) as 'Plan _ count_send', @ total number of planned vehicles: = IFNULL (select count (*) from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Operation _ status' = 2 AND 'app _ operation '. 'Is _ del' = 0), 0) as 'Plan _ count_receive ', @ total freight: = truncate (IFNULL (SELECT sum ('app _ operation '. 'receive _ weight '* 'app _ operation '. 'Operation _ operation') FROM 'app _ operation' WHERE 'app _ operation '. plan_id = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Is _ del '= 0), 0), 2) as 'Plan _ total_ship_fee', @ handle freight: = truncate (IFNULL (@ total freight-@ road damage beyond deduction), 0), 2) AS total_invoice_ship_amount, @ planned unreleased volume: = truncate (IFNULL ('Plan _ total_quantity '-@ total planned actual amount-@ total planned amount in transit, 0), 2) AS 'Plan _ total_not_quantity 'FROM ('app _ plan') left join 'app _ agreement' ON 'app _ plan '. 'agreement _ id' = 'app _ agreement '. 'agreement _ id' left join 'app _ customer' ON 'app _ agreement '. 'Customer _ id' = 'app _ customer '. 'Customer _ id' left join 'app _ product' ON 'app _ agreement '. 'product _ id' = 'app _ product '. 'product _ id' left join 'app _ product_category 'ON 'app _ product '. 'category _ id' = 'app _ product_category '. 'category _ id' WHERE 'Plan _ status' = 1 AND 'app _ plan '. 'Is _ del '= 0

The attachment is a database SQL backup.
Let's take a look. This should be a very complicated SQL statement.

Client running result

Database Backup: http://7xiqxd.com2.z0.glb.qiniucdn.com/deliver. SQL .zip

Reply content:

SELECT 'app _ plan '. *, 'app _ agreement '. *, 'app _ customer '. *, 'app _ product '. *, 'app _ product_category '. *, @ total plan primary volume: = (select sum ('app _ operation '. 'Send _ weight ') from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Is _ del '= 0) as 'Plan _ total_send_weight', @ total planned actual amount: = (select sum ('app _ operation '. 'receive _ weight ') from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Is _ del '= 0) as 'Plan _ total_receive_weight', @ total planned route loss: = IFNULL (select sum ('app _ operation '. 'Send _ weight '-'app _ operation '. 'receive _ weight ') from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Is _ del' = 0 AND 'app _ operation '. 'Operation _ status' = 2), 0) as 'Plan _ total_lost_quantity ', @ total planned amount in transit: = IFNULL (select sum ('app _ operation '. 'Send _ weight ') from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Is _ del' = 0 AND 'app _ operation '. 'Operation _ status' = 1), 0) as 'Plan _ onway_quantity ', @ planned loss exceeding: = IFNULL (SELECT sum ('app _ operation '. 'Send _ weight '-'app _ operation '. 'receive _ weight '-'app _ plan '. 'rational _ loss') from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' and 'app _ operation '. 'Is _ del' = 0 AND 'app _ operation '. 'Operation _ status' = 2 AND ('send _ weight'-'receive _ weight')> 'app _ plan '. 'rational _ loss'), 0) AS plan_over_lost, @ excessive damage deduction: = truncate (IFNULL (SELECT sum ('app _ operation '. 'Send _ weight '-'app _ operation '. 'receive _ weight '-'app _ plan '. 'rational _ loss') * 'app _ plan '. 'Plan _ deduct_operation') from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' and 'app _ operation '. 'Is _ del' = 0 AND 'app _ operation '. 'Operation _ status' = 2 AND ('send _ weight'-'receive _ weight')> 'app _ plan '. 'rational _ loss'), 0), 2) AS plan_total_punish_numbers, @ total number of scheduled vehicles: = IFNULL (select count (*) from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Is _ del '= 0), 0) as 'Plan _ count_send', @ total number of planned vehicles: = IFNULL (select count (*) from 'app _ operation' where 'app _ operation '. 'Plan _ id' = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Operation _ status' = 2 AND 'app _ operation '. 'Is _ del' = 0), 0) as 'Plan _ count_receive ', @ total freight: = truncate (IFNULL (SELECT sum ('app _ operation '. 'receive _ weight '* 'app _ operation '. 'Operation _ operation') FROM 'app _ operation' WHERE 'app _ operation '. plan_id = 'app _ plan '. 'Plan _ id' AND 'app _ operation '. 'Is _ del '= 0), 0), 2) as 'Plan _ total_ship_fee', @ handle freight: = truncate (IFNULL (@ total freight-@ road damage beyond deduction), 0), 2) AS total_invoice_ship_amount, @ planned unreleased volume: = truncate (IFNULL ('Plan _ total_quantity '-@ total planned actual amount-@ total planned amount in transit, 0), 2) AS 'Plan _ total_not_quantity 'FROM ('app _ plan') left join 'app _ agreement' ON 'app _ plan '. 'agreement _ id' = 'app _ agreement '. 'agreement _ id' left join 'app _ customer' ON 'app _ agreement '. 'Customer _ id' = 'app _ customer '. 'Customer _ id' left join 'app _ product' ON 'app _ agreement '. 'product _ id' = 'app _ product '. 'product _ id' left join 'app _ product_category 'ON 'app _ product '. 'category _ id' = 'app _ product_category '. 'category _ id' WHERE 'Plan _ status' = 1 AND 'app _ plan '. 'Is _ del '= 0

The attachment is a database SQL backup.
Let's take a look. This should be a very complicated SQL statement.

Client running result

Database Backup: http://7xiqxd.com2.z0.glb.qiniucdn.com/deliver. SQL .zip

Why do you have to use a chain to solve this complexity? If all your SQL statements come out, can you just use a direct SQL statement. The TP Model encapsulates several common simple query and update operations for ease of use. For complex operations, we recommend that you directly implement SQL. Even if you have racked your brains to come up with a complicated way to write this complex operation through the TP chain method, the TP still needs to convert your operation into SQL at last. Do you say you are not so hard?

It is not recommended to convert such complex SQL statements into TP SQL statements. The chain mode of TP will eventually be converted into an SQL statement. You can write the SQL statement first, and then use M to instantiate the database and execute the SQL statement. There are internal and external links in such a complex SQL statement, which will consume a lot of SQL resources. If the data volume is large, it is not easy to obtain data. We recommend that you split the SQL statement, use php to write algorithms after obtaining basic data, which is faster.

Do you want efficiency or want to structure the sentence?
Calculate the efficiency directly by SQL.
To understand the sentence, use the Model to complete the sentence assignment.