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
------------------------------------------------------------------------------
The NEW KODAK i700 Series Scanners deliver under ANY circumstances! Your
production scanning environment may not be a perfect world - but thanks to
Kodak, there's a perfect scanner to get the job done! With the NEW KODAK i700
Series Scanner you'll get full speed at 300 dpi even with all image
processing features enabled. http://p.sf.net/sfu/kodak-com
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
