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 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.
