Babydead Babydead - 4 months ago 11
MySQL Question

MYSQL get count of search results with group_concat

Ok, so, I've spent some hours working out this query with a buddy of mine. I've adopted a new 'hierarchical' structure which means either the 'child' (store) fills out the data and it grabs that, or it doesn't, so the parent (chain, creator of said store) has the default information which should then be grabbed.
Now, I finally got this search function to work with this fact. It's technically working perfectly fine (Although I guess it's not very efficiƫnt, but SQL really isn't my strong suit)

Now, the issue I take with this is, that before this hierarchical structure, I'd 'sum' the results up into how many matches it had made. However, because I am now doing a whole lot of 'either or' situations, that part seems to be getting confused. I've tried many things (

DISTINCT COUNT
,
DISTINCT SUM
,
COUNT
,
SUM
,
THEN 1
,
THEN +1
,
THEN -1
, etc.)

So... yeah... I'm really stuck on this one. The thing is, I need the counter in order to sort on the relevance.

SELECT stores.ID, store_info.display_name, store_info.address, store_info.phone,
IFNULL(GROUP_CONCAT(DISTINCT smallCheese.display_name ORDER BY smallCheese.name),
GROUP_CONCAT(DISTINCT bigCheese.display_name ORDER BY bigCheese.name)
) AS brands,
IFNULL(GROUP_CONCAT( DISTINCT
CASE WHEN smallCheese.ID IN (3,5,8,11,12,13,14,16,17)
THEN smallCheese.display_name
ELSE NULL
END),
(GROUP_CONCAT(DISTINCT
CASE WHEN bigCheese.ID IN (3,5,8,11,12,13,14,16,17)
THEN bigCheese.display_name
ELSE NULL
END))
) AS available_brands,
COUNT(DISTINCT CASE WHEN smallCheese.ID OR bigCheese.ID IN (3,5,8,11,12,13,14,16,17)
THEN 1
ELSE 0
END ) AS available_brands_count
FROM stores
LEFT JOIN store_info ON (stores.ID = store_info.storeID)
LEFT JOIN store_brands ON (stores.ID = store_brands.store)
LEFT JOIN chain_brands ON stores.chainID = chain_brands.chain
LEFT JOIN brands AS smallCheese ON store_brands.brand = smallCheese.ID
LEFT JOIN brands AS bigCheese ON chain_brands.brand = bigCheese.ID
WHERE stores.city = 1
GROUP BY store_info.storeID
ORDER BY `available_brands_count` DESC, store_info.display_name


Here's an SQL fiddle: http://sqlfiddle.com/#!9/cfe307/1/0

Answer

I think this might work:

IF (MAX(CASE WHEN smallCheese.ID IN (3,5,8,11,12,13,14,16,17)
             THEN smallCheese.ID
             ELSE NULL
        END) IS NOT NULL,
    COUNT(DISTINCT
        CASE WHEN smallCheese.ID IN (3,5,8,11,12,13,14,16,17)
            THEN smallCheese.ID
            ELSE NULL
        END),
    COUNT(DISTINCT
        CASE WHEN bigCheese.ID IN (3,5,8,11,12,13,14,16,17)
            THEN bigCheese.ID
            ELSE NULL
        END)) AS available_brands_count

You can't just use IFNULL(COUNT(...), COUNT(...)) because COUNT() returns 0 if there are no matches. I use MAX() to aggregate all the IDs, and if no IDs are found this will return NULL.

DEMO

Comments