mysql, left join, met slechts 1 row uit de rechter met spec

[Verwijderd]

ok, ik kom hier dus echt even niet uit. Voor een site voor de club moet ik een site maken van alle arcade kasten en flipperkasten die iedereen heeft. Er moet een overzichts-pagina komen met de namen van de kast, en als er een foto is, moet die er bij.
Nu zijn er 2 tabellen 1 met de kasten, en 1 met de foto's.

tabel 1- kasten

kastid | eigenaar | kastnaam |valid | omschrijving
-------------------------------------------------------------------
1 | 3 | adams family | 1 | Lorem ipsum dolor sit amet
2 | 3 | Twilight zone | 1 | Lorem ipsum dolor sit amet
3 | 4 | AC/DC | 0 | Lorem ipsum dolor sit amet
4 | 5 | Lord of the Rings | 1 | Lorem ipsum dolor sit amet


tabel 2- fotos

fotoid | kastid | ismain | fotourl
-------------------------------------------------------------------
1 | 4 | 1 | fofofmasomfsaomfosa.jpg
2 | 4 | 0 | vdsnvnds89vds8vhsds.jpg
3 | 2 | 0 | v89ahv9sdvhs,jpg
4 | 2 | 0 | fsdfhsdfhsdavs.jpg
5 | 1 | 0 | sdv8ihsda98vhdsvs.jpg
etc
etc

nu kan het dus zijn dat een kast geen fotos heeft, of heel veel. Ook kan het zijn dat de gebruiker een foto als hoofdfoto heeft aangemerkt (ismain).

Nu wil ik in het overzicht enkel de laatst toegevoegde of als hoofdfoto aangemerkte (ismain = 1) foto gebruiken. En moet de kast goedgekeurd zijn (valid = 1)

wat ik nu heb, maar daar worden fotos ook niet goed gesorteerd,

select k.*,f.*
from kasten k
LEFT JOIN fotos f USING (kastid)
where k.valid = '1'
GROUP by k.kastid
order by k.kastid DESC, f.ismain DESC, f.fotoid ASC
LIMIT 5

Ik ben de hele middag al bezig, maar ik kom er niet uit, of ik krijg een overzicht met 3 kasten met alle foto's of helemaal geen foto's, en nu ben ik het overzicht helemaal kwijt,.

Iemand die mij op de juiste weg wil/kan helpen ?

[Verwijderd]

Zo iets?

select k.*,f.*
from kasten k
LEFT JOIN fotos f USING (kastid)
where k.valid = '1' AND (f.ismain=1 OR f.fotoid IS NULL)
GROUP BY f.kastid

[frickY]

Een subquery is hiervoor veel handiger.

[XiN-eViL]

Is dit een optie:

SQL:

SELECT k.*,
    f1.fotoid AS fotoidmain,
    max(f2.fotoid) AS fotoidlast
FROM kasten k
LEFT JOIN fotos f1 ON f1.kastid = k.kastid
AND f1.ismain = 1
LEFT JOIN fotos f2 ON f2.kastid = k.kastid
AND f2.ismain = 0
WHERE k.valid = '1'
GROUP BY k.kastid

Hier krijg je in fotoidmain het ID van de main foto als die er is en in fotoidlast de laatste foto, telkens per kast.
Je moet dan zelf nog even een query doen om alle foto-informatie op te halen van die foto's (en de logica of je fotoidmain of fotoidlast moet hebben, hoewel je dat eventueel in de query kunt stoppen met een IFNULL).

[Harrie_]

OT: ik heb verder weinig nuttigs bij te dragen behalve toch even te vragen om alles gewoon even in [code]-tags te wrappen. Een SQL query kun je in een [code=SQL]-tag wrappen. Het is echt niet zo moeilijk... en anders kun je hier nog even teruglezen hoe het zit met die [code]-tag...

Zo dus:

code: tabel 1 - kasten

