[MySQL] Query met boolean uitzondering

[Joep]

Ha devvers, daar ben ik weer

Ik heb de volgende databasestructuur, klikbaar:



Het boolean veld exception kan twee waardes bevatten, namelijk 0 en 1 (of groter). Als een record in de tabel agent_contract bij exception de waarde 0 heeft, houdt dat in dat desbetreffende agent het desbetreffende contract niet heeft. Heeft exception de waarde 1, dan houdt dat in dat desbetreffende agent het desbetreffende contract wel heeft. In beide gevallen geldt dit ongeacht of de agent het contract al wel of niet via een team waar 'ie lid van is heeft.

Nu wil ik graag een lijst hebben van de contracten die een agent heeft, dus contracten via team(s) plus en min de exceptions. Nu is me dit wel gelukt met onderstaande query, maar ik heb zo m'n twijfels omdat de volgorde van het afhandelen van de exceptions die er afgaan en erbij worden opgeteld uitmaakt.

SQL:

SELECT contract_id 
FROM contract_team 
INNER JOIN agent_team 
ON contract_team.team_id=agent_team.team_id 
WHERE agent_id='Agent1' 
AND contract_id NOT IN (
    SELECT contract_id 
    FROM agent_contract 
    WHERE agent_id='Agent1' AND exception=0
) 
UNION 
SELECT contract_id 
FROM agent_contract 
WHERE agent_id='Agent1' AND exception=1

Kan iemand me helpen met het verbeteren van de query en/of uitleggen welke exception ik voorrang moet geven?

[NNF]

Je query is volgens mij prima zo, maar de datastructuur is wel een beetje... apart Je gebruikt nu een variabele om aan te geven dat er geen relatie is, maar het hele punt van een record in zo'n tabel moet (IMO) juist zijn om aan te geven dat er wel een relatie is. Het heet niet voor niets een relationele database

Als je nou aan contract_team een kolom 'agent_id' toevoegt dan weet je welke contracten de agent via het team heeft. Contracten die de agent heeft maar niet via een team kunnen gewoon in agent_contract, maar dan kan die kolom 'exception' volgens mij gewoon weg en wordt je query ook een stuk simpeler.

Of zie ik nu iets over het hoofd?

[ Voor 7% gewijzigd door NNF op 07-06-2015 09:52 ]

[Damic]

Euhm waarom zijn de id's van Agent enzo varchars maak daar ook eens int() van

[emnich]

Je moet de agent_contracts ook joinen en niet met een subquery doen Dit killed te performance. Ook is de UNION SELECT volgens mij helemaal niet nodig.

