On Aug 17, 2009, at 2:48 AM, vindhyavasini wrote:
> > 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) These are docs for the SQL Expression language outerjoin, whereas you're using outerjoin on the ORM query, which is different, and only takes one arg. I think it takes more args in later versions of SQLAlchemy -- 0.3.7 is ancient -- Philip Jenvey --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