kastid | eigenaar | kastnaam          |valid  | omschrijving
--------------------------------------------------------------------------
1      | 3        | adams family      | 1     | Lorem ipsum dolor sit amet
2      | 3        | Twilight zone     | 1     | Lorem ipsum dolor sit amet
3      | 4        | AC/DC             | 0     | Lorem ipsum dolor sit amet
4      | 5        | Lord of the Rings | 1     | Lorem ipsum dolor sit amet

code: tabel 2 - fotos

fotoid | kastid | ismain | fotourl
--------------------------------------------------
1      | 4      | 1      | fofofmasomfsaomfosa.jpg
2      | 4      | 0      | vdsnvnds89vds8vhsds.jpg
3      | 2      | 0      | v89ahv9sdvhs,jpg
4      | 2      | 0      | fsdfhsdfhsdavs.jpg
5      | 1      | 0      | sdv8ihsda98vhdsvs.jpg

SQL: de Query die niet naar behoren werkt...

SELECT 
      k.*
    , f.*
FROM kasten k
LEFT JOIN fotos f USING (kastid)
WHERE k.valid = '1'  
GROUP BY k.kastid
ORDER BY 
      k.kastid DESC
    , f.ismain DESC
    , f.fotoid ASC 
LIMIT 5

[ Voor 6% gewijzigd door Harrie_ op 01-09-2017 09:08 ]

[Josvds]

Inner select misschien een oplossing? Weet niet hoe ver dit werkt in MySQL.

[Verwijderd]

Omdat je in het geval dat ismain=0 de meest recente wilt hebben moet je die sub-selectie doen met een ORDER BY in combinatie met LIMIT, en LIMIT in een subquery is in MySQL niet mogelijk.

Heb je al eens gekeken naar views? Als je daarin een temp table vult is het verder niet zo heel lastig meer.

[Verwijderd]

hmmm
interesant..
waarschuwing vooraf: Ik ken alleen tsql ( sql server) dus qua syntax kan het een klein beetje afwijken

Eerst wil je uniek per kast weten of en een main foto is.

SQL:

Select kastid , max(ismain)  FROM Fotos GROUP BY kastid

Daarna haal je de fotoIds op welke je nodig hebt waarbij ik er vanuit ga dat de meest recente foto een hoger id heeft

SQL:

Select MAX(fotoid) AS fotoid, kastid
FROM 
Fotos f INNER JOIN 
(Select kastid , max(ismain)  FROM Fotos GROUP BY kastid) X 
ON f.kastid  = X.kastid AND f.ismain = X.ismain 
group by kastid

je hebt nu de id's van de fotos welke je wilt tonen. Omdat fotourl zich niet echt leend voor een max of een group by moet je deze later ophalen.

SQL:

SELECT f.kastid, f.fotourl 
FROM 
Fotos f INNER JOIN (
Select MAX(f1.fotoid) AS fotoid
FROM 
Fotos f1 INNER JOIN 
(Select kastid , max(ismain)  FROM Fotos GROUP BY kastid) as X 
ON f1.kastid  = X.kastid AND f1.ismain = X.ismain 
group by kastid
) as Y ON f.fotoid = Y.fotoid

en dit geheel kun je nu met een left join vast maken aan je kasten, zodat je alle kasten krijgt, en de eventuele bij behoorende foto

SQL:

SELECT k.kastid, k.eigenaar, k.kastnaam, k.valid, k.omschrijving,  f.fotourl 
FROM kasten k left join (
SELECT f2.kastid, f2.fotourl 
FROM 
Fotos f2 INNER JOIN (
Select MAX(f1.fotoid) AS fotoid
FROM 
Fotos f1 INNER JOIN 
(Select kastid , max(ismain)  FROM Fotos GROUP BY kastid) as X 
ON f1.kastid  = X.kastid AND f1.ismain = X.ismain 
group by kastid
) as Y ON f2.fotoid = Y.fotoid
) as f ON k.kastid = f.kastid
where k.isvalid = 1

