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): self.session.commit() self.session.close() """ app.close() On Sep 22, 10:25 pm, g00fy <[EMAIL PROTECTED]> wrote: > I have this problem with setting relations with specific models: > > 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 > > so those are my models. > > I 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. > > I am begginer in SQLAlchemy so please understand my maybe "Stupid" > question. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---