How to JOIN same table multiple times

[Mário Pálka]

Hello,

I have a problem, how to JOIN same table more than once. I have this query:

$counter = $this

->notOrm

->items

->where('type = ?', 'campaign')

->and(item_values:relation = ?', 'feed')

->and(item_values:value > ?', '')

->and(item_values:relation = ?', 'status')

->and(item_values:value = ?', 'A');

It generates query like this:

SELECT

  items.*

FROM

  items

LEFT JOIN

  items_values ON items.id = items_values.items_id

WHERE

  (`type` = 'campaign') 

  AND(items_values.relation = 'feed') 

  AND (items_values.value > '')

  AND (items_values.relation = 'status') 

  AND (items_values.value = 'A')

but I would like to achieve this query:

SELECT

  items.*

FROM

  items

LEFT JOIN

  items_values as values1 ON items.id = values1.items_id

LEFT JOIN

  items_values as values2 ON items.id = values2.items_id

WHERE

  (`type` = 'campaign') 

  AND(values1.relation = 'feed') 

  AND (values1.value > '')

  AND (values2.relation = 'status') 

  AND (values2.value = 'A')

Is it possible or should I use PDO rather?

Thx

[Allysson David]

'Directly' it's not possible, but it's possible under certain conditions.

In my following example, you can't ever have a table ending with numbers, because that's how I'll setup NotORM to join same table multiple times.

"tableX" where X is a number will always point to "table", okay? (If you can't guarantee that, you might setup another pattern).

So here's how it goes:

include "NotORM.php";

class MyStructure extends NotORM_Structure_Convention {

function getReferencedTable($name, $table) {

if (preg_match('/([^0-9]+)[0-9]+$/', $name, $matches) == 1) { // If tablename ends with digits, strip the digits and use the rest as tablename

$name = $matches[1];

}

return parent::getReferencedTable($name, $table);

}

}

$structure = new MyStructure;

$pdo = new PDO(...);

$db = new NotORM($pdo, $structure);

echo $db->items()

->where('type = ?', 'campaign')

->and('item_values1:relation = ?', 'feed')

->and('item_values1:value > ?', '')

->and('item_values2:relation = ?', 'status')

->and('item_values2:value = ?', 'A');

Results in:

SELECT items.* 

FROM items 

LEFT JOIN item_values AS item_values1 ON items.id = item_values1.items_id 

LEFT JOIN item_values AS item_values2 ON items.id = item_values2.items_id 

WHERE (type = 'campaign') 

AND (item_values1.relation = 'feed') 

AND (item_values1.value > '') 

AND (item_values2.relation = 'status') 

AND (item_values2.value = 'A')

Good Luck!

[Mário Pálka]

Hello Allyson,

you are my guru! :D Your solution is really nice, thank you!

 Mariyo