Need help with group_concat query

[Silx]

Before I go into it, let me state that I've received terrific assistance from this site before.. and it's now the first place I come to when researching query issues!

I'm working on a query that pulls from an OSCommerce database for products. Specifically, there are 4 tables I'm working with here..

products
products_to_categories
categories
categories_description
products_description

Ok, make that FIVE tables. lol

Let me start by pasting my current query:

SELECT
manufacturers.manufacturers_name AS manufacturer,
products.products_id AS id,
products_description.products_name AS title,
CONCAT('store/product_info.php?product_id=', products.products_id) as link,
products.products_upc AS upc,
products.products_id AS mpc,
products.products_model AS mpn,
products.products_quantity AS quantity,
products_description.products_description AS description,
products.products_price AS price,
products.products_tax_class_id,
CONCAT('store/images/', products.products_image) as products_image,
products_to_categories.categories_id
FROM (products,
products_description,
products_to_categories)
LEFT JOIN manufacturers ON ( manufacturers.manufacturers_id = products.manufacturers_id )
WHERE 
( products.products_id = products_description.products_id )
AND products.products_id = products_to_categories.products_id
AND products.products_status = 1 
AND products.products_quantity > 0
ORDER BY products_to_categories.categories_id DESC

Basically what I need to be able to do is add another column called "categories" that has the categories of the product concatenated together, separated by a comma.

The problem is, those category descriptions are in, well, categories_description.. which as of yet is not part of the query.

The table products_to_categories basically just consists of a couple fields. One with the product id, and one with the category id. So let's say product id #1 is in 5 categories. That product would be listed in products_to_categories 5 times. one for each category. What I want to do is take that list, look up the descriptions using the category id's supplied, and concat them into a field in the query. It hurts my brain to even think of how to do this, nevermind doing it in an efficient manner.

any help is appreciated!

thanks,
-s

 

[r937]

SELECT manufacturers.manufacturers_name AS manufacturer
     , products.products_id AS id
     , products_description.products_name AS title
     , CONCAT('store/product_info.php?product_id='
            , products.products_id) as link
     , products.products_upc AS upc
     , products.products_id AS mpc
     , products.products_model AS mpn
     , products.products_quantity AS quantity
     , products_description.products_description AS description
     , products.products_price AS price
     , products.products_tax_class_id
     , CONCAT('store/images/'
            , products.products_image) as products_image
     , [red]cat.categories[/red]
  FROM products
INNER
  JOIN products_description
    ON products_description.products_id = products.products_id
INNER
  JOIN [red]( SELECT products_to_categories.products_id
              , GROUP_CONCAT(categories.categories_description)
                  AS categories  
           FROM products_to_categories
         INNER
           JOIN categories
         GROUP
             BY products_to_categories.products_id ) AS cat[/red]
    ON cat.products_id = products.products_id
LEFT OUTER
  JOIN manufacturers 
    ON manufacturers.manufacturers_id = products.manufacturers_id 
 WHERE products.products_status = 1 
   AND products.products_quantity > 0

i left out your ORDER BY clause because it no longer makes sense (as the categories are now concatenated)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Silx]

I don't know how you guys do it. Machines!

There's one thing I forgot to mention. To get to the category descriptions, there are 2 tables.

categories
categories_description

categories.categories_id = categories_description.categories_id

categories_description.categories_name is where the name is located.

So I have to take the list of category id's from [products_to_categories], look up those id's in [categories], then pull the names from [categories_description] using the category id's pulled from [categories].

How confusing!

 

[r937]

so just add another INNER JOIN in the (red) subquery

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Silx]

I'm not sure what I added was right, as its taking eons to process.

use oscommerce
SELECT manufacturers.manufacturers_name AS manufacturer
     , products.products_id AS id
     , products_description.products_name AS title
     , CONCAT('store/product_info.php?product_id='
            , products.products_id) as link
     , products.products_upc AS upc
     , products.products_id AS mpc
     , products.products_model AS mpn
     , products.products_quantity AS quantity
     , products_description.products_description AS description
     , products.products_price AS price
     , products.products_tax_class_id
     , CONCAT('store/images/'
            , products.products_image) as products_image
     , cat.categories
  FROM products
INNER
  JOIN products_description
    ON products_description.products_id = products.products_id
INNER
  JOIN ( SELECT products_to_categories.products_id
              , GROUP_CONCAT(categories_description.categories_name)
                  AS categories  
           FROM products_to_categories
         INNER
           JOIN categories, categories_description
         GROUP
             BY products_to_categories.products_id ) AS cat
    ON cat.products_id = products.products_id
LEFT OUTER
  JOIN manufacturers
    ON manufacturers.manufacturers_id = products.manufacturers_id
 WHERE products.products_status = 1
   AND products.products_quantity > 0

 

[r937]

close, but not quite

you tried to add a comma and another table to an existing INNER JOIN, and it doesn't work that way -- in particular, you forgot the join condition, so it joined all rows to all rows!

what you want is...

...
INNER
  JOIN ( SELECT products_to_categories.products_id
              , GROUP_CONCAT(categories_description.categories_name)
                  AS categories
           FROM products_to_categories
         INNER
           JOIN categories
             [red]ON categories.category = products_to_categories.category_id[/red]
         INNER
           JOIN categories_description
             [red]ON categories_description.category_id = categories.category_id[/red]
         GROUP
             BY products_to_categories.products_id ) AS cat
    ON cat.products_id = products.products_id

please check the parts in red, i had to guess at your column names

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Silx]

Alright, Rudy! Now we're getting somewhere.

The names are being filled into that field. However, after further investigation into how the oscommerce system works, it seems that the categories are tree-based.

There is a column in categories called parent_id. If the parent_id is 0, there is no parent category. If it's any number, there is a parent category. Essentially I need to get that tree into the group_concat'd field. Is that possible? I have it done in php, but I'm essentially trying to bypass the php portion of it and have it done strictly by a query.

here is the current working query, with your helpful additions.

SELECT manufacturers.manufacturers_name AS manufacturer
     , products.products_id AS id
     , products_description.products_name AS title
     , CONCAT('store/product_info.php?product_id='
            , products.products_id) as link
     , products.products_upc AS upc
     , products.products_id AS mpc
     , products.products_model AS mpn
     , products.products_quantity AS quantity
     , products_description.products_description AS description
     , products.products_price AS price
     , CONCAT('store/images/'
            , products.products_image) as products_image
     , cat.categories
  FROM products
INNER
  JOIN products_description
    ON products_description.products_id = products.products_id
INNER
  JOIN ( SELECT products_to_categories.products_id
              , GROUP_CONCAT(categories_description.categories_name)
                  AS categories
           FROM products_to_categories
         INNER
           JOIN categories
             ON categories.categories_id = products_to_categories.categories_id
         INNER
           JOIN categories_description
             ON categories_description.categories_id = categories.categories_id
         GROUP
             BY products_to_categories.products_id ) AS cat
    ON cat.products_id = products.products_id
LEFT OUTER
  JOIN manufacturers
    ON manufacturers.manufacturers_id = products.manufacturers_id
 WHERE products.products_status = 1
   AND products.products_quantity > 0

 

[r937]

Essentially I need to get that tree into the group_concat'd field. Is that possible?

depends

what did you want? the parent? the parent and the parent's parent? the children? the children's children?

i understand "tree" but i don't understand "get that tree into the group_concat'd field"

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Silx]

Ah, good point. Tree is a poor choice of word.

The 'tree' is really just a linear line. A product is put into a category. That category may have a parent category, it may not.

The field should look something like this:

Computers > Peripherals > Printers

The item will only be in the printers category. The other 2 categories have to be retrieved using the parent id's.

 

[Silx]

Did my explanation make sense?

The product is at the furthest point in the linear tree, so in the case above, it would be in printers.

The only way to retrieve the names of the parent categories is to look at parent_id for each one as you work backwards until parent_id=0 for the category in question.

 

[r937]

thanks for that

your previous posts did not mention which way you wanted to traverse the tree

now it sounds like you want to go up, to parent, grandparent, etc.

with the adjacency model (which uses parent_id), you have to add another LEFT OUTER JOIN for every level up that you expect to go

there are other models, like the nested set model, which do not require these joins

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Silx]

Hmm. Maybe it would be easier just to create a temporary table that would consist of the trees in a static form?

Or is there a way to programmatically stop traversing once there are no more categories using sql?

 

[Silx]

Here is the php code for this:

// Function to get category with full path
function get_full_cat($cat_id, $CATEGORY_ARR) {

	$item_arr = $CATEGORY_ARR[$cat_id];
	$cat_name = $item_arr['categories_name'];
	
	while (sizeof($CATEGORY_ARR[$item_arr['parent_id']]) > 0 && is_array($CATEGORY_ARR[$item_arr['parent_id']]) ) {
		
		$cat_name = $CATEGORY_ARR[$item_arr['parent_id']]['categories_name'] . "  >  " . $cat_name;		
		$item_arr = $CATEGORY_ARR[$item_arr['parent_id']];
	}
	
	// Strip html from category name
	$cat_name = html_to_text($cat_name);
	
	return $cat_name;
}

 

[Silx]

Huh, can't edit post.

This is what creates the array in php:

// Get all categories
$categories_query = tep_db_query("SELECT * 
FROM (" . TABLE_CATEGORIES . ") 
LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " ON ( " . TABLE_CATEGORIES_DESCRIPTION . ".categories_id = " . TABLE_CATEGORIES . ".categories_id AND " . TABLE_CATEGORIES_DESCRIPTION . ".language_id = '" . $row_language_id['languages_id'] . "')"
);

while( $row_cat = tep_db_fetch_array( $categories_query ) ) {
	foreach ($row_cat as $i=>$v) {
		$CAT_ARR[$row_cat['categories_id']][$i] = $v;
	}
}