Hi all, I've got a problem with what should be a very simple query. I
have reduced the problem to its simplest form so I hope it is clear
enough.
I've got a three levels hierarchy: a<-b<-c. Now, the "b" table has a
timestamp field. I'm building a select object so I can map a query to
a class using ORM, adding a "dayssince" (or "timesince") property to
the class.
Then, I have to select all records in b without children records in c
(left outer join, "c.id is NULL"), and I have to include the related
records in a (eagerloading). I think it'll be easier to see with the
following code:
a_table = Table('a', metadata,
Column('id', Integer, primary_key = True),
Column('somedata', Unicode),
)
b_table = Table('b', metadata,
Column('id', Integer, primary_key = True),
Column('somedatainb', Unicode),
Column('modtime', DateTime, default = datetime.utcnow()),
Column('a_id', Integer, ForeignKey('a.id')),
)
c_table = Table('c', metadata,
Column('id', Integer, primary_key = True),
Column('somedatainc', Unicode),
Column('b_id', Integer, ForeignKey('b.id')),
)
class A(object):
pass
class B(object):
pass
class C(object):
pass
s = select([b_table, func.now().op('-')
(b_table.c.modtime).label('dayssince')]).alias('b_table_b')
assign_mapper(session.context, A, a_table,
properties = {
'bs':relation(B, lazy = None)
}
)
assign_mapper(session.context, B, s,
properties = {
'a':relation(A, lazy = None),
'cs':relation(C, lazy = None),
}
)
assign_mapper(session.context, C, c_table,
properties = {
'b':relation(B, lazy = None)
}
)
The query should be this one:
query = session.query(B)
q2 = query.options(eagerload('a'))
oj = q2.outerjoin('cs')
oj2 = oj.filter(c_table.c.id == None)
res = list(oj2)
That query works if I don't use the select object and map b_table
directly. It also works if I don't try to eagerload "a".
The error is "SQLError: (ProgrammingError) table name "b_table_b"
specified more than once"
And the query being executed is:
SELECT a_e6d8.somedata AS a_e6d8_somedata, a_e6d8.id AS a_e6d8_id,
b_table_b.modtime AS b_table_b_modtime, b_table_b.dayssince AS
b_table_b_dayssince, b_table_b.somedatainb AS b_table_b_somedatainb,
b_table_b.a_id AS b_table_b_a_id, b_table_b.id AS b_table_b_id
FROM (SELECT b.id AS id, b.somedatainb AS somedatainb, b.modtime AS
modtime, b.a_id AS a_id, (now() - b.modtime) AS dayssince
FROM b) AS b_table_b LEFT OUTER JOIN c ON b_table_b.id = c.b_id,
(SELECT b.id AS id, b.somedatainb AS somedatainb, b.modtime AS
modtime, b.a_id AS a_id, (now() - b.modtime) AS dayssince
FROM b) AS b_table_b LEFT OUTER JOIN a AS a_e6d8 ON a_e6d8.id =
b_table_b.a_id
WHERE c.id IS NULL ORDER BY b_table_b.id, a_e6d8.id
which of course, is wrong.
I've been trying lots of things, but I just can't get it to work, and
I thought maybe I am missing something simple enough for someone to
point out.
Thanks!
Juan
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
