Kohana ORM order_by a query

0
votes

I'm looking for the best way to order a result set based on the 'most viewed' count of another table?

I have a products table and a history table. The history table stores the view count of the products and is linked in by the product_id.

What is the best way to order the product listing by the 'view_count' column in the history table?

Thanks for help with this.

1
ormkohanakohana-orm
Share your DB schema for the tables involved. - Cody Caughlan

1 Answers

1
votes

Assuming there is a one-to-one- relationship between products and history the query would be something like:

SELECT products.* FROM products
INNER JOIN history on products.id = history.products_id
ORDER BY history.view_count DESC

If there are multiple history records for each product than its a different scenario. This might not scale, you might consider adding the view_count to products and just incrementing it on each view.