dat zou het ongeveer moeten zijn.... maar ik hoor net dat ik moet gaan eten.

[Dido]

offtopic:
Opvallend dat iedereen, behalve @Verwijderd, botweg een GROUP BY in de query gooit zonder zich af te vragen wat dat nou betekent. Dat het mag van MySQL betekent nog niet dat je het moet doen.
Wat verwacht je in hemelsnaam van een SELECT * FROM tabel GROUP BY id?

[Verwijderd]

tja, die group by perikelen van mysql zorgt er voor dat ik weiger om mysql een rdbms te noemen....
maar je hebt gelijk dat viel mij ook al op

[ Voor 0% gewijzigd door Verwijderd op 25-08-2017 12:46 . Reden: typo ]

[Rotterdammertje]

Ik heb geen ervaring met mySQL, maar volgens mij kan je het wel oplossen met een sub-query met ORDER BY / LIMIT. Sorteer de foto's per kast, zodat de main foto's altijd eerst komen, en de andere foto's daarna omgekeerd gesorteerd op de foto id (aannemende dat de id's altijd netjes oplopen -- een time stamp o.i.d. zou beter zijn):

SQL:

select k.kastid,  (
  select fotoid
  from fotos f
  where f.kastid = k.kastid
  order by ismain desc, fotoid desc
  limit 1
) as fotoid
from kasten k

SQL Fiddle: http://sqlfiddle.com/#!9/6307627/5/0

[Verwijderd]

Thanks, heren (m/v) ik ga hier morgen even naar kijken

[Caelorum]

@Verwijderd Ik vraag me af of het in T-SQL (niet mysql) niet makkelijker is om iets als dit te doen: (uit de losse pols, dus zal vast niet meteen werken)

SQL:

select k.kastid, k.eigenaar, k.kastnaam, k.valid, k.omschrijving,  first_value(f.fotourl) over(partition by f.kastid order by f1.ismain desc, f.fotoid desc) 
from kasten k
left join Fotos f
     on k.kastid  = f.kastid
where k.isvalid = 1

In MySQL is bovenstaande dacht ik niet mogelijk, omdat je een resultset niet kan partionen (alleen een tabel), maar wellicht dat iets vergelijkbaars nog mogelijk is.

offtopic:
@Dido van die van XiN-eViL is de group by ook nog duidelijk

[merauder]

Je zou ook voor een eenvoudigere implementatie kunnen gaan.

Dus in eerste instantie maak je een loop door alle flipperkasten, waarbij je de isValid parameter gebruikt om alle niet goedgekeurde kasten te weren. In een ForEach loop haal je vervolgens de foto op. Als je de pagina cached zou je ook geen last moeten hebben van veel onnodige queries.

[Verwijderd]

@Caelorum dat zou in tsql absoluut werken. zonder indexen zou het zelfs sneller zijn. ( 30% vs 70% ) Maar het probleem is hier mysql....... ( of in iedergeval mijn kennis daarvan.) je hebt zonder distinct alleen dubbelingen. Wanneer ik select distinct doe, krijg ik dezelde output als welke ik zelf heb. Met de juiste indexen toegevoegt is mijn querie met 8% sneller. ( had ik ook niet verwacht )

@merauder Als je kunt kiezen tussen een simpele oplossing waarbij alles in één keer opgehaald word, of heel vaak kleine porties, wint in 99.9% van de gevallen de oplossing met slechts één querie. Elke keer dat jij een querie naar een database server stuurt, heb je latencie om naar de server te gaan, moet de querie parser van de database deze verwerken, en door sturen naar de volgende stap, en moet het resultaat terug. wanneer het geen json is, dan heeft dit resultaat nog meta informatie over kolom datatypen ed. En wanneer je het in een foreach loop doet, dan heb je vaak de eerste querie nog open, waardoor je voor langere tijd locking over je tabel krijgt, waardoor je dan weer problemen kunt krijgen met andere gebruikers welke een update proberen te doen.

