Michel Vuijlsteke's weblog

Tales of Drudgery & Boredom.

Overdrijven met SQL

Mensen hebben een adres. Of nee, mensen hebben eventueel een adres. Of nee, mensen hebben eventueel meer dan één adres. Of nee, mensen hebben eventueel meer dan één adres, waarvan er 0, 1 of meer nog geldig zijn en/of waren, eventueel van een bepaalde datum tot een bepaalde datum.

Deze situaties zijn (onder meer) mogelijk:

  • Persoon A
    1. Adres 1 (tot 1998)
    2. Adres 2 (1998-2000)
    3. Adres 3 (sinds 2000)
  • Persoon B
    1. Adres 1 (tot 2010)
    2. Adres 2 (2010-2012) <– dus niet meer geldig!
  • Persoon C
    1. Adres 1 <– aangeduid als ‘ongeldig’, bijvoorbeeld omdat een brief onbesteld terugkwam
  • Persoon D
    1. Adres 1 (tot 1998)
    2. Adres 2 (sinds 1998) <– aangeduid als ‘ongeldig’

Het is ook mogelijk dat een persoon gezegd heeft dat hij niet meer in de adressenlijst wil staan, mensen kunnen meer dan één geldig adres hebben (thuis en werk, bijvoorbeeld, of buitenlands adres en Belgisch p/a voor correspondentie).

Gegeven dit, wil ik een lijst van mensen waar adresproblemen mee zijn:

  • alle mensen die geen adres hebben
  • alle mensen met enkel ongeldige adressen
  • voor de mensen met enkel ongeldige adressen: het meest recente ongeldige adres, met de ‘geldigheidsdata’ erbij

Dat lijkt op het eerste zicht allemaal zo simpel als iets, maar uiteindelijk toch niet echt: ik wou een bestaande template hergebruiken en dus was ik beperkt tot een bepaald aantal velden (en moesten die ‘geldigheidsdata’ in het adresveld geplakt worden, maar dat lukt niet als ze soms null zijn. En oh ja, het is niet evident om mensen zonder adressen en mensen met alleen ongeldige samen te krijgen, en dan nog eens voor die laatste enkel het meest recente ongeldige adress erbij.

Maar impossible pas français, en SQL is een gewillige hoer als het op dergelijke dingen doen aankomt, en uiteindelijk was het min of meer snel in orde. Maar zoals ze zeggen: it really puts the ‘dirty’ in ‘quick & dirty’.

Dat is de miserie soms: het bouwt en bouwt en voor ge het weet, is een query een monstrositeit zoals dit:

SELECT p.id, p.voornaam, p.achternaam, 
       r.bla, r.spel, c.geriefdata blop,  
   if(length(a.adres)>0, 
      concat(coalesce(a.adres,''), 
         '\n[', coalesce(a.van,''),
         ' - ',coalesce(a.tot,''),
         ']'), 
      null) adres
FROM persoon p 
   inner join dink r on p.dink_id=r.id
   left outer join gerief c on p.id=c.persoon_id
   left outer join 
      (select a1.adres, a1.persoon_id, 
         a1.geldig, a1.van, a1.tot
       from adres a1
         inner join (
            select persoon_id, 
               max(van) as recentste
            from adres
            where geldig=0
            group by persoon_id
         ) a2 on a1.persoon_id=a2.persoon_id and 
               a1.van = recentste
      ) a on p.id=a.persoon_id
WHERE
   p.splut=0 and
   p.pulk=1  and
   (a.geldig=0 or a.geldig is null) and 
   (c.gerieftype='blop' or c.id is null) and
   p.id not in (
      select persoon_id
      from adres
      group by persoon_id
      having sum(geldig)>0
   )
GROUP BY p.id
ORDER BY  r.bla, r.spel, p.achternaam

Erm ja. Niet dat het zó enorm groot is, maar ik zou dat eigenlijk eens moeten van nul af aan herbekijken, ja. 🙂

6 reacties

  1. Nu nog een filter om de woonwagenbewoners er uit te houden…?

  2. Met Common Table Expressions (WITH) worden dat soort monsterjes een stuk overzichtelijker.

    • Absoluut. Vandaar dat mijn adressentabel er zo uitziet:

      id
      adres (longtext)
      van (date)
      tot date)
      geldig (boolean)

      Gewoon alles in één veld gepleurd. Het alternatief is alles voor iedereen willen zijn, zoals Drupal’s Address Field dat constant alles moet achternahollen.

      (Het andere alternatief is doen zoals het naiefgewijs gedaan wordt: straat, nummer, postcode, stad, land in allemaal veldjes, of eventueel met postcode+gemeente en/of land uit een andere tabel, maar dat loopt gegarandeerd ook in de soep.)

      Het enige dat ik mij afvraag, is of het eventueel nuttig zou zijn om een notie van ‘niet-Belgisch adres’ te hebben.

Zeg uw gedacht

© 2017 Michel Vuijlsteke's weblog

Thema gemaakt door Anders NorenBoven ↑