[SQLObject] Joins with aliasing

[Andrew P. <ap...@gm...>]

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