[TurboGears] Re: SQLObject transaction problems

[Jonathan LaCour]

On Jan 2, 2006, at 7:07 PM, Jeff Watkins wrote:
While it *MIGHT* be possible to create this query using SQLBuilder,  
it certainly wouldn't be any easier using SQLBuilder than raw SQL:

                select t1.product_id,
                       t1.home*%(home_page_weight)s +
                           t1.product*%(product_page_weight)s +
                           coalesce(t2.num,0)*%(order_weight)s as  
score
                from
                    category_product,
                    (select product_id, sum(home_view) as home,
                            sum(product_view) as product
                     from product_view group by product_id) as t1
                left join
                    (select product_id, sum(product_id) as num
                     from order_entry group by product_id) as t2
                on t1.product_id=t2.product_id
                where t1.product_id=category_product.product_id and
                      category_product.category_id=%(category_id)s
                order by score desc

This query is only likely to become *MORE* complex as I add  
different view statistics (which will appear not as separate  
columns, but as a view type).

Why not just create an SQL view, and map the view as an SQLObject?   
This is often a good solution for classes of problems where query  
performance is more important than insert performance, like most web  
apps that I have seen.

Granted, I feel your pain.  SQLObject isn't particularly good at the  
stuff like this that I rarely, but still need, to do.  It sure does  
simplify the 90% of CRUD on most tables though!

Maybe one day SQLObject, or something else, will come along to make  
building reporting-like queries easier than writing raw SQL, but  
thats a pretty tall order.

--
Jonathan LaCour
http://cleverdevil.org