hi all,
i am working on an open source project (http://project.askspree.de/)
i need to write this simple sql :
SELECT tg_user.websites_specified AS tg_user_websites_specified,
tg_user.display_name AS tg_user_display_name, tg_user.created AS
tg_user_created, tg_user.user_name AS tg_user_user_name,
tg_user.expertise_calculated_subtree AS
tg_user_expertise_calculated_subtree, tg_user.keywords_specified AS
tg_user_keywords_specified, tg_user.expertise_subtree AS
tg_user_expertise_subtree, tg_user.expertise AS tg_user_expertise,
tg_user.user_id AS tg_user_user_id, tg_user.password AS
tg_user_password, tg_user.email_address AS tg_user_email_address
FROM tg_user LEFT JOIN visit_identity ON
tg_user.user_id=visit_identity.user_id
LEFT JOIN visit ON visit_identity.visit_key=visit.visit_key
WHERE visit_identity.user_id IS NULL OR visit.visit_key IS NULL OR
visit.expiry < now()
ORDER BY tg_user.user_id LIMIT 10 OFFSET 0
For this i have mapper classes
User (tg_user)
VisitIdentity (visit_identity)
Visit (visit)
problem is that while defining mapper i couldnt set proper relation to
these tables so i have to write condition to outer join these tables
in the query only.
i have tried these options :
1. session.query(User).select_from(
text(' LEFT JOIN visit_identity ON
tg_user.user_id==visit_identity.user_id LEFT JOIN visit ON
visit_identity.visit_key==visit.visit_key')
).select(
or_( (VisitIdentity.c.user_id==None) ,
(Visit.c.visit_key==None),
(Visit.c.expiry < time)
),
limit = limit,
offset = offset
)
it outputs:
SELECT tg_user.*
FROM tg_user, LEFT JOIN visit_identity ON
tg_user.user_id=visit_identity.user_id
LEFT JOIN visit ON visit_identity.visit_key=visit.visit_key,
visit_identity, visit
WHERE visit_identity.user_id IS NULL OR visit.visit_key IS NULL OR
visit.expiry < now()
ORDER BY tg_user.user_id LIMIT 10 OFFSET 0
means brute force using text wont work here :(
2. select( [User],
from_obj=(
outerjoin(
User,VisitIdentity,User.c.user_id==VisitIdentity.c.user_id).outerjoin(
Visit,VisitIdentity.c.visit_key==Visit.c.visit_key
)
),
).filter(
or_( (VisitIdentity.c.user_id==None) ,
(Visit.c.visit_key==None),
(Visit.c.expiry < time)
)
)
it gives error that: AttributeError: type object 'User' has no
attribute '_selectable'
3. session.query(User).outerjoin(
VisitIdentity,User.c.user_id==VisitIdentity.c.user_id).outerjoin(
Visit,VisitIdentity.c.visit_key==Visit.c.visit_key
).select(
or_( (VisitIdentity.c.user_id==None) ,
(Visit.c.visit_key==None),
(Visit.c.expiry < time)
),
limit = limit,
offset = offset
)
it gives error that:
TypeError: outerjoin() takes exactly 2 arguments (3 given)
documentation says u can pass condition in this outerjoin() (http://
www.sqlalchemy.org/docs/03/sqlalchemy_sql.html#docstrings_sqlalchemy.sql_modfunc_outerjoin)
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
