CodeIgniter SELECT query on table with JOIN and WHERE IN a subquery

Question

I have this raw MySQL query; how can I make this an active record query?

SELECT 
    chp_id, 
    chp_destination, 
    cde_name, 
    chp_year, 
    chp_from, 
    chp_to, 
    chp_budget_price_high, 
    chp_medium_price_high, 
    chp_luxury_price_high,
    chp_budget_price_low, 
    chp_medium_price_low, 
    chp_luxury_price_low, 
    chp_timestamp,
    chp_comment
FROM crm_hotel_price
LEFT JOIN crm_destinations ON cde_id = chp_destination
WHERE chp_id IN 
    (
        SELECT MAX( chp_id )
        FROM crm_hotel_price
        GROUP BY chp_destination, chp_year
    )
ORDER BY 
    chp_id

that is what I tried, and found out that mysql subquery is not yet supported by CodeIgniter's active record..Is there a way other way on how to make this with CI's active record? — mark yorky
– mark yorky, Commented Aug 15, 2013 at 10:24
Have a look at this one -> ellislab.com/codeigniter/user-guide/database/results.html . You can do something like $query = $this->db->query("YOUR QUERY"); and then to get the result $query->result(). — Boris
– Boris, Commented Aug 15, 2013 at 10:26

Pattle · Accepted Answer · 2013-08-15 10:33:39Z

1

Yes subquery is not yet supported by active record and you can't extend the class easily. So you want to do something like this (not tested)

$this->db->select('chp_id')
    ->select('chp_destination')
    ->select('cde_name')
    ->select('chp_year')
    ->select('chp_from')
    ->select('chp_to')
    ->select('chp_budget_price_high')
    ->select('chp_medium_price_high')
    ->select('chp_luxury_price_high')
    ->select('chp_budget_price_low')
    ->select('chp_medium_price_low')
    ->select('chp_luxury_price_low')
    ->select('chp_timestamp')
    ->select('chp_comment')
->from('crm_hotel_price')
->join('crm_destinations', 'cde_id = chp_destination', 'left')
->where('chp_id IN (SELECT MAX( chp_id ) FROM crm_hotel_price GROUP BY chp_destination, chp_year)')
->order_by('chp_id');
$query = $this->db->get();

answered Aug 15, 2013 at 10:33

Pattle

6,0358 gold badges37 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mark yorky Over a year ago

I think your code ->order_by('ORDER BY chp_id'); should be like this ->order_by('chp_id');

mickmackusa · Accepted Answer · 2024-09-16 22:00:59Z

Modern CodeIgniter does allow for subqueries to be compiled separately/safely and saved as a string to be inserted into a parent query. This subquery can be passed into where_in() so long as the the escaping parameter is turned off.

$destYearMaxIds = $this->db
    ->select_max('chp_id')
    ->group_by(['chp_destination', 'chp_year'])
    ->get_compiled_select('crm_hotel_price');

return $this->db
    ->select([
        'chp_id',
        'chp_destination',
        'cde_name',
        'chp_year',
        'chp_from',
        'chp_to',
        'chp_budget_price_high',
        'chp_medium_price_high',
        'chp_luxury_price_high',
        'chp_budget_price_low',
        'chp_medium_price_low',
        'chp_luxury_price_low',
        'chp_timestamp',
        'chp_comment'
    ])
    ->join('crm_destinations', 'crm_destinations.cde_id = crm_hotel_price.chp_destination', 'LEFT')
    ->where_in('chp_id', $destYearMaxIds, false)
    ->order_by('chp_id')
    ->get('crm_hotel_price')
    ->result();

Rendered SQL:

SELECT `chp_id`, `chp_destination`, `cde_name`, `chp_year`, `chp_from`, `chp_to`, `chp_budget_price_high`, `chp_medium_price_high`, `chp_luxury_price_high`, `chp_budget_price_low`, `chp_medium_price_low`, `chp_luxury_price_low`, `chp_timestamp`, `chp_comment`
FROM `crm_hotel_price`
LEFT JOIN `crm_destinations` ON `crm_destinations`.`cde_id` = `crm_hotel_price`.`chp_destination`
WHERE chp_id IN(
    SELECT MAX(`chp_id`) AS `chp_id`
    FROM `crm_hotel_price`
    GROUP BY `chp_destination`, `chp_year`
)
ORDER BY `chp_id`

A word of caution though, a subquery inside of an IN condition should probably be replaced with a JOIN on a subquery for performance reasons.

SELECT 
    p.chp_id, 
    p.chp_destination, 
    d.cde_name, 
    p.chp_year, 
    p.chp_from, 
    p.chp_to, 
    p.chp_budget_price_high, 
    p.chp_medium_price_high, 
    p.chp_luxury_price_high,
    p.chp_budget_price_low, 
    p.chp_medium_price_low, 
    p.chp_luxury_price_low, 
    p.chp_timestamp,
    p.chp_comment
FROM crm_hotel_price p
LEFT JOIN crm_destinations d ON d.cde_id = p.chp_destination
JOIN (
    SELECT 
        MAX(chp_id) max_chp_id, 
        chp_destination, 
        chp_year
    FROM crm_hotel_price
    GROUP BY chp_destination, chp_year
) max_p ON p.chp_id = max_p.max_chp_id
ORDER BY p.chp_id;

This can be achieved with:

$destYearMaxId = $this->db
    ->select([
        'MAX(chp_id) max_chp_id',
        'chp_destination',
        'chp_year'
    ])
    ->group_by(['chp_destination', 'chp_year'])
    ->get_compiled_select('crm_hotel_price');

return $this->db
    ->select([
        'p.chp_id',
        'p.chp_destination',
        'd.cde_name',
        'p.chp_year',
        'p.chp_from',
        'p.chp_to',
        'p.chp_budget_price_high',
        'p.chp_medium_price_high',
        'p.chp_luxury_price_high',
        'p.chp_budget_price_low',
        'p.chp_medium_price_low',
        'p.chp_luxury_price_low',
        'p.chp_timestamp',
        'p.chp_comment'
    ])
    ->join('crm_destinations d', 'd.cde_id = p.chp_destination', 'LEFT')
    ->join("($destYearMaxId) max_p", 'p.chp_id = max_p.max_chp_id')
    ->order_by('p.chp_id')
    ->get('crm_hotel_price p')
    ->result();

Collectives™ on Stack Overflow

CodeIgniter SELECT query on table with JOIN and WHERE IN a subquery

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related