[SQLObject] Direct SQL vs SQLObject

[Koen B. <ko...@ma...>]

In my application (POS) I use some direct SQL to optimize  
performance. The application runs over DSL, so one big query is very  
fast, but many small queries make my app a dog to use.

I know it is not a Bad Thing© to do this, but I find it hard where to  
do it and where not. By doing it, I use functionality while direct  
sql returns dicts and sqlobject editable objects (and database  
independent).

To make myself more sure in this I hope you can comment on wether  
what you think about this example.

The idea is that I need a selectable list with products selected by a  
name but which also include the number of 'available' products. The  
available products are calculated by the current stock minus the sum  
all quantities of the product in all orders.

So: Would you do this in SQLObject or in a raw sql query.


I have these database classes:
--------------------------------------------------------------------

class Product(SQLObject):
     code = UnicodeCol()
     name = UnicodeCol()
     price = CurrencyCol()
     orderproducts = MultipleJoin('OrderProduct') #Products on an Order

class OrderProduct(SQLObject):
     qty = DecimalCol(size=3, precision=0)
     product = ForeignKey('Product')

class Stock(SQLObject):
	main = IntCol()
	showroom = IntCol()
	product = ForeignKey('Product')
	location = ForeignKey('Location')

class Location(SQLObject):
     name = UnicodeCol()
     stocks = MultipleJoin('Stock')
     orders = MultipleJoin('Order')


I need this list:
--------------------------------------------------------------------

SELECT
	product.id,
	product.name,
     product.price,
     stock.main-COALESCE(SUM(qty),0) AS available
FROM product
	LEFT JOIN stock ON stock.product_id=product.id
	LEFT JOIN order_product ON order_product.product_id=product.id
WHERE product.name ILIKE '%ipod%'
AND stock.location_id=1
GROUP BY
	product.name,
	product.price,
	stock.main,
	product.id"""