[PGSQL] WHERE na een ORDER?

[KennieNL]

Momenteel heb ik een query om een lijst van fotos op te halen uit een DB:

SQL:

SELECT photo_id,horizontal FROM photo WHERE photo_album_id = 5 ORDER BY horizontal,photo_id

De resultaten worden gesorteerd op de bool horizontal, en daarna op de photo_id.


Nu wil ik via een query bepalen wat de volgende photo_id in de rij van bovenste resultaten is (ivm vorige/volgende knopjes bij het foto bekijken )
Simpel een 'WHERE photo_id > x' gaat niet werken, gezien hij over de where clause zal gaan sorteren (en dus in veel gevallen een foute photo_id returnt).


Heeft iemand een idee hoe ik dit kan klaar krijgen? Heb al een tijd zitten puzzelen, maar ik ben inmiddels al wat haar kwijt


Ohja, het gaat hier om Postgresql 8.3.11

[ Voor 3% gewijzigd door KennieNL op 19-09-2010 22:29 ]

[ValHallASW]

Je hebt geen LIMIT in je query? Gewoon een tweede rij ophalen dan.

[KennieNL]

ValHallASW schreef op zondag 19 september 2010 @ 22:38:
Je hebt geen LIMIT in je query? Gewoon een tweede rij ophalen dan.

Ja maar hoe weet ik mn positie van de eerste photo_id in de list?

[ValHallASW]

Dan moet je je probleem even duidelijker formuleren (grote kans dat je daarmee zelf al bedenkt wat de oplossing is). Hoe ik het begrijp:
* je alle foto's in een zeker album op, een lijst [(photo_id_i, horizontal_i)] i=0..N, gesorteerd op (horizontal, id)
* daar laat je er eentje van zien: (photo_id_k, horizontal_k), 0<=k<=N
* vervolgens wil je een linkje naar foto (photo_id_k-1, horizontal_k-1) en (photo_id_k+1, horizontal_k+1)

Maar dat is blijkbaar niet wat je wilt. Wat wel?

[ Voor 3% gewijzigd door ValHallASW op 19-09-2010 22:50 ]

[KennieNL]

horizontal is een bool, geen integer.

Ik heb een lijst:
id;horizontal (t=true, f=false)
88;f
92;f
94;f
100;f
101;f
102;f
89;t
90;t
91;t
93;t
95;t
96;t
97;t
98;t
99;t
103;t
104;t
105;t


Stel ik heb foto 99, dan wil ik de volgende weten (in dit geval, 103)

[ Voor 3% gewijzigd door KennieNL op 19-09-2010 23:01 ]

[The Eagle]

Dubbel ophalen
je krijgt dan zoiets:

code:

Select a.photo_id, b.photo_id  FROM photo a , photo b
WHERE a.photo_album_id = 5 
and b.photo_id = select min(b.photo_id) from photo b 
                               where b.photo_id > a.photo_id and a.album_id = b.album_id)
ORDER BY a.horizontal,a.photo_id

Wat je dus feitelijk doet is de minimale waarde van een id ophalen voor die betreffende range waarvan de waarde groter is dan de al opgevraagde waarde

Evt kun je zoiets ook met een having-clausule oplossen, maar dan nog zit je met 2 waarden die je tegelijkertijd op wilt halen. Dus aan iets van aliassen ontkom je eigenlijk niet

Edit: Of je gaat met een union aan de gang, kan ook nog

[ Voor 4% gewijzigd door The Eagle op 19-09-2010 23:11 ]

[ValHallASW]

Hoe kom je aan foto 99? Kan je niet tegelijk met foto 99 óók foto 103 ophalen?

[Anoniem: 26306]

Sowieso moet je niet sorteren op een surrogate key. Je zou een datum/tijd van toevoegen kunnen gebruiken of iets dergelijks, maar aan een surrogate key moet je geen betekenis koppelen omdat de enige reden dat zo' key bestaat is dat hij een unieke identifier is.

Maar dan nog, je kunt gewoon sorteren op "horizontal = $horizontal_van_huidige_item, datetime > $datetime_van_huidige_item, datetime".

[KennieNL]

@TheEagle: ik zal het proberen

