Page 1 of 1

Tag cloud logic [MySQL]

Posted: Wed Feb 27, 2008 8:29 am
by stakes
Hello developers.

I'm stuck in writing this query for a "Tag Cloud" for my page. Basicly what i want to do is
to select the name and the 10 most "popular" "head categories" based on how many "objects"
there are in the related "sub category".

Table structure:

HC (Head category)
-------------------
hc_id
hc_name

SC (Sub category)
-------------------
hc_id <-- relates to hc_id in HC
sc_id
sc_name

OB (Objects)
-------------------
sc_id <-- relates to sc_id in SC
ob_id
ob_name

I wrote on something that i thought might be a good start... but in the end it all gets to messy for me to figure it out.

Code: Select all

 
SELECT hc_hc.id, hc.hc_name, COUNT(ob.ob_id) AS quantity FROM hc, uc LEFT JOIN ob ON uc.uc_id = ob.uc_id WHERE
uk.hc_id = hc.hc_id ORDER BY quantity DESC LIMIT 10
 
:crazy:

I think im lost.!

Could anyone point me in the right direction?

Thanks in advance.

Re: Tag cloud logic [MySQL]

Posted: Wed Feb 27, 2008 10:54 am
by stakes
Ok i got as far as this:

Code: Select all

SELECT object_subcategory.subCategory_name AS name, 
object_subcategory.subCategory_headCategory_id AS head_id, 
object_item.item_subCategory_id AS tag, 
COUNT( object_item.item_subCategory_id ) AS quantity
FROM object_item
LEFT JOIN object_subcategory ON object_subcategory.subCategory_id = object_item.item_subCategory_id
GROUP BY item_subCategory_id
ORDER BY quantity DESC
LIMIT 10
which gives me.

Code: Select all

 
name    tag     quantity   head_id
---------------------------------
HTML     5          4              2
CSS       4          2              2
PHP       6          9              1
 
where name represents the sub category name and tag is subCategory_id.

Now if i could just combine a count for example HTML CSS.. which is under the category CLIENT SIDE, so that result
would be something like..

Code: Select all

head_id  quantity
----------------------
2           6
1           9

Re: Tag cloud logic [MySQL]

Posted: Thu Feb 28, 2008 8:17 am
by stakes
Ok i got help elsewhere and solved it. I'm posting the solution in case anyone is interested.

Code: Select all

SELECT headcategory_name AS name, subCategory_headCategory_id AS hid, item_subCategory_id AS tag, count( * ) AS quantity
FROM object_item
LEFT JOIN object_subcategory ON object_subcategory.subcategory_id = object_item.item_subcategory_id
LEFT JOIN object_headcategory ON object_headcategory.headcategory_id = object_subcategory.subcategory_headcategory_id
GROUP BY object_headcategory.headcategory_id
ORDER BY quantity DESC , name
LIMIT 10