Uiteindelijk wil je volgens mij alle gerelateerde records uit de agent_contract tabel (zowel met excepetion=0 als =1.


even uit m'n hoofd zou ik zo iets doen:

code:

SELECT ct.contract_id, ac.contract_id 
FROM contract_team AS ct
INNER JOIN agent_team as AT 
ON ct.team_id=at.team_id 
LEFT JOIN agent_contract as ac ON (ac.agent_id=at.agent_id)
WHERE at.agent_id='Agent1'
ORDER BY exception

Als ac.contract_id IS NULL dan weet je dat deze niet in de agent_contract tabel zit.

Misschien klopt bovenstaande query niet helemaal maar het idee moet zijn dat je een join maakt tussen alle tabellen - waarbij je het beste (small/med) int's kan gebruiken -. Ga niet werken met subqueries tenzij je zeker weet dat dit de performance ten goede komt (en dat is niet heel vaak).

[Joep]

Damic schreef op zondag 07 juni 2015 @ 09:55:
Euhm waarom zijn de id's van Agent enzo varchars maak daar ook eens int() van

What's the deal? In dit geval heb ik te maken met ID's die al uit o.a. letters bestaan, dus ik heb geen keus. Daarnaast boeit 't echt geen drol dat de PK/index een varchar is.

NNF schreef op zondag 07 juni 2015 @ 09:50:
Je query is volgens mij prima zo, maar de datastructuur is wel een beetje... apart Je gebruikt nu een variabele om aan te geven dat er geen relatie is, maar het hele punt van een record in zo'n tabel moet (IMO) juist zijn om aan te geven dat er wel een relatie is. Het heet niet voor niets een relationele database

Geen relatie != relatie, dus is er wel een relatie Klopt gewoon hoor. In dit geval kun je agents groeperen in team's. Die teams hebben een aantal contracten. Sommige agents hebben een contract extra of minder dan normaal. Hoe wil je dit anders dan in een database verwerken?

NNF schreef op zondag 07 juni 2015 @ 09:50:
Als je nou aan contract_team een kolom 'agent_id' toevoegt dan weet je welke contracten de agent via het team heeft. Contracten die de agent heeft maar niet via een team kunnen gewoon in agent_contract, maar dan kan die kolom 'exception' volgens mij gewoon weg en wordt je query ook een stuk simpeler.

Of zie ik nu iets over het hoofd?

Ja, want wat ga je doen als er opeens een contract bij een team wordt verwijderd of toegevoegd? En hoe zit het dan met de teams? Hoe weet je nu welk team welke contracten heeft? Er zit namelijk altijd een agent_id aan gekoppeld. Geloof me maar, dit is geen oplossing. Ik had ook iets soortgelijks bedacht, maar als er een contract bij een agent of team wordt verwijderd of toegevoegd, dan moet je nog extra queries uitvoeren die nog wat dingen moeten controleren en herstellen en dan ben je jezelf veel teveel extra complex werk aan het geven. De databasestructuur klopt nu.

@ emnich: Ik had al geprobeerd om die drie tabellen te joinen, maar het lukte me niet. Ik zal het eens proberen met jouw tips.

[Caelorum]

Joep schreef op maandag 08 juni 2015 @ 09:09:
[...] What's the deal? In dit geval heb ik te maken met ID's die al uit o.a. letters bestaan, dus ik heb geen keus. Daarnaast boeit 't echt geen drol dat de PK/index een varchar is.[...]

Maakt voor performance wel iets uit, maar dan moet je wel erg grote datasets krijgen wil je daar last van hebben. (Of erg lange strings in die varchar stoppen).

[emnich]

Joep schreef op maandag 08 juni 2015 @ 09:09:
[...]

What's the deal? In dit geval heb ik te maken met ID's die al uit o.a. letters bestaan, dus ik heb geen keus. Daarnaast boeit 't echt geen drol dat de PK/index een varchar is.

Natuurlijk heb je een keus. Je bent het toch zelf aan het bouwen ?

[xzaz]

Waarom die Exception nou precies, je relatie (tussentabel) geeft toch aan als de agent wel of geen contract heeft?

[Joep]

Caelorum schreef op maandag 08 juni 2015 @ 09:20:
[...]

Maakt voor performance wel iets uit, maar dan moet je wel erg grote datasets krijgen wil je daar last van hebben. (Of erg lange strings in die varchar stoppen).

I know, maar 't is een heel erg kleine database en geen lange varchar

emnich schreef op maandag 08 juni 2015 @ 09:29:
[...]

Natuurlijk heb je een keus. Je bent het toch zelf aan het bouwen ?

Nee, de ID bestaat al. Ik maak iets voor m'n stagebedrijf.

xzaz schreef op maandag 08 juni 2015 @ 09:45:
Waarom die Exception nou precies, je relatie (tussentabel) geeft toch aan als de agent wel of geen contract heeft?

Omdat het zonder exception onduidelijk is of de agent het contract wel of juist niet heeft.

[xzaz]

Joep schreef op maandag 08 juni 2015 @ 10:01:
[...]

Omdat het zonder exception onduidelijk is of de agent het contract wel of juist niet heeft.

Lijkt mij met mijn beknopte DB kennis dat juist je relatie daarvoor verantwoordelijk is.

[Joep]

Nou, en dan heb je een record in de tabel agent_contract met agent_id 'Agent1' en contract_id 'Contract2'

Wat nu? Heeft Agent1 nu Contract2 wel of niet? Stel dat je hiermee bedoelt dat Agent1 Contract2 wel heeft. Hoe wil je dan in dezelfde tabel een record maken die aangeeft dat een bepaalde agent een bepaald contract niet heeft?

[ValHallASW]

geen record = geen contract
wel een record = wel een contract

[Caelorum]

Lijkt mij dat hij dit wil:

Team 1: heeft contract 1 en 2.
Team 2: heeft geen contracten.

Agent 1 zit in Team 1, dus heeft contract 1 en 2.
Agent 2 zit in Team 1, maar moet geen contract 2 hebben.

Agent 3 zin in Team 2 en heeft geen contracten
Agent 4 zit in Team 2, maar heeft ook contract 1.

[Dido]

Caelorum schreef op maandag 08 juni 2015 @ 11:52:
Lijkt mij dat hij dit wil:

Team 1: heeft contract 1 en 2.
Team 2: heeft geen contracten.

Agent 1 zit in Team 1, dus heeft contract 1 en 2.
Agent 2 zit in Team 1, maar moet geen contract 2 hebben.

Agent 3 zin in Team 2 en heeft geen contracten
Agent 4 zit in Team 2, maar heeft ook contract 1.

Dan heb je in contract_team dus
Team 1 / Contract 1
Team 1 / Contract 2

in agent_team:
Agent 1 / Team 1
Agent 2 / Team 1
Agent 3 / Team 2
Agent 4 / Team 2

en in agent_contract
Agent 1 / Contract 1
Agent 1 / Contract 2
Agent 2 / Contract 1
Agent 4 / Contract 1

Als je default een agent die je aan een team toevoegt ook aan de contracten van dat team toe wilt voegen zijn daar beteer opties voor dan gaan werken met dit sorrt vlaggen.

Als je die vlag nodig denkt te hebben in agent_contract, waarom dan niet in contract_team? Als alle leden van een team onder contract x vallen, maar het team heeft dat contract niet, zijn dan alle teamleden "exceptions", of is dat ene team een "exception"? Of is er gewoon helemaal geen reden voor "exceptions"?

Als je een record opslaat dat zegt dat dit record eigenlijk niet bestaat, zit er een luchtje aan.

[Caelorum]

Het is gewoon maar net wat je handiger vind IMO. Je zal hier hoe dan ook moeten gaan kopiëren en bijhouden. Wil je dit elke keer dat je een contract aan een team toevoegt of een Agent toevoegt doen of wil je dit alleen doen als een bepaalde contract niet bij een agent moet? Wat komt vaker voor? Wat kost meer onderhoud? Dat moet je uitgangspunt zijn IMO.
De enige die hier iets zinnigs over kan zeggen is de TS. Blijven doorhameren op 1 van beide oplossingen, omdat die 'beter' zou zijn is IMO onzinnig als de TS al heeft aangegeven dat hij er van op de hoogte is.

[ Voor 22% gewijzigd door Caelorum op 08-06-2015 14:06 ]

[Jogai]

Een beetje lastig te duiden als je het domein niet kent, maar het klinkt meer als een real world problem. Blijkbaar zijn de teams in het echt ook niet altijd teams, maar zijn er agenten die soms niet bij het team horen vanwege een contract. Ik zou kijken of Sub/Super teams dit probleem niet kan oplossen.

[Dido]

Caelorum schreef op maandag 08 juni 2015 @ 14:05:
Het is gewoon maar net wat je handiger vind IMO.

Wat nu handig lijkt kan een valide reden zijn om degene die je code ooit gaat onderhouden aan te zetten een hitman in te huren...

Als ik de laatset reactie van de TS lees, zie ik geen enkel valide argument om "exceptions" op te nemen.

Zonder exceptions:
1) Als er een contract is is er een record.
2) Als er geen record is is er dus geen contract.

