I’m trying to sort products on my website by their sales volume. My thought is to create a view that tabulates the quantity sold by product_id over the past 180 days.
I’ve got a question regarding the query:
SELECT p.products_id, COUNT( op.products_id ) AS num_ordered
FROM orders_products op
JOIN products p
JOIN orders AS o
WHERE op.products_id = p.products_id
AND o.orders_id = op.orders_id
AND (
TO_DAYS( NOW( ) ) - TO_DAYS( o.date_purchased )
) <180
GROUP BY p.products_id
ORDER BY num_ordered DESC
This works great, but it doesn’t include products that have not been purchased during that window. How do I specify that the result should include all products, even ones with zero sales?
Assuming we figure that out, is a view and efficient way to tabulate these results? Would the view automatically be updated whenever a new product is added or a product is ordered? I’m a noobie when it comes to views…
a view is not really necessary here, it doesn’t do anything for you that the query itself can’t do
the key is to use LEFT OUTER JOINs
SELECT p.products_id
, COUNT( op.products_id ) AS num_ordered
FROM products AS p
LEFT OUTER
JOIN orders_products op
ON op.products_id = p.products_id
LEFT OUTER
JOIN orders AS o
ON o.orders_id = op.orders_id
AND o.date_purchased >= CURRENT_DATE - INTERVAL 180 DAY
GROUP
BY p.products_id
ORDER
BY num_ordered DESC
Thanks, Rudy. Each time a customer clicks on a product category on the site, I want to sort the products in that category by sales volume. It is likely that I don’t understand how views work, but my thought was that a view is cached so that the query doesn’t need to be re-run each time a customer clicks on a category. Is that not the case? Does it get executed each time you run a query against the view?
If the query gets run each time, then I suppose I should integrate the query so it just looks at the products within a given product category rather than looking across all products.
Am I better off creating a fixed table and then updating it every night with a cron job via PHP? Or is there a way to do something similar within mysql?
I mean create a table that gets updated periodically or is cached so that the query doesn’t need to run each time, but can be run every now and then. I really don’t care if the table is up to the minute…
Part of my motivation for the view was out of laziness, because all the queries are already written within my ecommerce platform. I was hoping to just change the “order by” modifier to get what I want without digging into the queries themselves.
yes, you’re right, having counts saved as static data would take some load off your system, and it’s a good idea especially if, as you say, accurate counts aren’t necessary
Is there a mysql function to store the results of a query to a table in a manner similar to a view, but where the data is simply cached instead of just aliasing the query? If there is a command like that, I could simply execute it in a cron job every now and then to keep the table semi-up-to-date. Or is it something I would need to create using PHP?