CodeIgniter Forums

Full Version: Nesting queries within active record
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Pages: 1 2

El Forum

[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.

El Forum

[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.

El Forum

[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

El Forum

[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?

El Forum

[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?

El Forum

[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

El Forum

[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.

El Forum

[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?

El Forum

[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
}

El Forum

[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!
Pages: 1 2