How to use sub query in CI Active record classes?

[eluser]Rey Philip Regis[/eluser]
Hi guys,

Does anybody know how to use subquery in CI Active Record class? The query is like this

Code:

SELECT
cse.TRIAGECASEID,
(SELECT cmp.DESCRIPTION FROM `complaint` cmp WHERE cmp.TRIAGECASEID = cse.TRIAGECASEID AND cmp.ISCHIEFCOMPLAINT = '1') as `CHIEF COMPLAINT`,
cal.DATEANDTIME,
cse.SCORE,
tcasestatus.CASESTATUSTYPECODE
FROM
`case` cse
INNER JOIN
triagecasecall` tcasecal ON(cse.TRIAGECASEID = tcasecal.TRIAGECASEID)
INNER JOIN
`call` cal ON(tcasecal.CALLLID = cal.CALLLID)
INNER JOIN
`triagecasestatus` tcasestatus ON(cse.TRIAGECASEID = tcasestatus.TRIAGECASEID)
WHERE
cse.PATIENTID = '114'

Thanks in advance.

[eluser]alaa007[/eluser]
for me I will do it this way

Code:

$this->db->query("SELECT
cse.TRIAGECASEID,
(SELECT cmp.DESCRIPTION FROM `complaint` cmp WHERE cmp.TRIAGECASEID = cse.TRIAGECASEID AND cmp.ISCHIEFCOMPLAINT = '1') as `CHIEF COMPLAINT`,
cal.DATEANDTIME,
cse.SCORE,
tcasestatus.CASESTATUSTYPECODE
FROM
`case` cse
INNER JOIN
triagecasecall` tcasecal ON(cse.TRIAGECASEID = tcasecal.TRIAGECASEID)
INNER JOIN
`call` cal ON(tcasecal.CALLLID = cal.CALLLID)
INNER JOIN
`triagecasestatus` tcasestatus ON(cse.TRIAGECASEID = tcasestatus.TRIAGECASEID)
WHERE
cse.PATIENTID = '114'");

[eluser]Rey Philip Regis[/eluser]
Ok..But That is what I was avoiding to use, custom query. If possible I want to use Active Record methods. Cause using active records has advantages than manual query.

But anyway thanks.

[eluser]Phil Sturgeon[/eluser]
Sadly the active record is not that flexible just yet.

The select part can be managed with the following:

Code:

$this->select('cse.TRIAGECASEID');
$this->select("(SELECT cmp.DESCRIPTION FROM `complaint` cmp WHERE cmp.TRIAGECASEID = cse.TRIAGECASEID AND cmp.ISCHIEFCOMPLAINT = '1') as `CHIEF COMPLAINT`", FALSE);
$this->select('cal.DATEANDTIME, cse.SCORE, tcasestatus.CASESTATUSTYPECODE');

Sadly I can't think of a way to jam the case joins in...

I'd strongly recommend using plain SQL for advance queries such as this. AR is only useful for simple queries at this point.

[eluser]m4rw3r[/eluser]
I've made IgnitedQuery, which is a stand alone SQL-building lib (backwards compatible with CI's AR).
It is the base of IgnitedRecord, but works as a normal lib too.

It is capable to handle subqueries, and it is described here: http://www.assembla.com/wiki/show/Ignite...ted_wheres