[MySQL] Alleen als alles matcht

[BikkelZ]

Ik heb de resultaten nodig van een filter voor de antwoorden die mensen gegeven hebben. Dat houdt in dat als iemand aan alle antwoorden (reacties) van dat filter voldoet (bijvoorbeeld bij vraag 1 'ja' gezegd en bij vraag 3 'dat weet ik niet') dat ik dan zijn record wil hebben, en anders niet. Wat die persoon bij vraag 2 ingevuld heeft dat interesseert me in dit voorbeeld dan weer niet, het gaat er puur om of iemand een bepaald antwoord gegeven heeft.

Deze filters kunnen mensen opslaan (en nu niet ter zake doend, maar mensen kunnen achteraf ook nog de batches terug zien omdat ze direct weer opgeslagen worden), zodat ze ze vaker kunnen hergebruiken (en dat is zeker handig als je wilt bijhouden wie er al uitgefilterd zijn met die filter en dus niet nog een keer terug moet komen).

Nu heb ik de volgende SQL:

SQL:

SELECT DISTINCT(k.id)
FROM kontakt k
    JOIN reactie r ON k.id = r.kontakt_id
WHERE r.antwoord_id IN (
    SELECT fa.antwoord_id
    FROM filter_antwoord fa
    WHERE fa.filter_id = 1
)

Natuurlijk levert dit iedereen op die op 1 van de n vragen het juiste antwoord heeft gegeven. Maar ik kan ook niet met een dubbele ontkenning gaan werken, want mensen die ik wel wil hebben, hebben ook antwoorden gegeven die niet in het filter zitten (zeg maar het antwoord op vraag 2, om het zelfde voorbeeld weer op te halen).

Kort gezegd:

Geef mij alle kontakten, die aan alle gewenste reacties (antwoorden) van het filter voldoen

[P.O. Box]

je kunt, als een bepaald antwoord gegeven is, in je query een item op 0 of 1 zetten... en dan alle kontakten selecteren waarbij de SUM van die 0-en en 1-en gelijk moet zijn aan het aantal filters dat je hebt....

[BikkelZ]

Edwardvb schreef op woensdag 30 mei 2007 @ 10:36:
je kunt, als een bepaald antwoord gegeven is, in je query een item op 0 of 1 zetten... en dan alle kontakten selecteren waarbij de SUM van die 0-en en 1-en gelijk moet zijn aan het aantal filters dat je hebt....

Ik hoor het al, ik zal de

PHP:

substr($username, 0, 6)

in mijn naam waar moeten gaan maken

Ik zit te twijfelen:

- Stored procedure
- Single query maar dan wel een lastige

Ik meld me over een half uurtje wel terug

[P.O. Box]

ik denk dat het wel in een single query kan... een lastige.... maar stored procedure moet zeker lukken...

SQL:

SELECT CONCAT(LEFT(username, 6), ' ', RIGHT(username, 1), 'e !!!')

[BikkelZ]

Ik denk dat het zoiets wordt als:

SQL:

HAVING COUNT(SELECT antwoord_id FROM filter_antwoord ....blablablaenzenz ) = COUNT(SELECT antwoord_id FROM reactie WHERE antwoord_id IN (SELECT antwoord_id FROM filter_antwoord blablaenzenz ))

Maar goed, ik moet er nu vandoor, kan er morgen pas weer verder aan werken.

[BikkelZ]

OK ik ben er uit hoe ik het morgen ga doen:

Het kaasschaafmodel

1. Ik maak een cursor op alle antwoorden waarop ik wil filteren
2. Ik pak de eerste waarde die de cursor tegen komt
3. Haal alle mensen op die dat antwoord gegeven hebben
4. Sla die mensen op in de database met selecties (dat onthou ik sowieso voor iedere filteractie meestal)
5. Itereer de rest van mijn stapeltje van de cursor
6. En delete iedereen die in de resultaatdatabase staat en niet het desbetreffende antwoord gegeven heeft
7. Zou ik echt persé niet willen dat de resultaten bewaard worden (een optionele feature die standaard uit staat, uit mutsproofheid) dan gooi ik alles weg nadat ik het heb laten zien

