Issue: Self refrencing joins with aggregate_rows
Author: arrowgamer
State: closed
Created: 2015-05-17 00:09:37

Hi again. I was trying to use aggregate_rows to eagerly load a model with a self referencing join, and I found that I could not do so unless the rel_for_model function was altered as per this pull request.

The reason is that rel_for_model doesn't appear to support joins via ModelAlias.


Can you provide a test-case that shows the broken behavior?


``` python
from peewee import *
from unittest import TestCase

db = SqliteDatabase(':memory:')


class SomethingThatReferencesItself(Model):
    name = CharField()
    bro = ForeignKeyField('self', null=True, related_name='bros')

    class Meta:
        database = db


class EagerLoadSelfReferencingJoinPleaseYesOkay(TestCase):
    @classmethod
    def setUpClass(cls):
        SomethingThatReferencesItself.create_table()
        bob = SomethingThatReferencesItself.create(name='bob')
        SomethingThatReferencesItself.create(name='joe', bro=bob)
        SomethingThatReferencesItself.create(name='rofl guy', bro=bob)

    def test_get_aggregate_bro(self):
        Bro = SomethingThatReferencesItself.alias()

        joe = (SomethingThatReferencesItself
               .select(SomethingThatReferencesItself, Bro)
               .join(Bro, on=(SomethingThatReferencesItself.bro == Bro.id).alias('bro'))
               .where(SomethingThatReferencesItself.name == 'joe')
               .aggregate_rows()
               .get())

        self.assertIn('bro', joe._obj_cache)
        self.assertEqual(joe._obj_cache['bro'].name, 'bob')
```

I'm using Model._obj_cache to verify that the data was eagerly loaded. Because ModelOptions.rel_for_model does not support joining via ModelAlias, this will not work.

I'm not aware of a way to eagerly load a parent with its children using a self-referencing join, for example, loading the above bob with his bros, but you're the master so perhaps you'd know how to write/test that.


Hmm, I think the fix for this may be more involved. I'm seeing some issues when self-references are queried using either `prefetch` or `aggregate_rows`. &lambda;


Added support for self-joins in `prefetch` in 499bf2e81e66173eb4c50c5436ec43e88cbeb96b. Just need to get `aggregate_rows()` fixed now.


Awesome work! Looking forward to the next release! As you can tell, I use `aggregate_rows` a lot =P


@arrowgamer not quite fixed yet, still need to get the `aggregate_rows()` bit working. Also just FWIW don't mix calls to `aggregate_rows()` with `.get()`.


@coleifer Yes, I saw your commit was only for `prefetch`. I assume by next release you'll have it wrapped up? :laughing:  And I'm aware of what `.get()` does to `aggregate_rows()` via `LIMIT`. Normally I use `SelectQuery[0]` in place of it when expecting more than one result returned from a join. Is there another way?


So from e5ce2bdf71f19bd5d4f8ab87b461bc23dea49da1 I think that the basic use-case should now work. Honestly due to the complexity of the `aggregate_rows()` implementation, plus mixing in `ModelAlias`, I don't know how much more I can do here.


So what would be the syntax of using `aggregate_rows()` to load a parent with its children or, using the above example, load a record with its bros?

Edit: Nevermind, took a look at your testcase =) Thanks!


The example test case shows, but say you have `Category` which has a foreign key like so:

``` python
class Category(Model):
    name = CharField()
    parent = ForeignKeyField('self', related_name='children')
```

You would write:

``` python
Child = Category.alias()
query = (Category
         .select(Category, Child)
         .join(Child, JOIN.LEFT_OUTER, on=(Category.id == Child.parent).alias('kids'))
         .order_by(Category.id, Child.id)
         .aggregate_rows())
for category in query:
    print category.name
    for child in category.kids:
        print '  -', child.name
```

A caveat is that you must specify an alias in the join condition.


Thanks a bunch for the explanation! Once released, I'll report any issues if I find any, as I'm already using aggregated self-referencing joins.