CodeIgniter Active Record Queries W/ Sub Queries

1
votes

Question:

I really am trying to stick to using ActiveRecord and not using straight SQL.. can someone help me convert this to activerecord?

Trying to get the email address and contact name from another table. map_userfields table is a one to many, multiple rows per p.id. one row per p.id per uf.fieldid.

see this screenshot for a reference to the map_userfields table:

enter image description here

Current Non active record query

SELECT
p.id,
(SELECT uf.fieldvalue FROM map_userfields uf WHERE uf.pointid = p.id AND uf.fieldid = 20) As ContactName,
(SELECT uf.fieldvalue FROM map_userfields uf WHERE uf.pointid = p.id AND uf.fieldid = 31) As ContactEmail
FROM
map_points p
WHERE
/** $pointCategory is an array of categories to look for **/
p.type IN($pointCategory)

Note: I am using CodeIgniter 2.1.x, MySQL 5.x, php 5.3

3
mysqlcodeigniteractiverecord

3 Answers

2
votes

CodeIgniter's Active Record doesn't support subqueries out-of-the-box. You'll need to download the CodeIgniter Subqueries class.

In the CodeIgniter wiki, there's an article on how to subqueries.

1
votes

You can select anything you need with CI's activeRecords as long as you tell CI not to parse your string

$this->db->select('p.id,
(SELECT uf.fieldvalue FROM map_userfields uf WHERE uf.pointid = p.id AND uf.fieldid = 20) As ContactName,
(SELECT uf.fieldvalue FROM map_userfields uf WHERE uf.pointid = p.id AND uf.fieldid = 31) As ContactEmail
',TRUE);
$this->db->from('map_points p');
$this->db->where_in('p.type',$pointCategory);
$q = $this->db->get();
0
votes

Sub queries indeed work in codeigniter but there is a little hack. I found that in in system/database/DB_active_rec.php there are two functions named _compile_select and _reset_select. If you remove the protected word from both you will be able to use these function for subqueries Here is a usage tutorial

http://heybigname.com/2009/09/18/using-code-igniters-active-record-class-to-create-subqueries/