phpmysqlcodeigniteractiverecordquery-builder

JOIN parent table to a many-to-many table using CodeIgniter query building methods

Can someone help me convert this to an active record script?

I'm trying to get the email address and contact name from another table. map_userfields table is a many-to-many table. Multiple rows relate to a single p.id. Isolating specific fieldvalue data is done by searching for p.id in conjunction with uf.fieldid.

See this screenshot for a reference to the map_userfields table: enter image description here

My current raw SQL:

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

Solution

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