problem with joins

[unitedworx]

There is little documentation for joins so i am not sure i got it right here but if i return results from 2-3 tables that have the same table names then idiorm does not play well since it shows everything in a flat structure,i would expect a nested array with the data of joined tables! Is there a way to handle this better? i know you can issue selects for each joined table with aliases for each column but this becomes very very ugly if you have many columns in the joined tables.

Does paris handle this better? if you create a model with one to one relashinship does it fetch the data in one go with joins or does it issue different selects when you ask for that data?

e.g this

$contentList = ORM::for_table('content')
                ->select('content.*')
                ->select('category1.*')
                ->select('category2.*')
                ->where('content.parentid', '0')
                ->join('category', array('content.category1id', '=', 'category1.categoryid'), 'category1')
                ->join('category', array('content.category2id', '=', 'category2.categoryid'), 'category2')
                ->find_many();

will result in this data

contentid: 1
title: Company Name
text: The quick brown fox jumps over the lazy dog. 
slug: home
categoryid: 36
name: MainMenu

i would expect to get the joined tables data in a nested array

contentid: 1
title: Company Name
text: The quick brown fox jumps over the lazy dog. 
slug: home
category1:  categoryid: 36
         name: Main Menu
category2:  categoryid: 36
          name: some Category 

[treffynnon]

Idiorm is a lightweight wrapper around PDO so just like PDO and by extension SQL you need to alias columns that appear in more than one table when they're joined together.

Paris issues multiple queries manually - no joins or relationship queries are automatically performed by Paris.

[henryruhs]

It took me hours to find out what I did wrong - you should add this to the documentation that an "alias" is needed to return the "merged" results...

I think the code should look like this, why didn't you anwser by adding some code?

$contentList = ORM::for_table('content')
                ->select('content.*', 'content')
                ->select('category1.*', 'category_one')
                ->select('category2.*', 'category_two')
                ->where('content.parentid', '0')
                ->join('category', array('content.category1id', '=', 'category1.categoryid'), 'category1')
                ->join('category', array('content.category2id', '=', 'category2.categoryid'), 'category2')
                ->find_many();

[treffynnon]

It's how SQL works and not a property of Idiorm so it shouldn't actually feature in the documentation for this project.

Besides it really is common sense that you cannot have two things named the same in the same scope - try doing the same with a PHP variable and it will wipe out the initial value with the last assignment.

$a = 1; $a = 5;

So what's $a contain now then? 5.

In a more tangible example; imagine trying to call a dog and just that one dog in a room of dogs called Rex...

[henryruhs]

Well... the native SQL statements generated by Idiorm on PHPMyAdmin always returned me the merged result set and not just one. I thought the columns are in the scope of their tables, maby this is how SQL works but still this is a trap for people with less experience.

It would be great if you consider to add more examples on a Github wiki.