On May 26, 2010, at 8:01 PM, ObjectEvolution wrote:
> Thanks for the input Michael. I think the polymorphism is messing
> things up...just a hunch. Your suggestion didn't work but this ended
> up working:
>
> 'children': relation(Category,
>
> primaryjoin=and_(TABLES.CATEGORY.c.id==TABLES.CATEGORY.c.parent_id,
>
> TABLES.BASE.c.deleted==False),
> backref=backref('parent',
>
> remote_side=(TABLES.CATEGORY.c.id)),
> ),
>
> Does that make sense? I was excited that it worked at first but then I
> wasn't quite sure how it worked. How does it recognize TABLES.BASE?
if the mapper is against the join of base->category, then both tables are
present in the FROM clause using a join.
>
> I'm thinking that it might be best to have a mapping instead of what
> we have now given our polymorphism. Thoughts on that?
not sure what this means. I was going to also suggest having a second mapper
against Category that filters out the "deleted" rows, is that what you mean ?
>
>
> On May 26, 3:00 pm, Michael Bayer <[email protected]> wrote:
>> On May 26, 2010, at 4:56 PM, ObjectEvolution wrote:
>>
>>
>>
>>> Hi,
>>
>>> I've got the following tables in my app (only showing applicable
>>> columns here) storing categories for my app:
>>
>>> Base
>>> - id (int) PK
>>> - deleted (int) - 0/1 as a value
>>
>>> Category
>>> - id (int) PK/FK - refers to Base.id
>>> - parent_id (int) FK - self-referential to Category.id
>>
>>> I then have a Category object, which inherits from Base. All's good.
>>
>>> What I'm trying to do is when I get my Category object I only get
>>> children which aren't deleted=1. My original property in my mapper was
>>> this:
>>
>>> 'children': relation(Category,
>>> primaryjoin=TABLES.CATEGORY.c.id==
>>> TABLES.CATEGORY.c.parent_id,
>>> backref=backref('parent',
>>
>>> remote_side=[TABLES.CATEGORY.c.id]
>>> ),
>>> ),
>>
>>> Which works fine but gets everything. So I changed it to this:
>>
>>> 'children': relation(Category, secondary=TABLES.BASE,
>>
>>> primaryjoin=TABLES.CATEGORY.c.id==TABLES.CATEGORY.c.parent_id,
>>
>>> secondaryjoin=and_(TABLES.BASE.c.id==TABLES.CATEGORY.c.id,
>>
>>> TABLES.BASE.c.deleted==False),
>>> foreign_keys=[TABLES.CATEGORY.c.id],
>>> backref=backref('parent',
>>
>>> remote_side=[TABLES.CATEGORY.c.id]
>>> ),
>>> ),
>>
>> the way "secondary" works is:
>>
>> parent -> primaryjoin-> secondary -> secondaryjoin -> child
>>
>> So primaryjoin has to be in terms of "category" and "base", as does
>> secondaryjoin. Also you don't use "remote_side" with "secondary".
>> "foreign_keys" are also usually implicit from your Table metadata and its
>> rare these are needed (unless an error message asks for them, which often
>> indicates something else is the actual issue).
>>
>> what you have here really does not appear to be a many-to-many relationship,
>> its one-to-many/many-to-one. So if you want the relationship to add a
>> where criterion for the "base.deleted", you likely just want to use and_()
>> all within the primaryjoin. and_(category.id==category.parent_id ,
>> base.deleted==False, base.id==category.id).
>>
>>
>>
>>> and I get nothing. Not a single object. Is my issue:
>>
>>> 1. Foreign key related?
>>> 2. Join related?
>>> 3. Developer related?
>>
>>> Any help here is appreciated.
>>
>>> Thanks!
>>
>>> Jon
>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To post to this group, send email to [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> For more options, visit this group
>>> athttp://groups.google.com/group/sqlalchemy?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
