Hello,
Actually, you can join on any condition, not only on the relation
condition,
In this case your query needs to evolve from:
.outerjoin((ATranslation,Book.translations))\
to:
.outerjoin((ATranslation,
and_(ATranslation.book_id == Book.isbn,
ATranslation.language_id == 'en')))\
what will give you
LEFT OUTER JOIN translation AS translation_1
ON book.isbn = translation_1.book_id
AND
translation_1.language_id = %s
what is probably what you want, you can also refer to
http://www.sqlalchemy.org/docs/05/sqlexpression.html#sql_joins
to get more info on this.
Alex
On 24 сент, 03:50, g00fy <[EMAIL PROTECTED]> wrote:
> One more thing,
>
> If i have just book and translations
>
> 1 Book has 3 translations
>
> this sql will create me 3 rows (due to the join)
>
> isbn translation_code translation_text
> 1 en "The Book"
> 1 ru "Книжка"
> 1 pl "Ksiazka"
>
> so if i have 10 books i will have 30 rows.
>
> this is NxM,
> what if I just want to join by :
>
> LEFT OUTER JOIN translation AS translation_1
> ON (book.isbn = translation_1.book_id ,
> translation_1.code="en")
>
> so i will only get the required information ?
> Limiting the results will also be a problem becouse simple LIMIT will
> not be enough.
>
> (consider having 30 multiple relations, it will generate huge ammount
> of data M*n*x*...)
>
> On 23 Wrz, 16:37, g00fy <[EMAIL PROTECTED]> wrote:
>
> > Thnx a lot Alex! I already love Pylons and SQLAlchemy!
>
> > On 23 Wrz, 12:16, Alex K <[EMAIL PROTECTED]> wrote:
>
> > > Hello, here is the answer:
>
> > > # -*- coding: utf-8 -*-
>
> > > from sqlalchemy import create_engine
> > > from sqlalchemy import Table, Column, Integer, String, Unicode,
> > > MetaData, ForeignKey
> > > from sqlalchemy.orm import sessionmaker, mapper,
> > > dynamic_loader,backref, relation, composite, comparable_property,
> > > contains_eager,aliased
> > > from sqlalchemy.sql import text, and_
> > > from orm.object.factory import make_papped_class
> > > from sqlalchemy.orm import PropComparator
>
> > > from common import Application
>
> > > app = Application()
> > > session = app.session
> > > metadata = app.metadata
>
> > > """
> > > Book :
> > > isbn -> integer
> > > translations -> many translations
> > > prices -> many prices
>
> > > Translation :
> > > book -> FK to book
> > > laguage -> FK to Language (oneTOone)
> > > title -> string
>
> > > Language :
> > > code -> string
> > > name -> string
>
> > > Currency :
> > > code -> string
> > > name -> string
>
> > > Price :
> > > currency -> FK
> > > book ->FK
> > > brutto -> int
> > > netto -> int
>
> > > """
>
> > > book_table = Table('book', metadata,
> > > Column('isbn', Integer, primary_key=True)
> > > )
>
> > > language_table = Table('language', metadata,
> > > Column('code', String(5), primary_key=True),
> > > Column('name', Unicode(20)),
> > > )
>
> > > currency_table = Table('currency', metadata,
> > > Column('code', String(5), primary_key=True),
> > > Column('name', Unicode(20)),
> > > )
>
> > > translation_table = Table('translation', metadata,
> > > Column('book_id', Integer,
> > > ForeignKey(book_table.c.isbn)),
> > > Column('language_id', String(5),
> > > ForeignKey(language_table.c.code)),
> > > Column('title', Unicode(512)),
> > > )
>
> > > price_table = Table('price', metadata,
> > > Column('currency_id', String(5),
> > > ForeignKey(currency_table.c.code)),
> > > Column('book_id', Integer,
> > > ForeignKey(book_table.c.isbn)),
> > > Column('brutto', Integer),
> > > Column('netto', Integer)
> > > )
>
> > > metadata.create_all()
>
> > > #create objects
> > > class Book(object):
> > > def __init__(self,isbn):
> > > self.isbn = isbn
>
> > > class Language(object):
> > > def __init__(self,code,name):
> > > self.code = code
> > > self.name = name
>
> > > class Currency(object):
> > > def __init__(self,code,name):
> > > self.code = code
> > > self.name = name
>
> > > class Translation(object):
> > > def __init__(self,book_id,language_id,title):
> > > self.book_id = book_id
> > > self.language_id = language_id
> > > self.title = title
>
> > > class Price(object):
> > > def __init__(self,currency_id,book_id,brutto,netto):
> > > self.currency_id = currency_id
> > > self.book_id = book_id
> > > self.brutto = brutto
> > > self.netto = netto
>
> > > mapper(Book,book_table)
> > > mapper(Language,language_table)
> > > mapper(Currency,currency_table)
>
> > > mapper(Translation,translation_table,properties = {
> > > 'book': relation(Book,
> > > lazy = False, #1 note lazy here, it means that
> > > we
> > > # will use lazy loading (more
> > > details in the docs
> > > backref = backref('translations',lazy = False)),
> > > 'language': relation(Language,
> > > uselist = False,#2 note uselist, it means
> > > #we use one-to-one instead of
> > > one-to-many
> > > lazy = False),},
>
> > > primary_key = [translation_table.c.book_id,
> > > translation_table.c.language_id]
> > > #explicit primary key is needed when SQLA can not assemble the one for
> > > you automatically
> > > );
>
> > > mapper(Price,price_table,properties = {
> > > 'currency': relation(Currency,lazy = False),
> > > 'book': relation(Book,lazy = False, backref =
> > > backref('prices',lazy = False))
>
> > > },primary_key = [price_table.c.book_id,price_table.c.currency_id]);
>
> > > session = app.session
>
> > > if False: #change this to True to issue add statement
> > > session.add(Language('en',u'English'))
> > > session.add(Language('ru',u'Русский'))
>
> > > session.add(Currency('usd',u'Dollar'))
> > > session.add(Currency('rub',u'Рубль'))
>
> > > session.add(Book(1))
> > > session.add(Book(2))
>
> > > session.flush()
>
> > > session.add(Translation(book_id = 1, language_id = 'en', title =
> > > u'The book'))
> > > session.add(Translation(book_id = 1, language_id = 'ru', title =
> > > u'Книжка'))
>
> > > session.add(Translation(book_id = 2, language_id = 'en', title =
> > > u'Book'))
> > > session.add(Translation(book_id = 2, language_id = 'ru', title =
> > > u'Книжка2'))
>
> > > session.add(Price(book_id = 1, currency_id = 'usd', brutto = 12,
> > > netto = 20))
> > > session.add(Price(book_id = 1, currency_id = 'rub', brutto = 250,
> > > netto = 500))
>
> > > session.add(Price(book_id = 2, currency_id = 'usd', brutto = 10,
> > > netto = 18))
> > > session.add(Price(book_id = 2, currency_id = 'rub', brutto = 200,
> > > netto = 440))
>
> > > """
> > > would like now to get books that:
> > > isbn>1
> > > translation.title in english starts with "The" ( I don't need other
> > > languages for this select)
> > > price.netto < 100 USD ( I don't need other prices, only in USD for
> > > this select)
>
> > > There are 5 Tables to join.
> > > Lets say I have 100 objects maching, I dont want to hit the db 100
> > > times.
> > > """
>
> > > # the common approach here, as Michael says,
> > > # is to construct the query as you would do it yourself
> > > # and to tel sql alchemy where to pick the data for objects from
>
> > > #aliases to control the query
> > > ATranslation = aliased(Translation)
> > > APrice = aliased(Price)
>
> > > books = session.query(Book)\
> > > .outerjoin((ATranslation,Book.translations))\
> > > .outerjoin((APrice,Book.prices))\
> > > .filter(and_(
> > > Book.isbn >= 1,
> > > ATranslation.language_id == 'en',
> > > ATranslation.title.like(u'The%'),
> > > APrice.currency_id == 'usd',
> > > APrice.netto < 25,))\
> > > .options(contains_eager('translations',alias =
> > > ATranslation))\
> > > .options(contains_eager('prices',alias = APrice))
>
> > > """
> > > .outerjoin((ATranslation,Book.translations)) - creates explicit
> > > join to the named alias,
> > > and tells that this join implements Book.translations relation
>
> > > ATranslation.language_id == 'en', - now we can use the named alias
> > > to issue filter statements
>
> > > .options(contains_eager('translations',alias = ATranslation)) -
> > > now refer to the note #1,
> > > this statement says to SQLA that it needs to load data for the
> > > translation relation from this alias
>
> > > """
>
> > > """
> > > at the end the query gives you the following statement (MySQL):
>
> > > SELECT book.isbn AS book_isbn,
> > > currency_1.code AS currency_1_code,
> > > currency_1.name AS currency_1_name,
> > > price_1.currency_id AS price_1_currency_id,
> > > price_1.book_id AS price_1_book_id,
> > > price_1.brutto AS price_1_brutto,
> > > price_1.netto AS price_1_netto,
> > > language_1.code AS language_1_code,
> > > language_1.name AS language_1_name,
> > > translation_1.book_id AS translation_1_book_id,
> > > translation_1.language_id AS translation_1_language_id,
> > > translation_1.title AS translation_1_title
> > > FROM book
> > > LEFT OUTER JOIN translation AS translation_1
> > > ON book.isbn = translation_1.book_id
> > > LEFT OUTER JOIN price AS price_1
> > > ON book.isbn = price_1.book_id
> > > LEFT OUTER JOIN currency AS currency_1
> > > ON currency_1.code = price_1.currency_id
> > > LEFT OUTER JOIN language AS language_1
> > > ON language_1.code = translation_1.language_id
> > > WHERE
> > > translation_1.language_id = %s
> > >
>
> ...
>
> продолжение >>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
