CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecordquery-builder
NDBoost
NDBoost

Reputation: 10634

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

Upvotes: 1

Views: 2428

Answers (4)

mickmackusa
mickmackusa

Reputation: 47883

I will strongly urge you to not execute subqueries in the SELECT clause because this tends to be a very poor performer.

Instead, I'll demonstrate how to use a pivot query on your map_userfields table to set up a very convenient JOIN query. For your project, it will be very valuable to embrace the pivot technique when accessing map_userfields data.

$pointCategory = ['type1', 'type2'];  // some made-up data

$pivot = $this->db->select([
    'uf.pointid',
    'MAX(CASE WHEN uf.fieldid = 20 THEN uf.fieldvalue END) contact_name',
    'MAX(CASE WHEN uf.fieldid = 31 THEN uf.fieldvalue END) contact_email'
])
->group_by('uf.pointid')
->get_compiled_select('map_userfields uf');

return $this->db->select('p.id, uf_pivot.contact_name, uf_pivot.contact_email')
    ->join("($pivot) uf_pivot", 'p.id = uf_pivot.pointid', 'LEFT')
    ->where_in('p.type', $pointCategory)
    ->get('map_points p')
    ->result();

The rendered SQL (with improved spacing for readability)

SELECT `p`.`id`,
       `uf_pivot`.`contact_name`,
       `uf_pivot`.`contact_email`
FROM `map_points` `p`
LEFT JOIN (
    SELECT `uf`.`pointid`,
           MAX(CASE WHEN uf.fieldid = 20 THEN uf.fieldvalue END) contact_name,
           MAX(CASE WHEN uf.fieldid = 31 THEN uf.fieldvalue END) contact_email
    FROM `map_userfields` `uf`
    GROUP BY `uf`.`pointid`
) uf_pivot ON `p`.`id` = `uf_pivot`.`pointid`
WHERE `p`.`type` IN('type1', 'type2')

  • You'll see that the MAX() expressions in the pivot subquery isn't fully quoted, but this is a harmless outcome for this query. If you wish to manually escape/quote the entities/values, CI has methods for this, but you'll need to muddy-up these expressions with verbose concatenation or interpolation.

  • result() will return an array of zero or more objects.

  • A LEFT JOIN is used to enable the possibility of returning rows where p.id is not actually found in the pivot subquery.

  • If the pivot subquery doesn't find a fieldvalue with fieldid 20 or 31, you'll get null values for contact_name and contact_email in the result set -- just something to be aware of.

  • More refinement could be added to the pivot subquery involving WHERE and HAVING conditions, but I decided to keep the script simple.

Upvotes: 1

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

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/

Upvotes: 0

kba
kba

Reputation: 19466

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.

Upvotes: 2

Dalen
Dalen

Reputation: 9006

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

Upvotes: 1

Related Questions