Met exceptions:
1) als er een record is met "false", dan is er geen contract.
2) als er een record is met "true", dan is er wel een contract.
3) als er geen record is, is er mogelijk toch een contract
4) als er geen record is, kan het ook zijn dat er geen contract is.

Het ligt misschien aan mij, maar als ik wil weten of een bepaalde agent onder een bepaald contract valt, en er staat een tabel agent_contract in mijn model, dan verwacht ik dat ik daar kan zien of die agent bij dat contract hoort.
En dat is niet het geval met deze "exceptions", of ik moet iet sheel erg over het hoofd zien.

[Caelorum]

Dido schreef op maandag 08 juni 2015 @ 14:14:
[...] Wat nu handig lijkt kan een valide reden zijn om degene die je code ooit gaat onderhouden aan te zetten een hitman in te huren...[...]

Inderdaad, maar ik had het niet over handig, maar over hoeveel werk het een kost vs het ander.

[...]Het ligt misschien aan mij, maar als ik wil weten of een bepaalde agent onder een bepaald contract valt, en er staat een tabel agent_contract in mijn model, dan verwacht ik dat ik daar kan zien of die agent bij dat contract hoort.
En dat is niet het geval met deze "exceptions", of ik moet iets heel erg over het hoofd zien.[...]

Je kan het daar nog steeds wel zien, alleen moet je naar twee tabellen kijken en 1 veld in dat tabel. Neem ook aan dat er bij de database documentatie komt over beslissingen en het waarom.

