phpsqlcodeigniteractiverecordquery-builder

Convert SQL including UNION, subqueries, JOIN, GROUP BY, and MATCH() AGAINST() into CodeIgniter's Active Record

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
Solution

  • 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.