使用SUM()将MySQL查询语句转换为Codeigniter活动记录样式查询语句

Question

我正在尝试找出转换此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

添加到使用Codeigniters活动记录格式的内容。例如

$this->db->select()
  ->from('user_event')
  ->... something?

标准的mysql语句运行得很好。但是以保持统一的名义，我想要使用活动记录。有什么办法做到这一点吗？我找不到给SUM()的东西

Answer 1

(更多的是一个评论而不是一个答案，因为我还没有测试过它；但它仍然可能会有所帮助)

有一个用于活动记录的$this->db->select_sum();函数，并且(这是我不太确定的一点)你可以使用方法链。因此，这里有一些可以尝试的东西。

阶段1是将常见的东西剥离到某个位置。如果对WHERE进行了索引，则会加快速度。这将为您提供(也修复了括号-您的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

此外，如果你可以通过说接受，做出是，不，也许都是排他性的来简化，那么你也可以去掉“决定的时间”来进一步简化。(理想情况下，您只需计算"COUNT“，然后减去其余部分，但不清楚如何在活动记录上的一个查询中执行此操作)。

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

Answer 2

      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"