thanx a lot philip...
i know i am using outerjoin on ORM query, but i couln't find doc about
outjoin in ORM query so i sent link to SQL Expression language
outerjoin
i have tried a query also using outerjoin in select from_obj, in 2nd
query that is [i think it is not ORM query is it?? i m a bit novice :
( ]:
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'
do i need to use tables instead of table mapper here in outerjoin???
if needed please refer me some doc which could give me more insight :)
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
-~----------~----~----~----~------~----~------~--~---
