Issue: Aggregate Self-Referencing Joins via Child to Parent
Author: arrowgamer
State: closed
Created: 2015-06-02 00:15:50

Hi there. I tested out your changes in version 2.6.1, and while we're now able to use aggregate_rows() to load a record with its children via a self-referencing join, loading a child with its parent does not seem possible.

Below is the code I'm using to test. The first test now passes. The second test does not pass due to ModelAlias being passed to rel_for_model, though altering the code to pass the alias' model_class causes the first test to fail.

Do you plan on fixing this issue? Is there any workaround?

``` 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_bros(self):
        Bro = SomethingThatReferencesItself.alias()

        bob = (SomethingThatReferencesItself
               .select(SomethingThatReferencesItself, Bro)
               .join(Bro, on=(SomethingThatReferencesItself.id == Bro.bro).alias('bros'))
               .where(SomethingThatReferencesItself.name == 'bob')
               .aggregate_rows())[0]

        self.assertIn('bros', bob._meta.reverse_rel.keys())
        self.assertIsInstance(bob.bros, list)
        self.assertEqual(len(bob.bros), 2)

    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())[0]

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


Shoot...be great if in the future the test-cases could use models in the existing tests, as much as I enjoy your 'bro' models lol.

Have you tried using `prefetch` instead of `aggregate_rows()`? I think that there are enough complexities to `aggregate_rows()` that making it a general-purpose does-everything kind of method will require some rethinking from the ground-up.


So this case is ambiguous and when using `aggregate_rows()` peewee assumes that you want the 1->N relationship, i.e. the category and all it's children, rather than the category and it's parent category.

That's kind of the point of using aggregate_rows() and is the reason I added this assumption. If you have a self-reference and want to get the parent, you can always write:

``` python
            Grandparent = Category.alias()
            Parent = Category.alias()
            sq = (Category
                  .select(Category, Parent, Grandparent)
                  .join(Parent, on=(Category.parent == Parent.id))
                  .join(Grandparent, on=(Parent.parent == Grandparent.id))
                  .where(Grandparent.name == 'old granddaddy')
                  .order_by(Category.name))
            for cat in sq:
                print cat.name, cat.parent.name, cat.parent.parent.name
```


I can see how this would be problematic if you have multiple joins though, i.e. you want to select the category and it's parent, but also some other objects that have a foreign key to category.


In my actual use case I am eagerly loading a table that joins to a few other tables including one table that references itself, twice, as well as joining those self references with another table.

So it's a pretty complex query, haha. Loading data from several tables to be serialized to json is basically the crux of the project, though, and so `aggregate_rows()` was an important factor in choosing to use peewee =(

Thanks for the suggestion, though!


Yeah, it's just a limitation right now of `aggregate_rows()` that when you have a self-referential join it will assume you want the 1->N relationship rather than the parent object. Have you tried using `prefetch()`? I feel like I've asked you that before a couple times.