Retrieve data from N:M relational table

albertosanchezm

Hello,

I've got to entities (e1,e2) and one relational table for joining them due they have a N:M relation.

I've retreiving the data from the second entity in to steps:

`$rel = $e1->related(‘relational_table’);
$e2 = $rel->related(‘entity2’);`

Is this the way to do it or is there any other way?

Thanks a lot.

Last edited by albertosanchezm (2014-03-05 15:22)

Oli

If you need data from both tables you can select data from relational_table and you can reache to e1 and e2.

$rt = $this->connection->table('relational_table');
foreach($rt as $value)
{
	$value->entity1->title;
	$value->entity2->title;
}

It suppose that you have in relational_table for example: entity1_id, entity2_id

albertosanchezm

Sorry, the point is that I want to retreive all values from entity2 having a entity1 key.

Following your example, it will be as follow:

$rt = $this->database->table(‘relational_table’)
->where(‘entity1_id’,$entity_id)->fetch();

But, now, with a list of entity2_id in the $rt variable, how can I retrieve the entity2 objects and pass them to the template?

Thanks a lot.

greeny

<?php
$ids = array_values($this->database->table('foo')->fetchPairs('id', 'id'));
$resultSet = $this->database->table('bar')->where('id', $ids);
?>

Oli

You can use:

$entity1 = $this->connection->table('entity2')->where(':relational_table.entity1_id', $entity1Id);
foreach($entity1 as $value)
{
	$value->title;
}

Or something similar. Check documentation for more information

note: fetch return just 1 (first) row. If you have more rows with the same entity1_id you have to go through cycle.

albertosanchezm

Thanks a lot. Greeny method works perfectly. The second one I could not tested.

Here is my final code if helps anyone:

$relation = array_values($this->database->table('relational_table')
                    ->where('entity1_id',$value)->fetchPairs('entity1_id', 'entity2_id'));
$this->template->result = $this->database->table('entity2')->where('entity2_id', $relation);

Last edited by albertosanchezm (2014-03-08 10:52)

Oli

I don't know, how exactly work my solution. But i would say, that is better, than greenys. His solution make 2 queries and one of them uses foreach to every item.

Maybe my solution works exactly same, but it isnt worse, i supous that is better.