Convert SQL with subquery as part of JOIN's ON condition to CodeIgniter active record syntax

Question

How do I write the following query as a CodeIgniter active record script?

select
    tbl_list.id,
    tbl_list_items.name
from
    tbl_list
join
    tbl_list_items
    on tbl_list_items.id=(
        select
            tbl_list_items.id
        from
            tbl_list_items
        where
            list_id=tbl_list.id
        order by
            tbl_list_items.item_no asc
        LIMIT 1
    )

I came up with this, but it does not do the job.

$this->db->select($this->table_list.'.*');
$this->db->from($this->table_list);

$sub = $this->subquery->start_subquery('join','',$this->table_list_items.'.id');
$sub->select('id')->from($this->table_list_items)->where('list_id','tbl_list.id')->order_by($this->table_list_items.'item_no','asc')->limit('1');
$this->subquery->end_subquery('list_items');

Finally changed the statement to select tbl_list.id, tbl_list_items.name from tbl_list join tbl_list_items on tbl_list_items.id=(select tbl_list_items.id from tbl_list_items where listing_id=tbl_list.id order by item_no asc LIMIT 1); Active record seems more happy with this. — BARON'S
– BARON'S, Commented Dec 31, 2011 at 16:28
You can always use Active Record with a straight SQL statement. But then you're not leveraging the breaking down of the statement into interchangeable components. — Dominic Tancredi
– Dominic Tancredi, Commented Jan 1, 2012 at 14:01
Ask 1 specific researched non-duplicate question. Either ask re 1 bad query/function with obligatory minimal reproducible example, including why you think it should return something else or are unsure at the 1st subexpression where you don't get what you expect or are stuck, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do with justification & a minimal reproducible example--then misunderstood code doesn't belong. But please ask about unexpected behaviour 1st because misconceptions get in the way of your goal. How to Ask Help center Basic questions are faqs. — philipxy
– philipxy, Commented Apr 21 at 8:04

philipxy · Accepted Answer · 2025-04-21 08:01:31Z

1

Review the Active Record methods available to you in CodeIgniter.

Then review a CI library to extend it to handle subqueries available on CodeIgniter.

A walk-through about how CI is doing this under-the-hood.

A GitHub codebase for implementing subqueries in CodeIgniter 1.7 and 2.x.

The code would be similar to (untested):

$this->db->select('tbl_list.id, tbl_list.names');
$this->db->from('tbl_list');
$sub = $this->subquery->start_subquery('join', 'left', 'tli.id = tbl_list.id');
$sub->select('tbl_list_items.id')->from('tbl_list_items')->where('tbl_list_items.list_id=tbl_list.id ');
$sub->db->order_by('ASC');
$sub->db->limit(1);
$this->subquery->end_subquery('tli');

The key is $this->subquery->end_subquery('tli');. It names the result of the subquery via AS tli so that you can reference it in tli.id.

edited Apr 21 at 8:01

philipxy

15.2k6 gold badges44 silver badges101 bronze badges

answered Dec 31, 2011 at 15:36

Dominic Tancredi

42.4k7 gold badges37 silver badges50 bronze badges

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

Comments

mickmackusa · Accepted Answer · 2025-11-18 08:04:00Z

I use PSQL professionally, but if you are using at least MySQL8, or a dialect that supports LATERAL JOINs, this will allow a more direct query. LATERAL JOIN allows you to reference column data from the parent table within the subquery. This way you can isolate the name of the list item with the lowest item number. SQLize Demo

SELECT list.id, lowest_list_item.name
FROM tbl_list list
JOIN LATERAL (
  SELECT name
  FROM tbl_list_items
  WHERE list_id = list.id
  ORDER BY item_no
  LIMIT 1
) lowest_list_item;

CodeIgniter:

$sub = $this->db
    ->select('name')
    ->where('list_id = list.id', null, false)
    ->order_by('item_no')
    ->limit(1)
    ->get_compiled_select('tbl_list_items');

return $this->db
    ->select(['list.id', 'lowest_list_item.name'])
    ->from('tbl_list list')
    ->join("LATERAL ($sub) lowest_list_item", '', '', false)
    ->get()
    ->result();

Collectives™ on Stack Overflow

Convert SQL with subquery as part of JOIN's ON condition to CodeIgniter active record syntax

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related