ThinkPHP's weird SQL query

I haven't used TP for a long time. I think TP has been used for a long time and I don't know why PHP is used. Come to the company and use TP2.1 again, OK, no problem, brother used 3 before. 1. the problem is that the daily attendance and monthly attendance statistics, my sweat, TP Association queries, and statistical queries are not fixed. if you can, efficiency... "/> <scripttype =" text/javascript "src =" http: // w

I haven't used TP for a long time. I think TP has been used for a long time and I don't know why PHP is used. Come to the company and use TP2.1 again, OK, no problem, brother used 3.1 before.
The problem is that the daily attendance and monthly attendance statistics are made. I am sweating, TP Association queries, and statistical queries are not fixed. if I can do this, the efficiency is not enough, as said Big Brother, I still don't know.
Refer to the Big Brother's SQL to change it:

 

ALTER TABLE `tbl_attendance` ADD COLUMN `check_unnormal` INT NULL default 1 AFTER `geo_normal`; ALTER TABLE `tbl_attendance` DROP COLUMN `real_name`;   SELECT usr_cal.user_id,usr_cal.real_name, IFNULL(SUM(checkintbl.nolate),0) AS checkin_late_sum, IFNULL (SUM(checkintbl.geo_normal),0) AS checkin_geo_unnormal_sum, IFNULL (SUM(checkouttbl.nolate),0) AS checkout_late_sum,  IFNULL (SUM(checkouttbl.geo_normal),0) AS checkout_geo_unnormal_sum, IFNULL (SUM(checkintbl.check_unnormal),0) AS checkin_check_sum, IFNULL (SUM(checkouttbl.check_unnormal),0) AS checkout_check_sum, IFNULL (SUM(COALESCE(checkintbl.nocheck,1)),0) AS checkin_nocheck_sum, IFNULL (SUM(COALESCE(checkouttbl.nocheck,1)),0) AS checkout_nocheck_sum FROM ( SELECT  usr.user_id,usr.real_name,workday.date FROM  (SELECT tbl_calendar.* FROM tbl_calendar  WHERE  DATE >='2013-07-01' AND DATE<='2013-07-06'  AND isorwork=0 ) workday, (SELECT tbl_user.* FROM tbl_user,tbl_group_member WHERE tbl_user.user_id = tbl_group_member.user_id AND tbl_group_member.group_id = 1 ) usr   ) usr_cal LEFT JOIN  (SELECT user_id,checkdate,nolate,geo_normal,0 AS nocheck,check_unnormal FROM tbl_attendance ,tbl_calendar WHERE TYPE = 1  AND  checkdate >='2013-07-01' AND checkdate <='2013-07-06' AND tbl_attendance.checkdate = tbl_calendar.date AND tbl_calendar.isorwork=0 ) checkintbl ON usr_cal.user_id = checkintbl.user_id AND usr_cal.date =checkintbl.checkdate LEFT JOIN  (SELECT user_id,checkdate,nolate,geo_normal,0 AS nocheck,check_unnormal FROM tbl_attendance,tbl_calendar WHERE TYPE = 2 AND  checkdate >='2013-07-01' AND checkdate <='2013-07-06' AND tbl_attendance.checkdate = tbl_calendar.date AND tbl_calendar.isorwork=0 ) checkouttbl ON usr_cal.user_id = checkouttbl.user_id AND usr_cal.date =checkouttbl.checkdate   GROUP BY usr_cal.user_id ALTER TABLE `tbl_attendance` ADD COLUMN `check_unnormal` INT NULL default 1 AFTER `geo_normal`;ALTER TABLE `tbl_attendance` DROP COLUMN `real_name`; SELECT usr_cal.user_id,usr_cal.real_name,IFNULL(SUM(checkintbl.nolate),0) AS checkin_late_sum,IFNULL (SUM(checkintbl.geo_normal),0) AS checkin_geo_unnormal_sum,IFNULL (SUM(checkouttbl.nolate),0) AS checkout_late_sum,IFNULL (SUM(checkouttbl.geo_normal),0) AS checkout_geo_unnormal_sum,IFNULL (SUM(checkintbl.check_unnormal),0) AS checkin_check_sum,IFNULL (SUM(checkouttbl.check_unnormal),0) AS checkout_check_sum,IFNULL (SUM(COALESCE(checkintbl.nocheck,1)),0) AS checkin_nocheck_sum,IFNULL (SUM(COALESCE(checkouttbl.nocheck,1)),0) AS checkout_nocheck_sumFROM(SELECT  usr.user_id,usr.real_name,workday.date FROM(SELECT tbl_calendar.* FROM tbl_calendarWHERE  DATE >='2013-07-01' AND DATE<='2013-07-06'AND isorwork=0 ) workday,(SELECT tbl_user.* FROM tbl_user,tbl_group_memberWHERE tbl_user.user_id = tbl_group_member.user_idAND tbl_group_member.group_id = 1) usr) usr_calLEFT JOIN(SELECT user_id,checkdate,nolate,geo_normal,0 AS nocheck,check_unnormal FROM tbl_attendance ,tbl_calendarWHERE TYPE = 1AND  checkdate >='2013-07-01' AND checkdate <='2013-07-06'AND tbl_attendance.checkdate = tbl_calendar.dateAND tbl_calendar.isorwork=0) checkintblON usr_cal.user_id = checkintbl.user_id AND usr_cal.date =checkintbl.checkdateLEFT JOIN(SELECT user_id,checkdate,nolate,geo_normal,0 AS nocheck,check_unnormal FROM tbl_attendance,tbl_calendarWHERE TYPE = 2AND  checkdate >='2013-07-01' AND checkdate <='2013-07-06'AND tbl_attendance.checkdate = tbl_calendar.dateAND tbl_calendar.isorwork=0) checkouttblON usr_cal.user_id = checkouttbl.user_id AND usr_cal.date =checkouttbl.checkdate