Een paar dingen die meer filosofisch dan praktisch van aard zijn:

• Stel dat ik de resultaten niet op zou willen slaan. Zou ik dan de rijen tijdelijk in het geheugen kunnen houden in een variabele en dan door middel van zoiets als een INTERSECT kunnen kaasschaven?
• Het via een simpele query doen lukt me niet. Hoe dachten de mensen die dachten het te kunnen oplossen het op te lossen?

[_js_]

select id
from kontakt
where id in (select kontakt_id from reactie where VOORWAARDE_1)
and id in (select kontakt_id from reactie where VOORWAARDE_2)
and id in (select kontakt_id from reactie where VOORWAARDE_3)

[Gwaihir]

Hoe ziet dit tabel reactie er precies uit? Waarom gebruik je de tabel kontakt als je er alleen maar het id uithaalt wat ook al in reactie zit?

[BikkelZ]

_js_ schreef op woensdag 30 mei 2007 @ 23:56:
select id
from kontakt
where id in (select kontakt_id from reactie where VOORWAARDE_1)
and id in (select kontakt_id from reactie where VOORWAARDE_2)
and id in (select kontakt_id from reactie where VOORWAARDE_3)

Ja, maar jij weet blijkbaar van te voren al hoeveel het er zijn door een 1e query te gebruiken en dan dit met een script of programma op te bouwen. Dus dan zit ik weer met een lap code in mijn scherm om data op te halen in plaats van een query die het gewoon doet voor me.

Birdie schreef op donderdag 31 mei 2007 @ 00:06:
Hoe ziet dit tabel reactie er precies uit? Waarom gebruik je de tabel kontakt als je er alleen maar het id uithaalt wat ook al in reactie zit?

Omdat het doel is dat ik de naam, adres, etc. gegevens van die kontakten op mijn scherm ga laten zien.

[P.O. Box]

kun je even laten zien hoe je filter_antwoord tabel eruit ziet?
en je andere tabellen dan ook gelijk even?

[BikkelZ]

OK, ik heb de volgende FUNCTION gebouwd die in ieder geval netjes de nieuwe ID van de filterbatch terug geeft (MySQL developer studio is wel heeeeel handig hier voor merk ik), de rest moet ik nog even goed doortesten:

SQL:

FUNCTION telemarketing.function1(ev_filter_id INT)
  RETURNS INT
