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
> > AND translation_1.title LIKE %s
> > AND price_1.currency_id = %s
> > AND price_1.netto < %s
>
> > with the bind args as:
> > ['en', 'The%', 'rub', 25]
>
> > If it's not what you expected, let me know
> > """
>
> > #now when you access the results, SQLA issues no additional queries
> > for you
> > print [(book.isbn, book.translations[0].title,book.prices[0].netto,)
> > for book in books]
>
> > """
> > a helper application class used in this example (from common import
> > Application)
>
> > class Application(object):
>
> > url = URL( drivername = 'mysql',
> > username = 'root',
> > password = '',
> > host = 'localhost',
> > port = '3306',
> > database = 'test',
> > query = {'charset':'utf8'})
>
> > def __init__(self,echo = True):
> > engine = create_engine(Application.url,encoding = 'utf-8',echo
> > = echo)
> > Session = sessionmaker(bind=engine)
> > self.session = Session()
> > self.metadata = MetaData()
> > self.metadata.bind = self.session.connection()
>
> > def close(self):
> >
>
> ...
>
> więcej »
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
