i know if i use later versions my problem will be easily solved but upgrading to newer version might screw some parts of project, that i will have to check and will be a bit bothersome task. please suggest me something to make it work with this version only.
On Aug 17, 11:19 pm, Philip Jenvey <pjen...@underboss.org> wrote: > 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....) > > 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 -~----------~----~----~----~------~----~------~--~---