BEGIN
  -- losse vars declareren
  DECLARE v_aantal_filters_gevonden INT;
  DECLARE v_antwoord_id INT;
  DECLARE v_filterbatch_id INT;
  DECLARE v_kontakt_id_initieel INT;
  DECLARE v_notfound BOOLEAN;

  -- cursors declareren
  DECLARE c_filter_antwoord_ids CURSOR FOR SELECT antwoord_id FROM filter_antwoord WHERE filter_id = ev_filter_id;
  DECLARE c_kontakt_ids_initieel CURSOR FOR SELECT kontakt_id FROM reactie WHERE antwoord_id = v_antwoord_id;

  -- handler declareren
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_notfound := TRUE;
  -- DECLARE EXIT HANDLER FOR SQLEXCEPTION csr_emp;

  
  SELECT COUNT(*) INTO v_aantal_filters_gevonden FROM filter WHERE id = ev_filter_id;

  -- kijken of het filter bestaat
  IF v_aantal_filters_gevonden = 1 THEN
  
    -- eerst filterbatch aanmaken voor deze filterbatch
    INSERT INTO filterbatch VALUES ('', ev_filter_id, NOW());
    SELECT MAX(id) INTO v_filterbatch_id FROM filterbatch WHERE filter_id = ev_filter_id;
  
    -- cursors openen
    OPEN c_filter_antwoord_ids;
    OPEN c_kontakt_ids_initieel;
    
    -- eerste fetch doen om de tabel met kontakten voor de batch te vullen
    FETCH c_filter_antwoord_ids INTO v_antwoord_id;
    -- loop voor het vullen
    insert_loop: LOOP
      FETCH c_kontakt_ids_initieel INTO v_kontakt_id_initieel;
      IF v_notfound THEN
        LEAVE insert_loop;
        END IF;
      INSERT INTO filter_kontakt VALUES (v_filterbatch_id, v_kontakt_id_initieel, NOW(), 1);
    END LOOP;
  
    -- notfound resetten
    SET v_notfound = TRUE;
  
  
    -- nu per extra vraag iedereen er af halen die dat antwoord niet gegeven heeft
    delete_loop: LOOP
      FETCH c_filter_antwoord_ids INTO v_antwoord_id;
      IF v_notfound THEN
        LEAVE delete_loop;
        END IF;
      DELETE FROM filter_kontakt WHERE filterbatch_id = v_filterbatch_id AND kontakt_id NOT IN (
        SELECT kontakt_id
        FROM reactie
        WHERE antwoord_id = v_antwoord_id
      );
    END LOOP;
  
    CLOSE c_filter_antwoord_ids;
  
    RETURN v_filterbatch_id;

  ELSE RETURN 0;
  END IF;

END

Een dumpje van de betrokken tabellen:

SQL:

-- 
-- Tabel structuur voor tabel `antwoord`
-- 

