我试图找出转换此mysql查询的最佳方法
SELECT
SUM(invite.friendID = $mID AND invite.decidedwhen = '0000-00-00 00:00:00' AND evnt.ends >= '$event_ts' and invite.isactive = 0 and evnt.isactive = 0) AS invites_undecided,
SUM(invite.friendID = $mID AND invite.decidedwhen != '0000-00-00 00:00:00' AND invite.yes = 1 AND evnt.ends >= '$event_ts' and invite.isactive = 0) and evnt.isactive = 0 AS invites_yes,
SUM(invite.friendID = $mID AND invite.decidedwhen != '0000-00-00 00:00:00' AND invite.no = 1 AND evnt.ends >= '$event_ts' and invite.isactive = 0 and evnt.isactive = 0) AS invites_no,
SUM(invite.friendID = $mID AND invite.decidedwhen != '0000-00-00 00:00:00' AND invite.maybe = 1 AND evnt.ends >= '$event_ts' and invite.isactive = 0 and evnt.isactive = 0) AS invites_maybe
FROM user_event_invite AS invite
JOIN user_event AS evnt ON evnt.eID = invite.eID
使用Codeigniter的活动活动格式。例如
$this->db->select()
->from('user_event')
->... something?
标准MySQL语句效果很好。但是以固定的名义,我想使用的所有活动记录。有什么方法吗?我找不到SUM()
(我尚未测试的答案比答案更多;但它仍然可能有所帮助)
活动记录有一个$this->db->select_sum();
功能,并且(这是我不确定的)您可能可以使用方法链。所以这是可以尝试的。
第1阶段是将常见的东西剥离到其中。如果索引的地方,它将加快速度。这将为您提供(也已修复了括号 - 您的SQL有错误)
SELECT
SUM(invite.decidedwhen = '0000-00-00 00:00:00') AS invites_undecided,
SUM(invite.decidedwhen != '0000-00-00 00:00:00' AND invite.yes = 1 ) AS invites_yes,
SUM(invite.decidedwhen != '0000-00-00 00:00:00' AND invite.no = 1) AS invites_no,
SUM(invite.decidedwhen != '0000-00-00 00:00:00' AND invite.maybe = 1) AS invites_maybe
FROM user_event_invite AS invite
JOIN user_event AS evnt ON evnt.eID = invite.eID
WHERE invite.friendID = $mID AND evnt.ends >= '$event_ts' and invite.isactive = 0 AND evnt.isactive = 0
另外,如果您可以通过Sayign接受,"是,否,也许都是独有的,那么您也可以剥离"决定时"以进一步简化。(理想情况下,您只是要进行"计数"并减去其余的,但尚不清楚如何在活动记录上的一个查询中执行此操作)。
SELECT
SUM(invite.decidedwhen = '0000-00-00 00:00:00') AS invitees_undecided,
SUM(invite.yes = 1 ) AS invites_yes,
SUM(invite.no = 1) AS invites_no,
SUM(invite.maybe = 1) AS invites_maybe
FROM user_event_invite AS invite
JOIN user_event AS evnt ON evnt.eID = invite.eID
WHERE invite.friendID = $mID AND evnt.ends >= '$event_ts' AND invite.isactive = 0 AND evnt.isactive = 0
现在使用该方法在活动记录上进行方法链:
$this->db->select_sum('invite.decidedwhen = '0000-00-00 00:00:00', 'invite_undecided')
->select_sum('yes', 'invite_yes')
->select_sum('no', 'invite_no')
->etc
->from('user_event_invite')
->join('user_event', 'user_event.eID=user_event_invite.eID)
->where('friendID', '$mID')
->where('user_event.ends >=', $event_ts)
-> etc
Like that you can write query :
==================================
$query="SELECT SUM(case when invite.friendID = " . $mID ." AND
invite.decidedwhen = '0000-00-00 00:00:00' AND evnt.ends >= '" . $event_ts . "'
and invite.isactive = 0 and evnt.isactive = 0) AS invites_undecided,...
FROM user_event_invite AS invite
left outer JOIN user_event AS evnt ON evnt.eID = invite.eID"