Thread: [SQLObject] Joins with aliasing
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Andrew P. <ap...@gm...> - 2009-05-12 23:47:16
|
I have quite a complex query, and I am trying to select from the database with SQLObject instead of using the query directly. Here is the table structure: class Item(SQLObject): """An item definition (e.g. 'The Life of Brian DVD')""" name = StringCol(length=15, alternateID=True) attributes = MultipleJoin('ItemAttribute') class Attribute(SQLObject): """An attribute definition (e.g. ISBN is a String)""" #columns name = StringCol(length=10, alternateID=True) title = StringCol(length=20) dataType = IntCol(length=1) class ItemAttribute(SQLObject): """An attribute instantiation (e.g. ISBN='abcd1234')""" item = ForeignKey('Item', cascade=True) attribute = ForeignKey('Attribute') value = StringCol(length=50) As you can see, each item may have zero or more item_attributes. Each item_attribute relates to exactly one attribute, which gives it an attribute name. Here is my query: SELECT item.* FROM item LEFT JOIN item_attribute as item_attribute0 ON item.id = item_attribute0.item_id LEFT JOIN attribute as attribute0 on attribute0.id = item_attribute0.attribute_id LEFT JOIN item_attribute as item_attribute1 ON item_attribute0.item_id = item_attribute1.item_id LEFT JOIN attribute as attribute1 on attribute1.id = item_attribute1.attribute_id WHERE 1 AND attribute0.name = 'price' AND item_attribute0.value = '19.99' AND attribute1.name = 'isbn' AND item_attribute1.value = 'life123'; In this specific example, the query (in MySQL) selects all items that have a price of 19.99 and ISBN of 'life123'. More generally, I use queries like this to select items that have certain attributes. Each attribute I am searching for creates two left joins, one for the item_attribute itself and one for the attribute's name. Not sure how to make such a selection through the SQLObject ORM. Any help is greatly appreciated! -Andrew Peace |
From: Oleg B. <ph...@ph...> - 2009-05-13 07:01:31
|
On Tue, May 12, 2009 at 07:47:10PM -0400, Andrew Peace wrote: > class Item(SQLObject): > """An item definition (e.g. 'The Life of Brian DVD')""" > name = StringCol(length=15, alternateID=True) > attributes = MultipleJoin('ItemAttribute') > > class Attribute(SQLObject): > """An attribute definition (e.g. ISBN is a String)""" > #columns > name = StringCol(length=10, alternateID=True) > title = StringCol(length=20) > dataType = IntCol(length=1) > > class ItemAttribute(SQLObject): > """An attribute instantiation (e.g. ISBN='abcd1234')""" > item = ForeignKey('Item', cascade=True) > attribute = ForeignKey('Attribute') > value = StringCol(length=50) > > SELECT item.* > FROM item > LEFT JOIN item_attribute as item_attribute0 ON item.id = item_attribute0.item_id > LEFT JOIN attribute as attribute0 on attribute0.id = > item_attribute0.attribute_id > LEFT JOIN item_attribute as item_attribute1 ON item_attribute0.item_id > = item_attribute1.item_id > LEFT JOIN attribute as attribute1 on attribute1.id = > item_attribute1.attribute_id > WHERE 1 > AND attribute0.name = 'price' > AND item_attribute0.value = '19.99' > AND attribute1.name = 'isbn' > AND item_attribute1.value = 'life123'; from sqlobject.sqlbuilder import Alias, LEFTJOINOn attribute0 = Alias(Attribute, 'attribute0') item_attribute0 = Alias(ItemAttribute, 'item_attribute0') item_attribute1 = Alias(ItemAttribute, 'item_attribute1') and then something like this: Item.select( join=[ LEFTJOINOn(None, item_attribute0, Item.q.id == item_attribute0.q.itemID), LEFTJOINOn(None, attribute0, attribute0.q.id == item_attribute0.q.attributeID), ... other left joins ... ], clause = ( (attribute0.q.name == 'price') && (item_attribute0.q.value == 19.99) && ... other where clauses ... ) ) Or, if you prefer this form clause = AND( (attribute0.q.name == 'price'), (item_attribute0.q.value == 19.99), ... other where clauses ... ) Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |