SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with creating a view query

    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:

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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by whitemank View Post
    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

    Quote Originally Posted by whitemank View Post
    ... 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

    Quote Originally Posted by whitemank View Post
    Am I better off creating a fixed table and then updating it every night with a cron job via PHP?
    not really, no

    Quote Originally Posted by whitemank View Post
    Or is there a way to do something similar within mysql?
    something similar to what?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, there is such a feature, but it's not "similar to a view"

    CREATE TABLE foo AS SELECT ...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •