SitePoint Sponsor |
|
User Tag List
Results 1 to 13 of 13
-
Mar 1, 2005, 12:38 #1
- Join Date
- Sep 2003
- Location
- Wales
- Posts
- 1,181
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Possibly need to construct a very complex query
Hi all,
I am producing a sales analyser function for a client so that he can monitor online sales. My client is able to view sales by highest quantity sold per month, and highest revenue per month.
By what I also want my client to do, is to be able to view the sales of Items within a specified Category. The catch is that in my database I have a hierachy of categories (around 4). So I have a table called categories, where each categories has a parent id which links into another category in the same table. The highest level category is 'Root'. Following that I have 4 main categories, and then they have sub-categories, and then they also have sub-categories. I then have a products table where each product will contain a foreign key value to the lowest level category it resides it (in PHP I can use a recursive function to get to the parent category).
So, if my client wishes to view all sales for a given month and for a specified top level category, how exactly to I implement a query that uses some form of recursion to get to that category that has been selected?
Thanks
TrystLast edited by Tryst; Mar 1, 2005 at 13:18.
-
Mar 2, 2005, 05:26 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
you don't need a category called root, the 4 main categories should each have a parent of null
how many levels of actual categories are there, and do all 4 main categories go down the exact same number of levels before getting to a level with products, or can products be tied to (sub)categories at any level (which is the more general and flexible design)?
-
Mar 2, 2005, 07:35 #3
- Join Date
- Sep 2003
- Location
- Wales
- Posts
- 1,181
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hi Rudy,
There are 3 levels of categories (not including root) meaning that alll products go down the same level of categories. for example...
Fine Art Materials->Accessories->Adhesvies & Tapes
Would be the 3 categories where 'Fine Art Materials' is the Top most, and 'Adhesvies & Tapes' is lowest category (products would be related to this category - and all other categories at the same level).
At this moment in time products cannot be tiered to sub categories.
(my local version of MySQL is 3.23, while my hosting company has version 4.*)
Thanks
Tryst
-
Mar 2, 2005, 08:45 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
are there any products tied to the first or second levels? what if a particular 2nd level subcategory doesn't have any third level subsubcategories?
-
Mar 2, 2005, 10:44 #5
- Join Date
- Sep 2003
- Location
- Wales
- Posts
- 1,181
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
No, its structered so that there is a fixed level of catgories (3), and products CAN ONLY be assigned to a third level category.
Thanks
Tryst
-
Mar 2, 2005, 11:00 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
assuming the tables and columns are called --
categories ( id, name, parentid )
products ( id, name, catid, salesdate, salesamt )
Code:select sum(products.salesamt) as totals_sales from categories as top inner join categories as sub on top.id = sub.parentid inner join categories as subsub on sub.id = subsub.parentid left outer join products on subsub.id = products.catid where top.id = 937 and products.salesdate >= '2005-02-01' and products.salesdate < '2005-03-01'
-
Mar 2, 2005, 12:03 #7
- Join Date
- Sep 2003
- Location
- Wales
- Posts
- 1,181
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Wow! WWorking it out in my head, that should work.
I'll have to wait until I get home though, before I can try it out.
Top stuff, r937!
Will keep you posted.
Tryst
-
Mar 2, 2005, 16:09 #8
- Join Date
- Sep 2003
- Location
- Wales
- Posts
- 1,181
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Ouch! I ran this query (Albiet, a bit modified) and it kill my computer, twice!
My CPU was on 100% until I had to reboot my laptop.
The modified query i ran, is as follows:
Code:SELECT itm.prod_code, prd.name, SUM(itm.quantity * prd.price) AS Total FROM categories AS top INNER JOIN categories AS sub ON (top.category_id = sub.parent_id) INNER JOIN categories AS subsub (sub.category_id = subsub.parent_id) LEFT OUTER JOIN products AS prd ON (subsub.category_id = prd.category_id) INNER JOIN order_items AS itm ON (prd.code = itm.prod_code) INNER JOIN orders AS ord ON (itm.order_id = ord.order_id) WHERE (top.category_id = 2) AND (MONTH(ord.date) = '12') AND (DATE(ord.date) = '2004') GROUP BY prod_code ORDER BY Total DESC LIMIT 20
Tryst
-
Mar 2, 2005, 16:32 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
change the LEFT OUTER JOIN to INNER JOIN (i didn't realize you wanted to list individual items)
move the two AND conditions for order fields to their appropriate ON clause, just in front of the WHERE clause, and change them so that they test a range of values (applying a function to a field means an index cannot be used on it)
make sure all the columns mentioned in any of the ON clauses, other than those that are already declared as PRIMARY KEYS, have an index on them
Code:SELECT itm.prod_code , prd.name , SUM(itm.quantity * prd.price) AS Total FROM categories AS top INNER JOIN categories AS sub ON top.category_id = sub.parent_id INNER JOIN categories AS subsub ON sub.category_id = subsub.parent_id INNER JOIN products AS prd ON subsub.category_id = prd.category_id INNER JOIN order_items AS itm ON prd.code = itm.prod_code INNER JOIN orders AS ord ON itm.order_id = ord.order_id AND ord.date >= '2004-12-01' AND ord.date < '2005-01-01' WHERE top.category_id = 2 GROUP BY itm.prod_code , prd.name ORDER BY Total DESC LIMIT 20
-
Mar 2, 2005, 17:06 #10
- Join Date
- Sep 2003
- Location
- Wales
- Posts
- 1,181
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Awesome!! That has done the trick
Thanks, Rudy!
(I don't think any of the Foriegn keys in my tables have indexes on them, can I just apply an INDEX to these Foriegn key columns without affecting the data in the db?)
Tryst
-
Mar 2, 2005, 18:11 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
yes, just use the ALTER TABLE ADD INDEX syntax
you realize that indexes will have to be updated whenever you insert/delete rows, so those operations will be a bit slower (if at all noticeable) but selects will be much faster (noticeably so)
-
Mar 3, 2005, 07:02 #12
- Join Date
- Sep 2003
- Location
- Wales
- Posts
- 1,181
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hi Rudy,
just out of curosity, why was my initial Query in an endless loop, and was your initial query meant to determine the total number of sales for a given category?
Thanks
Tryst
-
Mar 3, 2005, 23:54 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
sorry, out all day
Originally Posted by Tryst
Bookmarks