How can I rewrite this SQL into CodeIgniter's

问题:

SELECT *, SUM(tbl.relevance) AS relevance FROM
(
    (
        SELECT q_id,
        MATCH(a_content) AGAINST ('бутон') AS relevance
        FROM answers

        WHERE
        MATCH(a_content) AGAINST ('бутон')
    )
    UNION
    (
        SELECT q_id,
        (MATCH(q_content) AGAINST ('бутон')) * 1.5 AS relevance
        FROM questions

        WHERE
        MATCH(q_content) AGAINST ('бутон')
    )
) AS tbl

JOIN questions ON questions.q_id = tbl.q_id

GROUP BY tbl.q_id
ORDER BY relevance DESC

回答1:

Codeigniter currently does not have support for subqueries in its Active Records class.

You'll simply have to use this:

$this->db->query($your_query, FALSE);

---

Remember to pass that second argument, so that Codeigniter doesn't try to escape your query.

回答2:

Well there is indeed a simple hack for subqueries in codeigniter. You have to do the following Go to system/database/DB_active_rec.php

There if you are using version 2.0 or greater change this

public function _compile_select($select_override = FALSE)
public function _reset_select()

Remove public keyword and you will be able to use subqueries And now

$data   =   array(
             "q_id",
             "MATCH(a_content) AGAINST ('?????') AS relevance"  
         );

$this->db->select($data);
$this->db->from("answers");
$this->db->where("MATCH(a_content) AGAINST ('?????')");
$subQuery1 = $this->db->_compile_select();
// Reset active record
$this->db->_reset_select();   

unset($data);
$data   =   array(
            "q_id",
            "(MATCH(q_content) AGAINST ('?????')) * 1.5 AS relevance"   
        );

$this->db->select($data);
$this->db->from("questions");
$this->db->where("MATCH(q_content) AGAINST ('?????')");
$subQuery2 = $this->db->_compile_select();
// Reset active record
$this->db->_reset_select(); 

unset($data);
$data   =   array(
          "q_id",
          "SUM(tbl.relevance) AS relevance" 
         );

$this->db->select($data);
$this->db->from("$subQuery1 UNION $subQuery2");
$this->db->join("questions","questions.q_id = tbl.q_id");
$this->db->group_by("tbl.q_id");
$this->db->order_by("relevance","desc");
$query = $this->db->get();

回答3:

Did you try this query without sub-queries? I think you can do it without usage of sub-queries with left joins and use of IS NOT NULL.