ValHallASW schreef op zondag 19 september 2010 @ 23:12:
Hoe kom je aan foto 99? Kan je niet tegelijk met foto 99 óók foto 103 ophalen?

via $_GET request in php, zou eventueel kunnen

@Cheatah: die bool waarde kan veranderen van false -> true

[Anoniem: 26306]

Ja, und? Het idee is dat je condities in de ORDER BY clause kunt gebruiken.

[Killemov]

Anoniem: 26306 schreef op zondag 19 september 2010 @ 23:15:
Sowieso moet je niet sorteren op een surrogate key. Je zou een datum/tijd van toevoegen kunnen gebruiken of iets dergelijks, maar aan een surrogate key moet je geen betekenis koppelen omdat de enige reden dat zo' key bestaat is dat hij een unieke identifier is.

Maar dan nog, je kunt gewoon sorteren op "horizontal = $horizontal_van_huidige_item, datetime > $datetime_van_huidige_item, datetime".

Waarom niet? (BS IMHO)

Vaak is de PK een serial en zit de timestamp in dezelfde volgorde als de PK, dan kan het ZEKER WEL lonen om te sorteren op de PK omdat de sortering via de index KAN gaan en ... integers vergelijken KAN sneller gaan dan timestamps.

[Wolfboy]

KennieNL schreef op zondag 19 september 2010 @ 22:20:
Nu wil ik via een query bepalen wat de volgende photo_id in de rij van bovenste resultaten is (ivm vorige/volgende knopjes bij het foto bekijken )
Simpel een 'WHERE photo_id > x' gaat niet werken, gezien hij over de where clause zal gaan sorteren (en dus in veel gevallen een foute photo_id returnt).

Ik gok dat je "horizontal,photo_id" combinatie uniek is.

Zoja dan kan je prima een "WHERE photo_id > x" doen als je er maar een "horizontal > y" bij stopt

Want daar komt het in je sortering ook op neer.

[ Voor 5% gewijzigd door Wolfboy op 20-09-2010 16:28 ]

[Remus]

Waarom niet gewoon limit en offset gebruiken? http://www.postgresql.org/docs/8.4/static/queries-limit.html

Never mind...

Ik vind nog steeds niet echt heel duidelijk wat nu het probleem is dat je op probeert te lossen.

[ Voor 34% gewijzigd door Remus op 20-09-2010 08:17 ]

[KennieNL]

The Eagle schreef op zondag 19 september 2010 @ 23:10:
Dubbel ophalen
je krijgt dan zoiets:

code:

Select a.photo_id, b.photo_id  FROM photo a , photo b
WHERE a.photo_album_id = 5 
and b.photo_id = select min(b.photo_id) from photo b 
                               where b.photo_id > a.photo_id and a.album_id = b.album_id)
ORDER BY a.horizontal,a.photo_id

Wat je dus feitelijk doet is de minimale waarde van een id ophalen voor die betreffende range waarvan de waarde groter is dan de al opgevraagde waarde

Evt kun je zoiets ook met een having-clausule oplossen, maar dan nog zit je met 2 waarden die je tegelijkertijd op wilt halen. Dus aan iets van aliassen ontkom je eigenlijk niet

Edit: Of je gaat met een union aan de gang, kan ook nog

Helaas, deze query geeft me ook weer 100 ipv 103 als opvolger van 99.

Laat ik nog een keer proberen mn probleem te beschrijven:
Ik heb een photoalbum met photos. Om het design een beetje netjes te houden stel ik een boolean in of de foto horizontaal of verticaal gemaakt is.

Tijdens het ophalen van de fotos sorteer ik als eerste op deze boolean (horizontal) en daarna op de photo_id.
Uit dat lijstje (zie vorige post) wat hier uit komt wil ik vorige en volgende photo_id van een bepaalde photo krijgen:


Voor 88 is de volgende 92, en geen vorige
Voor 92 is de volgende 94, de vorige 92
Voor 94 is de volgende 100, de vorige 101
...etc (zie lijstje wat ik eerder gepost heb).

Het lukt mij dus niet een query te maken welke ook rekening houd met de horizontal boolean. Als ik voor volgende een simpele WHERE photo_id > vorige_id doe, en daarna soorteer op de boolean krijg ik verkeerde resultaten (de ORDER wordt toegepast op de WHERE, en hier vooraf rekening mee gehouden).


Duidelijk nu?

[Voutloos]

KennieNL schreef op maandag 20 september 2010 @ 09:01:
[...]


Helaas, deze query geeft me ook weer 100 ipv 103 als opvolger van 99.

Dan voeg je in de query toe waarom je 100 niet goed vind en ben je klaar. Waarschijnlijk wil je ook iets als a.horizontal = b.horizontal.

[P_de_B]

SQL:

SELECT TOP 1 *, 'Next' as Which 
FROM Photos
WHERE Horizontal = 'F' AND id > 94 ORDER BY ID ASC


SELECT TOP 1 *, 'Next' as Which 
FROM Photos
WHERE Horizontal = 'F' AND id < 94 ORDER BY ID DESC

Geeft dit foute resultaten? (MS SQL specifiek, vervang de top door LIMIT)

[ Voor 8% gewijzigd door P_de_B op 20-09-2010 09:12 ]

[KennieNL]

horizontal kan veranderen, zie:
102;f
89;t

Is er een manier om de result row number op te vragen? Zodat ik deze +1 en -1 kan doen.

[P_de_B]

De query die ik gaf geeft volgens mij voor het lijstje dat je eerder poste de juiste resultaten. Ik snap je opmerking dan ook niet.

Hou je er wel rekening mee dat er geen andere volgorde in een resultset is als de volgorde die je meegeeft in de ORDER BY?

[jbdeiman]

Even zien of ik je begrijp:
Probeer je nu door een bepaalde query, je design netjes te houden? Is het niet beter om dit buiten je SQL om op te lossen? Voor zo'n album kan je een "rowdiv" gebruiken, met daarin de photodivjes, die een bepaalde breedte hebben (om altijd "zoveel" foto's op een rij te hebben), en door de rowdiv allemaal dezelfde hoogte lijken te hebben, zodat je je foto's goed kan positioneren?

Heb je er bijv. wel aan gedacht dat je door deze sortering op enig punt in je fotolijst een overgang hebt van liggend naar staande foto's of andersom? Op dit omslagpunt heb je nog steeds hetzelfde probleem. En het is vervelend als je een nieuwe foto toevoegd die in portrait (== horizontal = false) is gemaakt, omdat je dan eerst voorbij alle liggende (landscape) foto's moet scrollen.
Volgens mij kan je beter kijken naar de beperkingen in je huidige uitwerking van de lay-out dan dit oplossen met een query/ sortering.

Als het gaat om de weergavepagina kan je om de foto's ook een div met vaste grootte maken, en daaronder de knoppen plaatsen. Een foto positioneren in die div is heel eenvoudig, en je design heeft ook geen problemen meer.

[ Voor 10% gewijzigd door jbdeiman op 20-09-2010 09:53 ]

[KennieNL]

P_de_B schreef op maandag 20 september 2010 @ 09:40:
De query die ik gaf geeft volgens mij voor het lijstje dat je eerder poste de juiste resultaten. Ik snap je opmerking dan ook niet.

Hou je er wel rekening mee dat er geen andere volgorde in een resultset is als de volgorde die je meegeeft in de ORDER BY?

Resultaten kloppen hier anders niet.
Die laatste opmerking snap ik even niet.

horizontal kan dynamisch zijn, als ik deze als true/false in mn where clause meegeef krijg ik geen goede resultaten.

[ReenL]

PostgreSQL:

code:

LIMIT 1 OFFSET 0

de "volgende" is dan

code:

LIMIT 1 OFFSET 1

etc

[P_de_B]

Ah, ok. Als je geen 'vorige' record hebt waarbij horizontal = 'T' wil je het hoogste ID waarbij horizontal = 'F'?

Dan moet je ROWNUM gebruiken, Postgres ondersteunt dat vanaf 8.4 of zo.

[ Voor 24% gewijzigd door P_de_B op 20-09-2010 10:03 ]

[KennieNL]

P_de_B schreef op maandag 20 september 2010 @ 09:59:
Ah, ok. Als je geen 'vorige' record hebt waarbij horizontal = 'T' wil je het hoogste ID waarbij horizontal = 'F'?

Exactly!


@ReenL: ik weet de vorige rownumber niet, alleen het id.

[P_de_B]

Zie mijn edit, Postgres heeft hier een functie voor.

[KennieNL]

P_de_B schreef op maandag 20 september 2010 @ 10:04:
Zie mijn edit, Postgres heeft hier een functie voor.

Hmm, moet ik maar eens postgresql update naar 8.4

Ik sta tevens open voor andere suggesties

[P_de_B]

Zoiets zou ook nog kunnen, niet echt fraai maar als je het echt aan de sql kant moet oplossen werkt het wel.

SQL:

DECLARE @CurrentRank INT
SELECT @CurrentRank = 1000000

SELECT  TOP 1 'Vorige' as Sequence, * FROM (
SELECT id, horizontal, 
        CASE horizontal 
        WHEN 'F' THEN 
            (SELECT COUNT(*) FROM Photos WHERE Id < P.Id AND Horizontal = P.Horizontal)+1 
        WHEN 'T' THEN (SELECT   COUNT(*) FROM Photos WHERE Id < P.Id AND Horizontal = P.Horizontal)+ 1000000
        END AS Rank
FROM Photos P
) as A
WHERE Rank < @CurrentRank ORDER BY RANK DESC

SELECT  TOP 1 'Volgende' as Sequence, * FROM (
SELECT id, horizontal, 
        CASE horizontal 
        WHEN 'F' THEN 
            (SELECT COUNT(*) FROM Photos WHERE Id < P.Id AND Horizontal = P.Horizontal)+1 
        WHEN 'T' THEN (SELECT   COUNT(*) FROM Photos WHERE Id < P.Id AND Horizontal = P.Horizontal)+ 1000000
        END AS Rank
FROM Photos P
) as A
WHERE Rank > @CurrentRank ORDER BY RANK DESC

Je moet in je code alleen zorgen dat je de huidige RANK onthoudt en dat als input gebruik in je query voor volgende en vorige. Hierbij is wel hard gecodeerd dat je eerst horizontal = 'f' krijgt en dan de 't'.

[Wolfboy]

KennieNL schreef op maandag 20 september 2010 @ 10:37:
[...]


Hmm, moet ik maar eens postgresql update naar 8.4

Ik sta tevens open voor andere suggesties

Wat dacht je van ReenL z'n limit/offset of mijn query met beide parameters om te filteren?

Beiden moeten werken al wordt een limit/offset oplossing wel traag met een grote offset.

[P_de_B]

Wolfboy schreef op maandag 20 september 2010 @ 12:03:
[...]
Wat dacht je van ReenL z'n limit/offset of mijn query met beide parameters om te filteren?

Beiden moeten werken al wordt een limit/offset oplossing wel traag met een grote offset.

En hoe krijg je dan record 102 als je huidige 89 is?

[ReenL]

PHP:

<?php
// ranzige code
$offset = ctype_digit($_GET['offset']) ? $_GET['offset'] : 0;

$sql = "SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT 1 OFFSET ".$offset

echo '<a href="?offset='.($offset+1).'">Volgende</a>';
echo '<a href="?offset='.($offset-1).'">Vorige</a>';

[Wolfboy]

P_de_B schreef op maandag 20 september 2010 @ 12:05:
[...]

En hoe krijg je dan record 102 als je huidige 89 is?

Ik was niet duidelijk genoeg blijkbaar

Ik doelde op een tuple vergelijking:

SQL:

WHERE (horizontal, photo_id) > (x, y)

[cariolive23]

KennieNL schreef op maandag 20 september 2010 @ 10:37:
[...]


Hmm, moet ik maar eens postgresql update naar 8.4

Ik sta tevens open voor andere suggesties

Neem dan direct de stap naar versie 9.0, die is vandaag uitgegeven.

http://www.postgresql.org/about/press/presskit90

[Wolfboy]

Wolfboy schreef op maandag 20 september 2010 @ 16:29:
[...]
Ik was niet duidelijk genoeg blijkbaar

Ik doelde op een tuple vergelijking:

SQL:

WHERE (horizontal, photo_id) > (x, y)

Goed... tuple vergelijkingen werken in Postgres niet zoals ik verwacht had... in dat geval dus een row_number() gebruiken:

SQL:

SELECT
  ROW_NUMBER() OVER (ORDER BY horizontal, photo_id)