Possibly need to construct a very complex query

[Tryst]

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

Tryst

[r937]

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)?

[Tryst]

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

[r937]

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?

[Tryst]

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

[r937]

assuming the tables and columns are called --

categories ( id, name, parentid )
products ( id, name, catid, salesdate, salesamt )

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'

[Tryst]

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

[Tryst]

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:

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

To me, I can't see anything wrong. I 'think' all the joins are correct. But for some reason this is killing my computer

Tryst

[r937]

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

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

[Tryst]

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

[r937]

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)

[Tryst]

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

[r937]

sorry, out all day

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?

i'm sure it would have ended eventually, and yes, guess which category -- the one with id = 937