[ Voor 10% gewijzigd door Verwijderd op 31-08-2017 12:52 . Reden: stukje over distinct toegevoegt ]

[_superboer_]

Je zou het ook kunnen oplossen met outer apply:

SQL:

SELECT 
       k.*,
       f.*
FROM 
       kasten k
OUTER APPLY 
       (
       SELECT 
              TOP 1 *
       FROM 
              fotos f 
       WHERE 
              f.kastid = k.kastid
       ORDER BY 
              f.ismain DESC, 
              f.fotoid DESC
       ) f
WHERE 
       k.valid = '1'

[Verwijderd]

@_superboer_ je vergeet dat wanneer er voor een kast geen foto beschikbaar is, deze een null moet krijgen. Nu worden kasten zonder foto's helemaal niet weer gegeven als ik je querie goed lees.

SQL:

CREATE TABLE [dbo].[fotos](
    [fotoid] [int] NULL,
    [kastid] [int] NULL,
    [ismain] [bit] NULL,
    [fotourl] [varchar](512) NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [ClusteredIndex-20170831-124448] ON [dbo].[fotos]
(
    [fotoid] ASC
)
GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20170831-124552] ON [dbo].[fotos]
(
    [ismain] DESC,
    [fotoid] DESC
)
INCLUDE ( [kastid], [fotourl])
GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20170831-124926] ON [dbo].[fotos]
(
    [kastid] ASC,
    [ismain] ASC
)
GO

CREATE TABLE [dbo].[kasten](
    [kastid] [int] NULL,
    [eigenaar] [varchar](200) NULL,
    [kastnaam] [varchar](200) NULL,
    [valid] [bit] NULL,
    [omschrijving] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20170831-124759] ON [dbo].[kasten]
(
    [kastid] ASC
)
GO

INSERT INTO fotos (fotoid,kastid,ismain,fotourl) VALUES
(1, 1,  1,  'een.jpg'),
(2, 1,  0,  'twee.jpg'),
(3, 2,  1,  'drie.jpg'),
(4, 2,  1,  'vier.jpg'),
(5, 3,  1,  'vijf.jpg'),
(6, 4,  1,  'zes.jpg'),
(7, 6,  1,  'zeven.jpg'),
(8, 1,  0,  'acht.jpg'),
(9, 7,  1,  'negen.jpg'),
(10,10, 0,  'tien.jpg'),
(11,10, 1,  'elf.jpg'),
(12,10, 0,  'twaalf.jpg'),
(13,11, 0,  'dertien.jpg'),
(14,11, 0,  'veertien.jpg'),
(15,11, 0,  'vijftien.jpg'),
(16,11, 0,  'zestien.jpg'),
(17,12, 0,  'zeventien.jpg'),
(18,13, 0,  'achtien.jpg'),
(19,14, 0,  'negentien.jpg'),
(20,16, 0,  'twintig.jpg');
GO

INSERT INTO kasten (kastid, eigenaar, kastnaam, valid, omschrijving) VALUES
(1, 'ikke', '', 1, ''), 
(2, 'nie ikke', '', 1, ''), 
(3, 'jij', '', 1, ''),
(4, 'nie jij', '', 1, ''),
(5, 'wij', '', 1, ''),
(6, 'ons', '', 1, ''),
(7, 'hunnie', '', 1, ''),
(8, 'hullie', '', 1, ''),
(9, 'jullie', '', 1, ''),
(10, 'jaapie', '', 1, ''),
(11, 'joost', '', 1, ''),
(12, 'frits', '', 1, ''),
(13, 'sjaak', '', 1, ''),
(14, 'sjonnie', '', 1, ''),
(15, 'anita', '', 1, ''),
(16, 'kees', '', 1, '');

SQL:

