[MySQL] Selecteer waar overal aanwezig

[royduin]

Beste tweakers,

zit al enige tijd te rommelen maar nog steeds geen resultaat. Al meerdere forums gehad maar helaas ook daar geen oplossingen. Nu maar eens op een echte website rond vragen.

Om te beginnen een voorbeeldje van de database (met zo'n 80.000 items):

code:

id, product_id, cat, name, value
--------------------------------
1,1,Algemeen,Processor,2 Ghz
2,1,Algemeen,Geheugen,4 GB

3,2,Algemeen,Processor,3 Ghz
4,2,Algemeen,Geheugen,4 GB

5,3,Beeldscherm,Inch,22"
6,3,Beeldscherm,Kleur,Zwart
7,3,Algemeen,Geheugen,3 GB
8,3,Algemeen,Processor,3 Ghz

Dan de query die ik nu gebruik om allen te selecteren:

code:

SELECT DISTINCT cat, name, value
FROM producten_specs 
WHERE product_id IN (1,2,3) 
ORDER BY cat,name,ABS(value) ASC

Deze query moet wat omgebouwd worden zodat de volgende id's geselecteerd worden: 1,2,3,4,7,8

Omdat: de cat, algemeen en name, processor bij alle producten aanwezig zijn. ID 5,6 zijn alleen bij product 3 aanwezig, deze hoeven dus niet geselecteerd te worden.

=algemeen en =processor is niet wat ik zoek omdat er velen verschillende cat's, name's en value's zijn.

Dus, de items (cat en name) die bij alle producten (product_id) voorkomen moeten geselecteerd worden.

Wie helpt mij uit de brand?

Alvast bedankt!

Gr. Roy

[CodeCaster]

Kun je iets duidelijker vertellen wat je wil en aan welke voorwaarden je resultaten moeten voldoen?

Van wat jij nu vertelt kan ik enkel dit maken:

SQL:

SELECT DISTINCT 
  cat, name, value
FROM 
  producten_specs 
WHERE 
  product_id IN (1,2,3) 
AND
  cat <> 'Beeldscherm'
ORDER BY 
  cat,name, ABS(value) ASC

En werkt dat ABS(value)?

[dik_voormekaar]

royduin schreef op vrijdag 25 februari 2011 @ 13:53:
... Al meerdere flora gehad maar helaas ook daar geen oplossingen. ...

Leuk! We krijgen een bloemetje als er geen oplossing voor een probleem geboden wordt.

[zwippie]

Los van je query probleem:

Best een vage indeling eigenlijk. De name => value combinaties die je maakt zijn niet altijd zinnig, de gekozen categorieën misschien ook niet echt.

Waarom maak je niet een categorie 'Processor' met eigenschap 'Snelheid (MHz)' met waarde '2000'. Dan kun je bijvoorbeeld ook sorteren op CPU snelheid. Een categorie 'Algemeen' zegt niet zoveel als het eigenlijk over de CPU gaat.

[royduin]

Ik hoop dat het duidelijker is met een nieuw voorbeeld:

Het is een database met heel veel product informatie. Hier staat alles zoals in het bovenstaande bericht vermeld. Die informatie wordt per product netjes weergegeven. Nu ben ik bezig om een filter te maken. Zoals het nu draait krijg ik gigantisch veel filters omdat bij het ene product bijv. processor, familie, snelheid staat en bij het andere product alleen processor.
Nu moet alles geselecteerd worden waar een eigenschap aanwezig is bij alle producten. In dit geval zou dus alleen processor geselecteerd moeten worden omdat familie en snelheid niet aanwezig zijn bij de 2e.

Wat er geselecteerd moet worden is dus van product_id 1,2,3 waar dezelfde cat en name aanwezig zijn bij deze 3 producten.

Hopende dat het zo duidelijker uitgelegd is.

@CodeCaster: Die ABS doet wel zijn werk. Weet niet of ik het juiste roep maar 10 kwam geloof ik boven 1 te staan, met ABS erbij staat het wel goed.

@zippie: Het is maar een voorbeeldje. De informatie komt bij icecat vandaan hier is het netjes gecategoriseert.

[KopjeThee]

Ok, ik begrijp dat je de regels wilt hebben die, wat betreft cat en name, gemeenschappelijk zijn tussen de product_id's 1, 2 en 3.

Volgens mij krijg je dan een rare query zoals deze:

code:

select
  *
from producten_specs
where
  product_id in (1, 2, 3) and
  concat(cat, name) in
  (
    select concat(p1.cat, p1.name) from 
      (select * from producten_specs where product_id = 1) as p1
    inner join
      (select * from producten_specs where product_id = 2) as p2
    on p1.cat = p2.cat and p1.name = p2.name
    inner join
      (select * from producten_specs where product_id = 3) as p3
    on p1.cat = p3.cat and p1.name = p3.name 
  )

Die joins bepalen wat de gemeenschappelijke cat/name combinaties zijn tussen de producten.

Maar goed ik ben niet erg goed in mysql. Misschien kan het veel makkelijker.

[royduin]

Heb hem niet getest omdat de product_id's genoemd worden. Het zal waarschijnlijk werken maar er zijn meer dan 3 producten. Deze staan in $product_ids:

code:

WHERE product_id IN (".mysql_real_escape_string($product_ids).")

Als voorbeeld gaf ik 3 producten.

Moet een wat dynamische query worden. Wellicht is er wat PHP bij nodig?

Ik hoor graag weer!

[ Voor 4% gewijzigd door royduin op 25-02-2011 20:34 . Reden: Toevoeging ]

[Skinny]

Ik heb ff geen mysql bij de hand, maar in MSSQL werkt het volgende volgens mij.

1. Bepaal aantal unieke producten
2. Bepaal aantal producten per attribute
3. Join 1+2
4. Selecteer opnieuw alle records die voldoen aan het resultaat van 3.

Levert bij mij, 1,2,3,4,7,8 op

SQL:

SELECT Results.* FROM Specs Results
INNER JOIN 
(
    SELECT Cat,Name  FROM
        (SELECT COUNT(DISTINCT ProductId) NumberOfProducts FROM Specs) TotalProducts
        INNER JOIN 
        (
            SELECT Cat,Name, COUNT(ProductId) AS NumberOfProducts
            FROM Specs
            GROUP BY Cat, Name
        ) GlobalSpecs ON (GlobalSpecs.NumberOfProducts = TotalProducts.NumberOfProducts)
) ResultSpecs ON (ResultSpecs.Cat = Results.Cat AND ResultSpecs.Name = Results.Name)

disclaimer: voor erg grote tabellen gaat dit zonder goede indexen waarschijnlijk issues op leveren, maar het biedt een houvast

[ Voor 8% gewijzigd door Skinny op 25-02-2011 20:50 ]

[royduin]

Bedankt voor je reactie maar krijg het helaas niet aan de praat. Resultaat is elke keer 0.
Heb Specs vervangen door producten_specs, ProductId door product_id en Cat, Name zonder hoofdletters.
Verder geprobeerd met onder andere WHERE product_id erin en ORDER BY maar tevergeefs geen resultaat.

Iemand die mij nog een klein setje de juiste richting op wil geven?

[Voutloos]

Ik wil je best helpen, en ik vermoed dat de uiteindelijke query geen rocket science gaat zijn, maar voorlopig snap ik eerlijk gezegd geen hol van je vraag + voorbeeld. Ik denk dat je dus beter iets meer moet verhelderen wat je wilt, en vooral even moet wachten met in queries denken / random queries gokken.

[royduin]

Ik ga het duidelijker proberen uit te leggen:

Ik ben bezig met een webshop met velen producten welke veel mogelijkheden moet krijgen. Zo worden momenteel gegevens van leveranciers geïmporteerd en wordt product informatie bij Icecat vandaan gehaald. Al deze informatie wordt opgeslagen in de MySQL database.

Deze gegevens worden weergegeven op het moment dat een product aangeroepen wordt. Dit gaat prima. Alle producten staan uiteraard ook in een categorie. Wanneer deze producten in een categorie weergegeven worden moet er ook op producten uit die categorie gefilterd kunnen worden. Als voorbeeld op, merk, prijs, processor, geheugen, enz.

Omdat we van Icecat heel veel informatie krijgen waarbij bijv. bij Toshiba ook wat voor snelheden een dvdspeler heeft, krijgen we heel veel filters. Te veel voor de gebruiker. Naast dat levert bijv. Asus niet de snelheden van een dvdspeler (en staan dus ook niet in de database). Met het gevolg dat wanneer er op snelheid van een dvdspeler gefilterd wordt komen hier enkel Toshiba producten voor.

Deze filters wil ik nu beperken door van alle merken de overeenkomende specificaties te selecteren. Zowel Toshiba als Asus geven de processor snelheid mee. De snelheid van de dvdspeler komt te vervallen omdat enkel Toshiba dit aangeeft en in de database staat. Snelheid van een dvd speler zijn bij Asus helemaal niet in de database aanwezig.

Dit verhaal met bovenstaande informatie en voorbeelden denk ik dat het een stuk duidelijker is.

Ik verneem graag weer!

[ Voor 6% gewijzigd door royduin op 01-03-2011 21:08 ]

[chronozphere]

Ik volg nu een vak databases dus ik vond het een interessante uitdaging om een query voor je te verzinnen.

[theoretisch geblaat]

Het probleem van SQL is dat er geen "universele quantificatie" ondersteund wordt, d.w.z dat je in SQL geen voorwaarde kunt formuleren in de zin van: "voor alle X geldt Y". In jou geval zou dit de voorwaarde: "voor alle product_id's geldt dat ze gerelateerd zijn aan een bepaalde (cat/name)" zijn. Wat je wel kunt doen is een "existentiele quantificatie" gebruiken. Dat is: "er bestaat een X waarvoor geldt Y". Als je dit gebruikt, i.c.m een dubbele negatie heb je logisch gezien pricies hetzelfde (Er bestaat geen X waarvoor niet geldt Y). Dat is waarschijnlijk een belangrijk deel van je oplossing.

[/theoretisch geblaat]

Wat je dus eerst moet doen is een lijst kandidaat-oplossingen verkrijgen en vervolgens elke row op deze lijst toetsen aan de onderstaande eis. Alle cat/name pairs die je vervolgens overhoud zijn je resultaten.
De kandidaat-oplossingen in jou geval zijn alle cat/name pairs die voorkomen voor de gegeven lijst product_id's waarin je geinteresseerd bent.

Die voorwaarde zou je als volgt kunnen formuleren:

Een cat/name pair mag in mijn resultaat zitten als er geen product_id bestaat, waarmee dat cat/name pair niet geassocieerd is.

Heb een query voor je in elkaar geflanst.

Tabel C zijn hier dus de candidaat oplossingen.

code:

SELECT  C.cat, C.name FROM 

   (SELECT DISTINCT cat, name 
    FROM product_specs 
    WHERE  product_id IN [<jou product ids>]
     GROUP BY cat, name) C

WHERE NOT EXISTS (
        
           SELECT * FROM product_specs PS
           WHERE PS.product_id IN  [<jou product ids>]
           AND (NOT PS.cat = C.cat OR NOT PS.name = C.name)

)

Dit is allemaal niet getest. Heb geen idee hoe dit performance wise uitpakt.
Hopelijk heb je toch iets aan deze post.

Succes!

Edit: query nogmaals verbeterd.:)

