Need help with creating a view query

[whitemank]

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...

[r937]

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

[whitemank]

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?

Thanks again.

[r937]

Does it get executed each time you run a query against the view?

yes

you can think of a view as a simplified "alias" for a complex query

... so it just looks at the products within a given product category rather than looking across all products.

yes, although then your query would be a bit more complicated

Am I better off creating a fixed table and then updating it every night with a cron job via PHP?

not really, no

Or is there a way to do something similar within mysql?

something similar to what?

[whitemank]

something similar to what?

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.

Thanks again.

[r937]

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

[whitemank]

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?

Thanks.

[r937]

yes, there is such a feature, but it's not "similar to a view"

CREATE TABLE foo AS SELECT ...