[MySQL] Probleem met group by en count

[Verwijderd]

Hoi,

ik heb een probleem met het maken van een query binnen MySQL die ik vanuit een PHP applicatie wil aanroepen.
Ik draai MySQL 4.0.17-standard.

Het gaat over de volgende 3 tabellen;

code:

PRODUCTS
id
name
description

PROD_CATS
prod_id
cat_id

CATEGORIES
id
name

Nu zou ik graag de volgende output generenen:
(Een fulltext search op name en description)

Totaal aantal hits,
Hits per categorie,
Resultaten gegroepeerd per categorie,
EN deze hits gesorteerd op het aantal hits per category.
(EN indien mogelijk qua performance, binnen de category, de producten gesorteerd op de SCORE van de fulltext query.... maar dit is geen noodzakelijke feature.. maar wel leuk )

Dus;

bv.
Gevonden resultaten: 10434

Category 34 (Aantal resultaten: 43)
product 4334
product 4324
product 1243

Category 143 (Aantal resultten: 34)
product 3242
product 3234

Category 643 (Aantal resulaten: 4)
product 7455
product 7235
product 9335


Hoe kan ik dit het beste aanpakken?
Is alles in 1 query mogelijk, en hoe zit het dan met de performance?

Ik heb op dit moment 2 queries; 1 voor het fulltext zoeken en 1 voor de count.
Echter, de resultaten per categorie en het sorteren hierop heb ik er nog niet in weten te verwerken.

Graag jullie hulp.

[ Voor 8% gewijzigd door Verwijderd op 12-02-2004 10:18 ]

[Verwijderd]

* bump *

niemand? Is het niet duidelijk omschreven?

[BrZ]

SELECT C.id, A.aantal, P.id, P.name FROM categories C, (SELECT C.id, count(P.id) AS aantal FROM categories C LEFT JOIN prod_cats PC ON C.id=PC.cat_id INNER JOIN products P ON PC.prod_id=P.id GROUP BY C.id) A LEFT JOIN prod_cats PC ON C.id=PC.cat_id INNER JOIN products P ON PC.prod_id = P.id

Volgens mij moet hij zo werken, ook in MySQL (4.0).
Maar ik vraag me af of dit nou sneller is dan per categorie los een count te doen
Daarnaast vraag ik me 1 ding af. Met je huidige db-structuur kan een product in meerdere categorieen horen. Is dit de bedoeling? Zo nee, dan kan de prod_cats tabel weg

[Gert]

code:

SELECT SUM(COUNT(PRODUCTS.id)), COUNT(PRODUCTS.id) 
FROM CATEGORIES
INNER JOIN ( PROD_CATS 
    INNER JOIN PRODUCTS
    ON PROD_CATS.prod_id = PRODUCTS.id
) ON CATEGORIES.id = PROD_CATS.cat_id 
GROUP BY cat_id 
ORDER BY 2 ASC;

Je kan niet altijd een referentie op naam maken naar een kolom naam bij dit soort queries, met 1,2,3,4,5 kan je dan alsnog bijvoorbeeld sorteren op de bijbeorende kolom.

Verder is je verhaal niet overduidelijk want in eens komt er een full text search voorbij fietsen die niet eerder uitgelegd wordt. Je zou ook je eigen brouwsels eens kunnen posten.

[Verwijderd]

BrZ schreef op 13 februari 2004 @ 17:58:
SELECT C.id, A.aantal, P.id, P.name FROM categories C, (SELECT C.id, count(P.id) AS aantal FROM categories C LEFT JOIN prod_cats PC ON C.id=PC.cat_id INNER JOIN products P ON PC.prod_id=P.id GROUP BY C.id) A LEFT JOIN prod_cats PC ON C.id=PC.cat_id INNER JOIN products P ON PC.prod_id = P.id

Volgens mij moet hij zo werken, ook in MySQL (4.0).
Maar ik vraag me af of dit nou sneller is dan per categorie los een count te doen
Daarnaast vraag ik me 1 ding af. Met je huidige db-structuur kan een product in meerdere categorieen horen. Is dit de bedoeling? Zo nee, dan kan de prod_cats tabel weg

Subselects zijn niet mogelijk in 4.0 ....

[Verwijderd]

Gert schreef op 13 februari 2004 @ 18:01:

code:

SELECT SUM(COUNT(PRODUCTS.id)), COUNT(PRODUCTS.id) 
FROM CATEGORIES
INNER JOIN ( PROD_CATS 
    INNER JOIN PRODUCTS
    ON PROD_CATS.prod_id = PRODUCTS.id
) ON CATEGORIES.id = PROD_CATS.cat_id 
GROUP BY cat_id 
ORDER BY 2 ASC;

Je kan niet altijd een referentie op naam maken naar een kolom naam bij dit soort queries, met 1,2,3,4,5 kan je dan alsnog bijvoorbeeld sorteren op de bijbeorende kolom.

Verder is je verhaal niet overduidelijk want in eens komt er een full text search voorbij fietsen die niet eerder uitgelegd wordt. Je zou ook je eigen brouwsels eens kunnen posten.

Mijn uitleg was de 'kern van het probleem'.
De tabel structuur was iets uitgebreider, maar hier kwam het op neer.
Wat betreft de fulltext search, op de product description wil ik een extra condition leggen gebruik makende van de fulltext index die op de kolom ligt.
Dit is nml waar de gebruiker op zoekt.
Maar nogmaals, dit is stap 2. Eerst wil ik deze query netjes werkende hebben.
Als ik jouw query run krijg ik een illegal use of GROUP function.
Check ff mijn MySQL versie; het is 4.0.17. Ik weet niet of het hier iets me te maken heeft?....