Edit2: volgens mij bevat deze query een denkfout. Ik kom zo even niet tot een oplossing.

[ Voor 14% gewijzigd door chronozphere op 27-02-2011 22:51 ]

[Voutloos]

royduin schreef op zondag 27 februari 2011 @ 22:26:
Deze filters wil ik nu beperken door van alle merken de overeenkomende specificaties te selecteren. Zowel Toshiba als Asus geven de processor snelheid mee. De snelheid van de dvdspeler komt te vervallen omdat enkel Toshiba dit aangeeft en in de database staat.

Goed, je wilt dus categorieën selecteren, en rows weglaten indien een categorie niet voor elke product ingevuld is. Met andere woorden, je wilt de rows waarbij een categorie voor alle producten bekend is, dus:
where (...subquery welke het aantal producten met die categorie telt) = totaal aantal producten.

edit:

Skinny zegt ongeveer hetzelfde zie ik nu, maar doet het op een iets andere manier. Welke manier sneller is is lastig te zeggen (blinde gok : Skinny's aanpak), dat zal je zelf moeten meten. Mijn query is misschien iets eenvoudiger te doorgronden.

[ Voor 16% gewijzigd door Voutloos op 27-02-2011 23:08 ]

[Cousin Boneless]

Leuk puzzeltje! Dit geeft iig de goede antwoorden. Moet dus nog wel expliciet tellen hoeveel items er in de komma-gescheiden string worden gezet (having cnt = 3)

SQL:

create table producten_specs (
id int primary key,
product_id int,
cat varchar(50),
`name` varchar(50),
`value` varchar(50)
);

insert into producten_specs (id,product_id,cat,`name`,`value`)
values
(1,1,'Algemeen','Processor','2 Ghz'),
(2,1,'Algemeen','Geheugen','4 GB'),
(3,2,'Algemeen','Processor','3 Ghz'),
(4,2,'Algemeen','Geheugen','4 GB'),
(5,3,'Beeldscherm','Inch','22"'),
(6,3,'Beeldscherm','Kleur','Zwart'),
(7,3,'Algemeen','Geheugen','3 GB'),
(8,3,'Algemeen','Processor','3 Ghz')

SQL:

select p.id
from producten_specs p
inner join (
  -- de categorieen die bij alle opgegeven producten voorkomen
  select cat, `name`, count(*) as cnt
  from producten_specs
  where product_id in (1,2,3)
  group by cat, `name`
  having cnt = 3
) c on p.cat = c.cat and p.`name` = c.`name`
where p.product_id in (1,2,3)

[ Voor 5% gewijzigd door Cousin Boneless op 01-03-2011 03:14 . Reden: Nalezende begrijp ik dat je alleen de eigenschappen van de producten met product_id's (1,2,3) wil selecteren.. ]

[royduin]

Super dit! Heb hem werkend:

PHP:

                <?php
                $query = mysql_query("
                select distinct p.cat, p.name, p.value 
                from producten_specs p 
                inner join ( 
                  select cat, `name`, count(*) as cnt 
                  from producten_specs 
                  where product_id in (".mysql_real_escape_string($product_ids).") 
                  group by cat, `name` 
                  having cnt = ".mysql_real_escape_string($product_ids_aantal)." 
                ) c on p.cat = c.cat and p.`name` = c.`name` 
                where p.product_id in (".mysql_real_escape_string($product_ids).") 
                ORDER BY cat,name,ABS(value) ASC
                ");
                ?>

Nu verder experimenteren tot alles vlekkeloos werkt. Ik wil een ieder hartelijk danken!

EDIT: Hebben we hier nog een knopje opgelost o.i.d.?

[ Voor 4% gewijzigd door royduin op 01-03-2011 20:29 ]