Modeling a tree

[Jason Horman]

I have a data type called Organization that be be contained by other Organizations. Is there a way to model that?

class Organization(Model):

  parent = peewee.ForeignKeyField(Organization)

won't work b/c Organization isn't defined yet. Adding it later doesn't update the meta class.

[Charles]

Check out self referential fks: http://peewee.readthedocs.org/en/latest/peewee/models.html#self-referential-foreign-keys

class Organization(Model):

    parent = ForeignKeyField('self', related_name='children', null=True)

[Jason Horman]

I promise I did try to find that in the docs first. Thanks a lot.

[Ivan Kleshnin]

Is there a way to print model ID and parent ID without triggering an additional SELECT?

class Item(Model):

    id = PrimaryKeyField()

    code = CharField()

    name = CharField()

    parent = ForeignKeyField('self', related_name='children', null=True)

items = Item.select().where(Item.id << ids)

for item in items:

    print item.parent, item.id # +1 query

[Ivan Kleshnin]

I meant is there more "official" way than accessing to: 

item._data['parent'] ?

[Charles]

Ivan -- thank you for pointing this out, you are right: there is currently a limitation in peewee's query generation that prevents a self-join (or selecting and populating a related instance of the same model).  If we try to do it how we would normally:

Item.select(Item, Item???).join(Item, on=Item.parent)

We get:

'SELECT t1."id", t1."name", t1."parent_id", t1."id", t1."name", t1."parent_id" FROM "item" AS t1 INNER JOIN "item" AS t1 ON t1."parent_id" = t1."id"

There needs to be a way to specify when selecting that the second ``Item`` refers to the second table.  Similarly, the same table alias is used for both "t1" -- we need a way to know that "t2" should be used.  If we tried to do something in the where clause, it is similarly ambiguous:

Item.select(...).join(Item, on=Item.parent).where(Item.name == 'foo')

SELECT t1."id", t1."name", t1."parent_id", t1."id", t1."name", t1."parent_id" FROM "item" AS t1 INNER JOIN "item" AS t1 ON t1."parent_id" = t1."id" WHERE (t1."name" = ?)

How to let peewee know which "item" we want to be querying?  I will think about a fix, but any suggestions for an API are appreciated.

[Ivan Kleshnin]

I'm not good at data structures but as I understand the main inner limitation for self-joins is alias_map dict

{

 <class 'application.users.models.Price'>: 't1', 

 <class 'application.users.models.PriceRelation'>: 't2'

}

The same table == the same key. This blocks the very possibility of granting 2 different aliases to single table.

I can only propose to flip this structure:

{

 't1': <class 'application.users.models.Price'>, 

 't2': <class 'application.users.models.PriceRelation'>,

}

Then, I think, Model class should supports alias() method, returning unique object to differ.

t2 = Item.alias('t2')

Item.select(Item, t2).join(t2, on=t2.parent).where(t2.name == 'foo')

Maybe I don't understand quite well the meaning of alias term in Peewee, but I'd come with something like that.

[Charles]

Right -- that dictionary keyed by model class is the limitation currently.  There are other places things might be odd, too -- I'm thinking possibly when constructing instances in the QueryResultWrapper.

Anyways, yeah, I've been playing with a similar API -- implementation-wise I've considered a couple options for what the alias method should return.  One is a model subclass, another is a wrapper/proxy.  The benefit of the subclass is that we can reuse all the APIs we already have -- the downside is that foreign key fields aren't behaving and a hack will have to be put in place to make that work.  The benefits of a wrapper/proxy is that it may be a cleaner separation of concerns...the downside is needing to add special-case logic in various places.