--40% tijd volgens voorspelling
SELECT k.kastid, k.eigenaar, k.kastnaam, k.valid, k.omschrijving,  f.fotourl 
FROM kasten k left join (
SELECT f2.kastid, f2.fotourl 
FROM 
Fotos f2 INNER JOIN (
Select MAX(f1.fotoid) AS fotoid
FROM 
Fotos f1 INNER JOIN 
(Select kastid , max(cast(ismain as tinyint)) ismain FROM Fotos GROUP BY kastid) as X 
ON f1.kastid  = X.kastid AND f1.ismain = X.ismain 
group by f1.kastid
) as Y ON f2.fotoid = Y.fotoid
) as f ON k.kastid = f.kastid
where k.valid = 1
GO

--47% volgens voorspelling
select distinct
    k.kastid, k.eigenaar, k.kastnaam, k.valid, k.omschrijving,  
    first_value(f.fotourl) over (partition by f.kastid order by f.ismain desc, f.fotoid desc) 
from kasten k
left join Fotos f
     on k.kastid  = f.kastid
where k.valid = 1
order by 1
GO

--13% volgens voorspelling, maar wel te weinig kasten.
SELECT 
       k.*,
       f.*
FROM 
       kasten k
CROSS APPLY 
       (
       SELECT 
              TOP 1 *
       FROM 
              fotos f 
       WHERE 
              f.kastid = k.kastid
       ORDER BY 
              f.ismain DESC, 
              f.fotoid DESC
       ) f
WHERE 
       k.valid = '1'

[ Voor 104% gewijzigd door Verwijderd op 31-08-2017 13:06 ]

[_superboer_]

@Verwijderd

Je hebt gelijk, in dat geval is een OUTER APPLY beter. Ik heb de post aangepast.

[merauder]

@Verwijderd Moet heel erg hard toegeven dat MySQL of SQL heel erg mijn dagelijkse bezigheden zijn want ik ben maar een front-end devver

Wel een opmerking. Outer apply / Cross apply zijn een paar specifieke functies van TSQL. Voor MySQL zou je aan een LEFT JOIN kunnen denken.

[RedHat]

Als het aantal foto's per kast geen enorme aantallen betreft kun je toch gewoon alle foto's ophalen en daarna je array builden zoals je het zelf wilt?

Lijkt mij simpeler dan enorm complexe querys te gaan uitvinden wanneer dat niet hoeft.

[Caelorum]

@Verwijderd jouw queries doen niet allemaal hetzelfde. De tweede ordent ook nog de resultset, de andere n niet. De cross apply moet je vervangen voor een outer apply om een vergelijkbaar iets te krijgen. Zoals @_superboer_ al had aangegeven
De distinct bij de query met de partition is op zich wel logisch. Je krijgt gewoon de eerste result terug voor elke rij uit de resultset, dus je krijgt een rij per foto waar een kast bij hoort terug.

@RedHat Persoonlijk zou ik als het even kan toch niet meer data ophalen dan nodig. Het is doorgaans sneller en efficiënter om het door de DB te laten doen, dan zelf te gaan knutselen naderhand. Overigens verbaast het me hoeveel werk er nodig is om mysql dit te laten doen. Zelfs met onze vorige database op het werk (een stokoude sybase) was het nog niet zo lastig om dit soort dingen te doen. Toegegeven, we moesten wel vaak terugvallen op een temp-table, maar toch.

[RedHat]

Caelorum schreef op donderdag 31 augustus 2017 @ 22:09:

@RedHat Persoonlijk zou ik als het even kan toch niet meer data ophalen dan nodig. Het is doorgaans sneller en efficiënter om het door de DB te laten doen, dan zelf te gaan knutselen naderhand. Overigens verbaast het me hoeveel werk er nodig is om mysql dit te laten doen. Zelfs met onze vorige database op het werk (een stokoude sybase) was het nog niet zo lastig om dit soort dingen te doen. Toegegeven, we moesten wel vaak terugvallen op een temp-table, maar toch.