CREATE TABLE `antwoord` (
  `id` int(11) NOT NULL auto_increment,
  `vraag_id` int(11) NOT NULL default '0',
  `antwoord` varchar(255) NOT NULL default '',
  `voorgedefinieerd` tinyint(1) NOT NULL default '0',
  `spring` int(11) default NULL,
  `rangorde` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=231 ;

-- --------------------------------------------------------

-- 
-- Tabel structuur voor tabel `filter`
-- 

CREATE TABLE `filter` (
  `id` int(11) NOT NULL auto_increment,
  `actie_id` int(11) NOT NULL,
  `naam` varchar(50) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `actie_id` (`actie_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;

-- --------------------------------------------------------

-- 
-- Tabel structuur voor tabel `filter_antwoord`
-- 

CREATE TABLE `filter_antwoord` (
  `filter_id` int(11) NOT NULL,
  `vraag_id` int(11) NOT NULL,
  `antwoord_id` int(11) NOT NULL,
  PRIMARY KEY  (`filter_id`,`vraag_id`,`antwoord_id`),
  KEY `antwoord_id` (`antwoord_id`),
  KEY `vraag_id` (`vraag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- --------------------------------------------------------

-- 
-- Tabel structuur voor tabel `filter_kontakt`
-- 

CREATE TABLE `filter_kontakt` (
  `filterbatch_id` int(11) NOT NULL,
  `kontakt_id` int(11) NOT NULL,
  `tijdstip` datetime NOT NULL,
  `definitief` tinyint(4) NOT NULL,
  PRIMARY KEY  (`filterbatch_id`,`kontakt_id`),
  KEY `kontakt_id_4` (`kontakt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- --------------------------------------------------------

-- 
-- Tabel structuur voor tabel `filterbatch`
-- 

CREATE TABLE `filterbatch` (
  `id` int(11) NOT NULL auto_increment,
  `filter_id` int(11) NOT NULL,
  `datum` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

-- 
-- Tabel structuur voor tabel `kontakt`
-- 

CREATE TABLE `kontakt` (
  `id` int(11) NOT NULL auto_increment,
  `actie_id` int(11) NOT NULL default '0',
  `status_id` int(11) NOT NULL default '1',
  `naam` varchar(100) NOT NULL default '',
  `firma` varchar(100) NOT NULL default '',
  `adres` varchar(100) NOT NULL default '',
  `postcode` varchar(8) NOT NULL default '',
  `woonplaats` varchar(50) NOT NULL default '',
  `telefoonnummer` varchar(20) NOT NULL default '',
  `code` varchar(35) NOT NULL default '',
  `memo` text NOT NULL,
  `terugbeltijd` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `actie_id` (`actie_id`),
  KEY `status_id` (`status_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1288 ;

-- --------------------------------------------------------

-- 
-- Tabel structuur voor tabel `vraag`
-- 

CREATE TABLE `vraag` (
  `id` int(11) NOT NULL auto_increment,
  `actie_id` int(11) NOT NULL default '0',
  `type` enum('open','checkbox','checkboxopen','radio','radioopen') NOT NULL default 'open',
  `vraag` varchar(100) NOT NULL default '',
  `uitleg` varchar(255) NOT NULL default '',
  `rangorde` int(11) NOT NULL default '0',
  `spring` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `actie_id_2` (`actie_id`,`rangorde`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

(om het nog ingewikkelder te maken, zitten er ook nog open vragen met open antwoorden in, maar dat laat ik er nu even buiten for sanity's sake )

[P.O. Box]

pfoei, dat is een aardig systeempje...

ik zat zelf te denken aan het volgende, maar waarschijnlijk werkt je stored procedure beter... als het volgende uberhaupt werkt:

PSEUDO:

SQL:

SELECT
      kontakt.id
FROM
     kontakt
LEFT JOIN
     reactie
ON
     kontact.id = reactie.kontakt_id
LEFT JOIN
     filter_antwoord
ON
     (
               filter = [[:gekozen_filter:]]
               AND
               reactie.antwoord_id = filter_antwoord.antwoord_id
     )
HAVING
              COUNT(aantalfilterongelijkNULL) = COUNT(aantalvragen)

dit werkt niet, maar moet volgens mij wel een mogelijkheid zijn. Maar als je stored procedure werkt, zou ik het daar lekker bij laten

[BikkelZ]

Dit zijn de twee twee stored functions die nu alles afvangen:

SQL:

FUNCTION telemarketing.write_filter(ev_filter_id INT)
  RETURNS INT
BEGIN
  -- losse vars declareren
  DECLARE v_aantal_filters_gevonden INT;
  DECLARE v_antwoord_id INT;
  DECLARE v_filterbatch_id INT;
  DECLARE v_filterbatch_size VARCHAR(23);
  DECLARE v_notfound BOOLEAN DEFAULT FALSE;

  -- cursors declareren
  DECLARE c_filter_antwoord_ids CURSOR FOR SELECT antwoord_id FROM filter_antwoord WHERE filter_id = ev_filter_id;

  -- handler declareren
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_notfound := TRUE;
  -- DECLARE EXIT HANDLER FOR SQLEXCEPTION csr_emp;

  
  SELECT COUNT(*) INTO v_aantal_filters_gevonden FROM filter WHERE id = ev_filter_id;

  -- kijken of het filter bestaat
  IF v_aantal_filters_gevonden > 0 THEN
  
    -- eerst filterbatch aanmaken voor deze filterbatch
    INSERT INTO filterbatch VALUES ('', ev_filter_id, NOW());
    SELECT MAX(id) INTO v_filterbatch_id FROM filterbatch WHERE filter_id = ev_filter_id;

    -- cursors openen
    OPEN c_filter_antwoord_ids;
    -- eerste id ophalen
    FETCH c_filter_antwoord_ids INTO v_antwoord_id;
  
    -- externe procedure voor het wegschrijven van de eerste ronde
    SELECT write_first_batch(v_antwoord_id, v_filterbatch_id) INTO v_filterbatch_size;

    IF v_filterbatch_size > 0 THEN
      -- nu per extra vraag iedereen er af halen die dat antwoord niet gegeven heeft
      delete_loop: LOOP
        FETCH c_filter_antwoord_ids INTO v_antwoord_id;
        IF v_notfound THEN
          LEAVE delete_loop;
          END IF;
        DELETE FROM filter_kontakt WHERE filterbatch_id = v_filterbatch_id AND kontakt_id NOT IN (
          SELECT kontakt_id
          FROM reactie
          WHERE antwoord_id = v_antwoord_id
        );
      END LOOP;
      RETURN v_filterbatch_id;
    ELSE
      RETURN 0;
    END IF;
    CLOSE c_filter_antwoord_ids;

  ELSE RETURN 0;
  END IF;

END

En

SQL:

FUNCTION write_first_batch(v_antwoord_id INT, v_filterbatch_id INT)
  RETURNS INT
BEGIN
  DECLARE v_notfound BOOLEAN;
  DECLARE v_kontakt_id_initieel INT;
  DECLARE v_count INT DEFAULT 0;
  DECLARE c_kontakt_ids_initieel CURSOR FOR SELECT kontakt_id FROM reactie WHERE antwoord_id = v_antwoord_id;

  -- handler declareren
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_notfound := TRUE;
    OPEN c_kontakt_ids_initieel;
    
    -- eerste fetch doen om de tabel met kontakten voor de batch te vullen
    insert_loop: LOOP
      FETCH c_kontakt_ids_initieel INTO v_kontakt_id_initieel;
      IF v_notfound THEN
        LEAVE insert_loop;
      ELSE
        INSERT INTO filter_kontakt VALUES (v_filterbatch_id, v_kontakt_id_initieel, NOW(), 1);
        SET v_count = v_count + 1;
      END IF;
    END LOOP;

    RETURN v_count;

END

Volgens mij kan een volleerde PL'ert me nog wel de oren wassen vanwege de manier waarop, maar het doet wat het moet doen zo te zien.

[Aham brahmasmi]

@BikkelZ en anderen: kan iemand mij goede websites aanraden waar ik meer kan leren over (MySQL)? Ik ben een PHP+MySQL amateur en probeer te leren meer gebruik te maken van de functionaliteit van MySQL, want ik gebruik nu alleen de meest basale queries (en doe verder alles met PHP). Een site over MySQL waar zoveel van te leren is als van php.net moet ik nog vinden...

[ Voor 13% gewijzigd door Aham brahmasmi op 01-06-2007 12:31 ]

[BikkelZ]

Aham brahmasmi schreef op vrijdag 01 juni 2007 @ 12:30:
@BikkelZ en anderen: kan iemand mij goede websites aanraden waar ik meer kan leren over (MySQL)? Ik ben een PHP+MySQL amateur en probeer te leren meer gebruik te maken van de functionaliteit van MySQL, want ik gebruik nu alleen de meest basale queries (en doe verder alles met PHP). Een site over MySQL waar zoveel van te leren is als van php.net moet ik nog vinden...

php.net is echt een van de grote motoren achter het succes van PHP. Alleen van Java ken ik zoiets, de rest lijkt minder goed gedocumenteerd te zijn (JavaScript bijvoorbeeld). Ten tijde van versie 3.x en 4.0.x was er ook geen noodzaak voor zulke documentatie - je kon vrij weinig met MySQL.

Nu mis ik ook goede documentatie voor deze database. Maar het voordeel is dat het wel redelijk standaard SQL is en dat de dingen die je niet kunt doen qua queries met MySQL wat wel in bvb Oracle kan, dat je met die termen wel weer kunt uitzoeken wat het verschil is of wat niet kan.

Dus mijn advies is om gewoon een keer een goed boek te kopen, ik heb zelf wisselende maar meestal wel goede ervaringen met de boeken van O'Reilly (maar die over PHP was echt klote), kijk eens of je er ook een hebt over MySQL versie 5 of hoger.