CODEWITHSUNDEEP
phpsqlcodeigniteractiverecordquery-builder
SwiftD
SwiftD

Reputation: 6079

Convert SQL containing a subquery and multiple OR WHERE conditions to CodeIgniter's active record syntax

I am struggling to create an active record statement to do what I want, here is my current attempt:

$this->db
    ->select('*')
    ->from('item_entries')
    ->where('is_pick', 'y')
    ->join('item_categories_rel', 'item_categories_rel.item_id = item_entries.item_id')
    ->join('item_categories', 'item_categories.cat_id = item_categories_rel.cat_id')
    ->join('ratings_total', 'ratings_total.item_id = item_entries.item_id')
    ->where('item_categories.cat_id', $cat_id)
    ->or_where('parent_id', $cat_id)
    ->or_where('gparent_id', $cat_id)
    ->order_by("item_name", "desc");  
$query2 = $this->db->get();
if (is_object($query)) {
    return $query2->result_array();
} else { 
    return $query2;
}

This translates to the following sql:

SELECT *
FROM (`item_entries`)
JOIN `item_categories_rel` ON `item_categories_rel`.`item_id` = `item_entries`.`item_id`
JOIN `item_categories` ON `item_categories`.`cat_id` = `item_categories_rel`.`cat_id`
JOIN `ratings_total` ON `ratings_total`.`item_id` = `item_entries`.`item_id`
WHERE `is_pick` =  'y'
AND `item_categories`.`cat_id` =  '3'
OR `parent_id` =  '3'
OR `gparent_id` =  '3'

Now, what I want (although I am aware I cant do temp tables in active record) is the equivalent of this:

SELECT *
FROM (
    SELECT *
    FROM (`item_entries`)
    JOIN `item_categories_rel` ON `item_categories_rel`.`item_id` = `item_entries`.`item_id`
    JOIN `item_categories` ON `item_categories`.`cat_id` = `item_categories_rel`.`cat_id`
    JOIN `ratings_total` ON `ratings_total`.`item_id` = `item_entries`.`item_id`
    WHERE `is_pick` =  'y'
    AS T
)
WHERE `item_categories`.`cat_id` =  '3'
OR `parent_id` =  '3'
OR `gparent_id` =  '3'

How can retrieve this result in active record?

Please note - I have not checked the above SQL so it may be slightly wrong but hopefully you get what I am trying to do.

Upvotes: 1

Views: 76

Answers (2)

mickmackusa
mickmackusa

Reputation: 47992

I don't see any reason for a subquery for your provided SQL, but he is how you can implement that:

Notice the use of USING and the inverted WHERE IN parameters to avoid several OR WHERE statements.

Code:

$subquery = $this->db
    ->join('item_categories_rel', 'item_id')
    ->join('item_categories', 'cat_id')
    ->join('ratings_total', 'item_id')
    ->where('is_pick', 'y')
    ->get_compiled_select('item_entries');

return $this->db
    ->where_in(3, ['item_categories.cat_id', 'parent_id', 'gparent_id'], false)
    ->get("($subquery) sub")
    ->result();

Rendered SQL (with some manually added spaces for readability):

SELECT *
FROM (
    SELECT *
    FROM `item_entries`
    JOIN `item_categories_rel` USING (`item_id`)
    JOIN `item_categories` USING (`cat_id`)
    JOIN `ratings_total` USING (`item_id`)
    WHERE `is_pick` = 'y'
) sub
WHERE 3 IN (item_categories.cat_id, parent_id, gparent_id)

If you want to explicitly enforce quoting on the WHERE IN statement, use:

->where_in(
    $this->db->escape($number),
    $this->db->escape_identifiers(['item_categories.cat_id', 'parent_id', 'gparent_id']),
    false
)

Upvotes: 0

Fad
Fad

Reputation: 9858

You can't do that with CodeIgniter Active Record. It's not mean to do that. You can use this library instead as a workaround.

Upvotes: 1

Related Questions