On 2009-11-25 16:15, Michael Bayer wrote:
> Wichert Akkerman wrote:
>> I have a data model which has accounts and events, and a many-to-many
>> relation between the two using a CalendarEvent class. It boils down to
>> this:
>>
>>
>> class Account(BaseObject):
>> __tablename__ = "account"
>> id = schema.Column("id", types.Integer(), primary_key=True)
>>
>> class Event(BaseObject):
>> __tablename__ = "event"
>> id = schema.Column("id", types.Integer(), primary_key=True)
>>
>> class CalendarEvent(mBaseObject):
>> __tablename__ = "calendar"
>> account_id = schema.Column(types.Integer(),
>> schema.ForeignKey(Account.id, onupdate="CASCADE",
>> ondelete="CASCADE"),
>> primary_key=True, nullable=False)
>> account = orm.relation(Account,
>> backref=orm.backref("calendar", lazy="dynamic"))
>> event_id = schema.Column(types.Integer(),
>> schema.ForeignKey(Event.id, onupdate="CASCADE",
>> ondelete="CASCADE"),
>> primary_key=True, nullable=False)
>> event = orm.relation(Event, lazy=False)
>>
>>
>> the calendar backref works fine when you generate a query for it and
>> generates SQL like this:
>>
>> SELECT calendar.account_id AS calendar_account_id, calendar.event_id AS
>> calendar_event_id, event_1.id AS event_1_id
>> FROM event, calendar LEFT OUTER JOIN event AS event_1 ON event_1.id =
>> calendar.event_id
>> WHERE :param_1 = calendar.account_id
>>
>> but if you use acount.calendar.count() the join conditions disappears
>> and you end up with this:
>>
>> SELECT count(1) AS count_1
>> FROM calendar, event
>> WHERE %(param_1)s = calendar.account_id
>
>
> why is "event" in either of those queries ? In the first case its valid
> for the LEFT OUTER JOIN since that's the eager load, but I also see it
> stated a second time, by itself, not associated to anything. Both queries
> are incorrect. The mapping you illustrate doesn't involve "event" at all
> between Account and Calendar, which wouldn't be rendered as a standalone
> FROM object unless you are specifically saying filter(Event.foo == 'bar')
> without establishing your join().
I should have been more explicity, sorry about that. This is the full query:
today = datetime.date.today()
query = self.context.calendar\
.filter(models.Event.start_date>=today)\
.filter(models.Event.state=="active")\
.order_by(models.Event.start_date,
models.CalendarEvent.start_time,
models.Event.start_time)
I was expecting the join to automatically created since the
Account.calendar relation depends on it. Adding a .join(model.Event) to
the query before the filter() calls indeed fixes this. Does SQLAlchemy
indeed assume that my usage of models.Event as query filters has no
relation at all to the calendar relation?
Wichert.
--
Wichert Akkerman <wich...@wiggy.net> It is simple to make things.
http://www.wiggy.net/ It is hard to make things simple.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