Jogai schreef op maandag 08 juni 2015 @ 14:12:
Een beetje lastig te duiden als je het domein niet kent, maar het klinkt meer als een real world problem. [...]

Inderdaad. Deze situatie zou je niet eens willen hebben en mogelijk zelfs buiten het ontwikkelproces willen oplossen. Helaas gaat dat niet altijd, waardoor je gedrochten kan hebben.

[...]Blijkbaar zijn de teams in het echt ook niet altijd teams, maar zijn er agenten die soms niet bij het team horen vanwege een contract. Ik zou kijken of Sub/Super teams dit probleem niet kan oplossen.[...]

Vraag is natuurlijk wat er hier onder Team valt. Kan me best situaties bedenken waarbij er wel een team is, maar sommige leden een uitzonderingspositie hebben. Dat kan je eventueel ook met rollen in het team oplossen.

[ajakkes]

Zoals ik hem lees hebben 90% van de teams een contract en 90% van de agenten zelf geen contract maar via hun team wel een contract. 5% van de agenten hoort wel tot een team maar heeft geen of een ander contract.

Hierdoor zijn ze een uitzondering.

Wanneer er een nieuwe agent tot een team wordt toegevoegd krijgt hij automatisch een contract. Wanneer hij uit een team stapt heeft hij geen contract.

Maar misschien snap ik het verkeerd.

[Joep]

O.a. Caelorum en Dido hebben me wat stof tot nadenken gegeven.

Rollen heb ik btw overwogen, maar dat werkt niet in deze situatie omdat de uitzonderingen gewoon willekeurig zijn en er geen touw aan vast te knopen is.

Zoals Caelorum aangeeft zijn er meerdere wegen die naar Rome leiden. Elke oplossing heeft z'n voor- en nadelen. Ik zit me nu dan ook af te vragen wat de meest handige en logische oplossing is. Het lijkt er op dat ik de database en code moet aanpassen naar de structuur en logica die de meesten in dit topic hebben voorgesteld. En ik weet nu al dat ik dan een query moet gaan maken waarbij ik hulp nodig heb

[Joep]

Ok, ik ga het volgende toepassen: de tabel "agent_contract" hernoem ik naar "exception" en het attribuut "exception" verdwijnt uit de tabel.



