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?
I'm thinking that it might be best to have a mapping instead of what
we have now given our polymorphism. Thoughts on that?
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.