Voor mijn educatie, kun je dat onderbouwen?
Als er niet zoveel data in staat (en een paar foto's per flipperkast lijkt mij dat de case) dan ben ik eigenlijk wel heel benieuwd naar benchmarks met moeilijke joins of een array opnieuw bouwen in PHP. Ik heb al een tijd geen PHP/MySQL gebruikt alleen ik vond de performance van MySQL vroeger niet denderend wanneer je wat meer complexere query's ging maken. Er zal ongetwijfeld een hoop veranderd zijn maar ben toch benieuwd.

Ik bedoel ook niet dat het de meest elegante oplossing is, alleen ik denk dat het hier om een dermate kleine tabel gaat met weinig data dat het ook anders 'kan'. Afhankelijk van de hoeveelheid data. Maar er is ook niets mis mee met trial & error en er wat van leren om een query te bouwen die precies doet wat je wilt.

[ Voor 13% gewijzigd door RedHat op 31-08-2017 22:34 ]

[Caelorum]

mysql ben ik niet zo bekend mee. Alleen sybase en mssql, maar daar is het vaak wel sneller als je de goede indexes aanlegt. Punt is dat de DB juist gemaakt is om efficient data op te halen. Daar houdt het ding allerlei heuristiek voor bij (expliciet door ons aangegeven of uit zichzelf) om dat zo snel mogelijk te kunnen doen. Daarnaast moet het al over de resultset lopen om het te serializen en terug te geven aan de client, dus waarom op de client er nog eens overheen lopen om het te filteren e.d.? Tenzij je het meteen bij het daadwerkelijk gebruiken (opbouwen van de view?) doet, moet je er zelfs nog eens overheen lopen. Allemaal extra werk dat je kan besparen.
Zoals gezegd kan ik het mij niet voorstellen dat mysql het slechter doet dan een 12 jaar oude sybase installatie. Dat ding was niet vooruit te branden met queries waar mssql gewoon instant antwoord op had, maar zelfs daar konden we veel van die queries sneller op de DB doen dan in de client door gebruik te maken van tmp tables. Dat scheelt gewoon alleen al op processing time aan server en client kant.

Ik werk dagelijks aan een product wat veel data verwerkt en je wilt niet weten hoeveel oude code ik wel niet heb versneld door gewoonweg minder data op te halen uit de DB en meer verwerking op de DB te doen. En dat is niet omdat de webservers traag zijn en de DB snel, die zijn redelijk in balans. Gewoonweg minder data sturen tussen applicaties is vrijwel altijd sneller, omdat je een stuk (de)serialisatie mist en in het geval van een DB mis je aan de clientkant ook heuristiek over de data die er in de resultset zit op basis waarvan je het juiste algoritme kan kiezen om er iets mee te doen (misschien is de set bijv. groot genoeg om te multithreaden? misschien een index gebruiken of is er overheen lopen sneller?).

Iemand met meer DB kennis kan hier vast meer over uitweiden. Vrijwel al mijn kennis is self-taught on the job, voornamelijk bij een bedrijf dat vrijwel niets anders doet dan data rondschuiven en aggregeren.

[ Voor 5% gewijzigd door Caelorum op 31-08-2017 22:54 ]

[Fiander]

@Caelorum 22:09: Nee natuurlijk doen die dat niet, ik heb daar alleen de verschillende query's gepakt zoals die er op dat moment waren. waarbij alleen de eerste van mijn hand is.
als reactie hierop heeft @_superboer_ inderdaad zijn querie aangepast.
en inderdaad, jou querie heeft een sortering extra, die is alleen bij deze querie nodig om de volgorde tbv vergelijken goed te krijgen. bij de andere queries ging dat dankzei andere indexen reeds automatisch goed. tbv je windowd functie moest ik een desc index gebruiken om het eerlijk te houden. anders zou deze query geen geschikte index hebben om te gebruiken.

Ik heb getracht om het zo eerlijk mogelijk te maken. vergeef me voor die order by die ik moest toevoegen