codeigniter - 如何在代码点火器中编写连接选择语句

Question

SELECT
    q1.user_id,q2.count,q2.total,q1.choice
FROM    
    (
    SELECT
        "table1"."user_id" as user_id,"table2"."choice" as choice
    FROM
        "table1", "table2"
    WHERE
        "table1"."user_id" = table2.ref_id                
    AND
        "table1"."active" = 1
    )q1
LEFT OUTER JOIN        
    (
    SELECT
        count(table1.*) as count, SUM(table2.add1) as total,table1.user_id as user_id
    FROM
        "table1", "table2"
    WHERE                   
        "table1"."type" = 1               
    AND
        table1"."some_id" IN(SELECT user_id FROM "table2", "table3" WHERE "table3"."user_id" = table3.refid)                
    group by
        "table2"."user_id"
    )q2        
ON
    q2.user_id=q1.user_id
ORDER BY
    q2.count ASC

我有一个这样的查询。它在 db 中工作正常。但不知道如何使用子查询在 codeigniter 中编写它。或者有没有其他方法可以获得相同的结果？

Answer 1

你可以为此使用$this->db->query()..

doc here

$sql='SELECT
    q1.user_id,q2.count,q2.total,q1.choice
FROM    
    (
    SELECT
        "table1"."user_id" as user_id,"table2"."choice" as choice
    FROM
        "table1", "table2"
    WHERE
        "table1"."user_id" = table2.ref_id                
    AND
        "table1"."active" = 1
    )q1
LEFT OUTER JOIN        
    (
    SELECT
        count(table1.*) as count, SUM(table2.add1) as total,table1.user_id as user_id
    FROM
        "table1", "table2"
    WHERE                   
        "table1"."type" = 1               
    AND
        table1"."some_id" IN(SELECT user_id FROM "table2", "table3" WHERE "table3"."user_id" = table3.refid)                
    group by
        "table2"."user_id"
    )q2        
ON
    q2.user_id=q1.user_id
ORDER BY
    q2.count ASC';

$result=$this->db->query($sql);