Group by usr_cal.user_id: I'm dizzy ...... A variety of SQL knowledge reviews, various queries, and finally understand, so I developed my own daily attendance SQL solution, with a sense of accomplishment. I can test it in the database,

$sql = "SELECT usr_cal.user_id,usr_cal.real_name,COALESCE(checktbl.nocheck,1)  AS nocheck,COALESCE(checktbl.nolate,0)  AS late, COALESCE(checktbl.geo_normal,0)  AS geo_normal,checktbl.checktime,checktbl.address FROM( SELECT  usr.user_id,usr.real_name,workday.date FROM  (SELECT tbl_calendar.* FROM tbl_calendar  WHERE  DATE="."'".$date."'"."  AND isorwork=0 ) workday, (SELECT tbl_user.* FROM tbl_user,tbl_group_member WHERE tbl_user.user_id = tbl_group_member.user_id AND tbl_group_member.group_id = $group_id AND tbl_user.type!=-11 and tbl_group_member.user_type = 4  ) usr ) usr_cal LEFT JOIN  (SELECT user_id,checkdate,nolate,checktime,geo_normal,0 AS nocheck,check_unnormal,address FROM (SELECT tbl_attendance.*,tbl_geo.address FROM tbl_attendance,tbl_geo WHERE tbl_attendance.geo_id = tbl_geo.geo_id AND tbl_attendance.group_id = $group_id ) attend ,tbl_calendar WHERE TYPE = $type  AND  checkdate ="."'".$date."'"." AND attend.checkdate = tbl_calendar.date AND tbl_calendar.isorwork=0 ) checktbl ON usr_cal.user_id = checktbl.user_id AND usr_cal.date =checktbl.checkdate where $where_search GROUP BY usr_cal.user_id ORDER  BY nocheck desc,late asc,geo_normal asc "； $sql = "SELECT usr_cal.user_id,usr_cal.real_name,COALESCE(checktbl.nocheck,1)  AS nocheck,COALESCE(checktbl.nolate,0)  AS late,COALESCE(checktbl.geo_normal,0)  AS geo_normal,checktbl.checktime,checktbl.addressFROM(SELECT  usr.user_id,usr.real_name,workday.date FROM(SELECT tbl_calendar.* FROM tbl_calendarWHERE  DATE="."'".$date."'"."AND isorwork=0 ) workday,(SELECT tbl_user.* FROM tbl_user,tbl_group_memberWHERE tbl_user.user_id = tbl_group_member.user_idAND tbl_group_member.group_id = $group_id AND tbl_user.type!=-11 and tbl_group_member.user_type = 4) usr) usr_calLEFT JOIN(SELECT user_id,checkdate,nolate,checktime,geo_normal,0 AS nocheck,check_unnormal,address FROM (SELECT tbl_attendance.*,tbl_geo.address FROM tbl_attendance,tbl_geoWHERE tbl_attendance.geo_id = tbl_geo.geo_id AND tbl_attendance.group_id = $group_id) attend ,tbl_calendarWHERE TYPE = $typeAND  checkdate ="."'".$date."'"."AND attend.checkdate = tbl_calendar.dateAND tbl_calendar.isorwork=0) checktblON usr_cal.user_id = checktbl.user_id AND usr_cal.date =checktbl.checkdate where $where_searchGROUP BY usr_cal.user_id ORDER  BY nocheck desc,late asc,geo_normal asc"；

Okay, let's hand it over to TP :.

This is what we say in both 2.1 and 3.1. Why can't I try it? why can't I check it? $ Model = M (); no online solution is available. ask Liang Ge, just instantiate an object. various attempts

$ Tag = M ('tag'); $ Tag = new Model ('tag'); $ tag-> query ('select * from tbl_tag '); I couldn't do this simply. I was completely speechless and crashed. $ tag = M ('tag'); $ Tag = new Model ('tag '); $ tag-> query ('select * from tbl_tag '); I am completely speechless and collapsed,

I told you everything about instantiating an object, opening GroupAction, and saying that I don't know how to say it to you.
Pasted $ group = D ('Group'); $ Group-> query ($ SQL); the result is displayed, so it is displayed. why is it so inflexible, I don't know how to change M and D,
How can this problem be solved? I told myself over and over again