Nesting queries within active record

Nesting queries within active record - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Nesting queries within active record (/showthread.php?tid=12060)

Pages: 1 2

Nesting queries within active record - El Forum - 10-03-2008

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

Nesting queries within active record - El Forum - 10-04-2008

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

Nesting queries within active record - El Forum - 10-04-2008

[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

Nesting queries within active record - El Forum - 10-04-2008

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

Nesting queries within active record - El Forum - 10-04-2008

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

Nesting queries within active record - El Forum - 10-04-2008

[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/articles/hierarchical-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

Nesting queries within active record - El Forum - 10-04-2008

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

Nesting queries within active record - El Forum - 10-04-2008

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

Nesting queries within active record - El Forum - 10-04-2008

[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

}

Nesting queries within active record - El Forum - 10-05-2008

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