Als ik een overzicht wil hebben van de contracten die een agent heeft, selecteer ik de distinct contracten van de teams waar de agent lid van is min de contracten in de tabel exception waar de agent bijhoort.

Lijkt mij i.i.g. de meest duidelijke oplossing waarbij ik data niet redundant opsla. What you say?

Edit: He shit, dan kun je natuurlijk geen agent creëren die niet lid is van een team maar wel één of meerdere contracten heeft. Hmm, ff langer nadenken

Edit 2: Ok, ik houd toch vast aan het plan dat ik al had voordat ik dit topic opende. Het enige wat ik doe is de tabel "agent_contract" hernoemen naar "exception", zodat het voor iemand die de code doorleest duidelijker is wat de tabel bevat.

[ Voor 24% gewijzigd door Joep op 09-07-2015 14:28 ]

[Joep]

emnich schreef op zondag 07 juni 2015 @ 10:19:
Je moet de agent_contracts ook joinen en niet met een subquery doen Dit killed te performance. Ook is de UNION SELECT volgens mij helemaal niet nodig.

Uiteindelijk wil je volgens mij alle gerelateerde records uit de agent_contract tabel (zowel met excepetion=0 als =1.


even uit m'n hoofd zou ik zo iets doen:

code:

SELECT ct.contract_id, ac.contract_id 
FROM contract_team AS ct
INNER JOIN agent_team as AT 
ON ct.team_id=at.team_id 
LEFT JOIN agent_contract as ac ON (ac.agent_id=at.agent_id)
WHERE at.agent_id='Agent1'
ORDER BY exception

Als ac.contract_id IS NULL dan weet je dat deze niet in de agent_contract tabel zit.

Misschien klopt bovenstaande query niet helemaal maar het idee moet zijn dat je een join maakt tussen alle tabellen - waarbij je het beste (small/med) int's kan gebruiken -. Ga niet werken met subqueries tenzij je zeker weet dat dit de performance ten goede komt (en dat is niet heel vaak).

Het is me uiteindelijk gelukt met de volgende query:

SQL:

(SELECT DISTINCT contract.id, contract.name 
 FROM contract 
 JOIN contract_team 
 ON contract.id = contract_team.contract_id 
 JOIN agent_team ON 

 contract_team.team_id = agent_team.team_id 
 LEFT JOIN exception ON contract.id = exception.contract_id 
 WHERE agent_team.agent_id='Agent1' AND (exception.exception IS NULL OR exception.exception = 1)
) 
UNION 
(SELECT DISTINCT contract.id, contract.name 
 FROM contract 
 INNER JOIN exception 
 ON contract.id = exception.contract_id 
 WHERE exception.agent_id = 'Agent1' AND exception.exception = 1
)
ORDER BY id

Mocht iemand een manier weten om deze query te formuleren zonder union, dan hoor ik het graag. Ik heb een union gebruikt, omdat er natuurlijk ook contracten zijn die niet in de tabel exception zijn vermeld.

[Joep]

Hatseflats! En nu zonder union:

SQL:

SELECT DISTINCT contract.id, contract.name 
FROM contract 
LEFT JOIN contract_team 
ON contract.id = contract_team.contract_id 
LEFT JOIN agent_team 
ON contract_team.team_id = agent_team.team_id 
LEFT JOIN exception 
ON contract.id = exception.contract_id 
WHERE (agent_team.agent_id = 'Agent1' OR exception.agent_id = 'Agent1') 
AND (exception.exception IS NULL OR exception.exception = 1) 
ORDER BY contract.name

Ik heb hier een hoop van geleerd wat betreft joins, where-clause en union.

[Joep]

Update zodat iemand er wat aan heeft als 'ie dit topic tegenkomt via de search of google ofzo:
Uiteindelijk dit toch maar niet gedaan, want er klopte het één en ander niet. Het is wel mogelijk maar ik heb ervoor gekozen om de contracten die een agent heeft via de teams en de uitzonderingen apart weer te geven.