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).
On 2 Jan, 2006, at 12:50 pm, midtoad wrote:
Jeff:
are you saying that SQLObject has an inability to do complex
queries or
that *you* have an inability? How about posting your query and
letting
us see if we can find a way to do the query using SQLObject?
S
--
Jeff Watkins
http://newburyportion.com/
"Daddy, I want a purple iMac. And I want ice cream!"
-- Unidentified 7-year-old to his father.
