Nesting queries within active record

[eluser]E.T.Cook[/eluser]
Is there a method to create a nested results object with Active Record?

For example

Let's say I have two tables: one is cars, one is manufacturers

I would like the hierarchy to be as such:

[Ford]
[Taurus]
[F-150]
[Focus]
[Audi]
[A4]
[A6]
[A8]

etc...

Is there any way to do it without looping each query separately, and manually propagating an array? On the rails version of active record, I believe you use :through, but I can't seem to find anything like that here.

Thanks in advance for any help you can provide.

[eluser]Jamie Rumbelow[/eluser]
The only thing I can think of is to create a model function. You will still have to run some queries, but then in your controller you only have to call one function.

[eluser]Daniel Walton[/eluser]
Join the cars table to the manufacturers, only one query. You can either propogate the array in the model, controller or view

[eluser]E.T.Cook[/eluser]
That is what I am trying to prevent. With most ORM's, you can call derivative tables with the object. The problem with a joined query is that it looks like this:

[Ford][F-150]
[Ford][Focus]
[Ford][Taurus]

when I want it

[Ford]
[F-150]
[Focus]
[Taurus]

The problem with this method is that I will have to write either business logic to fix the display, or loop through each manufacturer, and propagate an array. Both are messy solutions...is there a more automated method?

[eluser]E.T.Cook[/eluser]
[quote author="Jamie Rumbelow" date="1223134018"]The only thing I can think of is to create a model function. You will still have to run some queries, but then in your controller you only have to call one function.[/quote]

I do want to do most of it in the model. I assume then, that there is no pretty way to do it. I will have to loop through the queries, and propagate an array manually. Is that correct?

[eluser]stoop[/eluser]
ET:

I have a similar issue I'm working with. From my bookmarks:

Managing Hierarchical Data in MySQL
http://dev.mysql.com/tech-resources/arti...-data.html

Some db design seems to help make the process a wee easier.

Though not sure as of yet on the activerecord implementation.

-Edit-
And Rockstarapps also had a rather lengthy discussion here:

How do you store a tree in a database table? Part 2
http://www.rockstarapps.com/wordpress/?p=84

sample with code
http://www.rockstarapps.com/wordpress/?p=82

- Stoop

[eluser]m4rw3r[/eluser]
I have made a model for handling hierarchical trees in the DB (my MPTtree), but I made it some time ago, so it hasn't the same ease of use as IgnitedRecord.

[eluser]Sumon[/eluser]
Tree concept seems to me all about category or parent category name. But what about the item description while i am using binary tree. moreover, category and item tables doesn't contain same properties. so as a result how to get

Code:

[Ford]
    [F-150]
    [Focus]
    [Taurus]

instead of

Code:

[Ford][F-150]
[Ford][Focus]
[Ford][Taurus]

using a query in the model without loop or apply logic in view?

[quote author="E.T.Cook" date="1223102492"]
Is there any way to do it without looping each query separately, and manually propagating an array? On the rails version of active record, I believe you use :through, but I can't seem to find anything like that here.
[/quote]
@E.T.Cook: is it really in rails active record? if yes then is rail process (in model or somewhere builtin) the data by looping and applying logic and return desired value. is it?

[eluser]m4rw3r[/eluser]
I've done some peeking into ActiveRecord's code, and that is what it does, from my understanding of Ruby.

It does a join, and then does something like (this is in PHP, and overly simplified):

Code:

foreach($rows as $row)
{
    if( ! isset($result[$row['id']]))
    {
        $result[$row['id']] = new Manufacturer($row);
    }

    $result[$row['id']]->cars[] = new Car($row); // constructor will filter the data
}

[eluser]E.T.Cook[/eluser]
I figured that is what it was doing. I was trying to prevent myself from having to do that. Looks like that is going to be the best method.

Thanks again guys!