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

Answer 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();