sqlobject-discuss

[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

Re: [SQLObject] Joins with aliasing

[Oleg B. <ph...@ph...>]

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.