One table multiple join fields

[Gosmaster]

I'm starting to think that NotORM is pretty awesome, it fast not to hard to learn and pretty intelligent. 

But there is a question I cannot seem to find the answer to, or maybe there is just something I do not understand.

The scenario There is a `base_table` and this has two or more fields that are a reference to other tables, and these continue the relations and spit into others. The question is how to do this because it seems that because the join spits it cannot be auto joined and can not

..be chained like this,

$oNotOrm->base_table[$iPageId]
->table_x
->table_z
->table_y;

...be selected like this,

$oNotOrm->base_table[$iPageId]
->select("table_x.*,table_z.*,table_y.*");

...or be split like this:

$oResBaseTable  = $oNotOrm->base_table[$iPageId];
$oResTableXandZ = $oResBaseTable->table_x->table_z;
$oResTableY     = $oResBaseTable->table_y;

DB Structure

`base_table`

• `id`
• `stuff`
• `table_x_id` (reference)
• `table_y_id` (reference)

`table_x`.

• `id`
• `stuff`
• `table_z_id` (reference)

`table_z`

• `id`
• `stuff`

`table_y`

• `id`
• `stuff`

[Allysson David]

Hello, I'll be replying in parts, so expect multiple messages.

Chaining is possible, but the way you put it is wrong because of your database structure.

You can access data (`stuff`) like this:

$oNotOrm->base_table[$iPageId]['stuff'];
$oNotOrm->base_table[$iPageId]->table_x['stuff'];
$oNotOrm->base_table[$iPageId]->table_x->table_z['stuff'];
$oNotOrm->base_table[$iPageId]->table_y['stuff'];

It's not possible to have this:

$oNotOrm->base_table[$iPageId]->table_x->table_z->table_y['stuff'];

Because `table_z` doesn't know `table_y`.

[Allysson David]

To select from referenced tables, the correct way to do is this:

$oNotOrm->base_table()->select('table_x.*, table_x.table_z.*, table_y.*')->where('base_table.id', $iPageId);

Explanation:

Select is to be called on a Result (think of as a collection of Rows).

When calling:

$oNotOrm->base_table[$iPageId];

The output will be a single Row. The Row class doesn't have the Select function, instead, if you use it, the class will interpret as if it was a reference to a table called `select`.

I suppose that's because you wouldn't perform a Select in a single Row...

The being so, we change to:

$oNotOrm->base_table()->where('id', $iPageId);

That will return a Result object accordingly to the conditions. It's just that it will contain a single Row, but it's still a Result object and can have a Select performed on it.

Now we try to specify the desired info:

$oNotOrm->base_table()->select('table_x.*, table_z.*, table_y.*')->where('id', $iPageId);

The problem is that now we have the column `id` ambiguously on all the tables, so we have to specify which is the one in the condition:

$oNotOrm->base_table()->select('table_x.*, table_z.*, table_y.*')->where('base_table.id', $iPageId);

The problem now is that NotORM will expect `base_table` to have a direct reference to `table_z` while doing the joins. That isn't the case, so we specify who has the reference:

$oNotOrm->base_table()->select('table_x.*, table_x.table_z.*, table_y.*')->where('base_table.id', $iPageId);

And this will finally give us the expected result.

[Allysson David]

Yes, it can be split.

But the variable naming is wrong.

$oResBaseTable  = $oNotOrm->base_table[$iPageId];

$oResTableX     = $oResBaseTable->table_x;
$oResTableZ     = $oResBaseTable->table_x->table_z;
$oResTableY     = $oResBaseTable->table_y;

When chaining you aren't just including the info to the info on the previous link, you're changing where the variable is pointing to.

That being the case, the $oResTableXandZ can't exist unless you clearly use a Select statement to create it.

Also, the Select can't be directly used on $oResBaseTable because it will treat it as another table reference, as I described before.

[Gosmaster]

Thank you for this great great explanation, your time and examples ! 

Op donderdag 6 november 2014 14:21:53 UTC+1 schreef Allysson David: