I have this huge ugly query below, i'd like to sort by it in a catalog view. Thinking something like http://wow.dev:3000/catalog_items?&order=deals. A million thank yous in advance for any comments or answers.
select 100 - round((current_price / item.estimated_price)*100) as percent, item.cached_thumbnail_url, item.item_id, it.name,
ci.current_price, ci.close_date
from item
join catalog_item ci on ci.item_id = item.item_id
join item_translations as it on (it.item_id = item.item_id)
where (100 - round((current_price / item.estimated_price)*100)) > 49 and
item.estimated_price > 0 and ci.current_price > 0 and ci.close_date > now() and item.active = 1 and ci.active = 1 and
(current_price / estimated_price) < 1
order by (ci.close_date < DATE_ADD(now(), INTERVAL 17 hour)) and (item.estimated_price - current_price) desc
limit 12
Not sure how this could be related to ROR, but anyways:
You have 100 - round((current_price / item.estimated_price)*100) as percent
in your SELECT clause, yet anyway using the same expression in WHERE conditions.
Can item.estimated_price
be less than zero? If not the item.estimated_price > 0
condition is excessive, if it's zero the (100 - round((current_price / item.estimated_price)*100)) > 49
condition will be false
The (current_price / estimated_price) < 1
is excessive for same reasons
So you query can be rewritten a bit more clearly like this:
select (100 - round((current_price / item.estimated_price)*100)) as percent,
item.cached_thumbnail_url, item.item_id, it.name,
ci.current_price, ci.close_date
from item
join catalog_item ci on ci.item_id = item.item_id
join item_translations as it on (it.item_id = item.item_id)
where
percent > 49
and ci.current_price > 0
and ci.close_date > now()
and item.active = 1
and ci.active = 1
order by
(ci.close_date < DATE_ADD(now(), INTERVAL 17 hour))
and (item.estimated_price - current_price) desc
limit 12
This doesn't improve the situation a lot, but I can't say more without knowing any more reasons about your DB architecture.
BTW the link in your question doesn't work (it's you local link obviously)