[PGSQL] WHERE na een ORDER?

Pagina: 1
Acties:

Onderwerpen


Acties:
  • 0 Henk 'm!

  • KennieNL
  • Registratie: Mei 2007
  • Laatst online: 08-07 11:07
Momenteel heb ik een query om een lijst van fotos op te halen uit een DB:

SQL:
1
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 :P


Ohja, het gaat hier om Postgresql 8.3.11

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


Acties:
  • 0 Henk 'm!

  • ValHallASW
  • Registratie: Februari 2003
  • Niet online
Je hebt geen LIMIT in je query? Gewoon een tweede rij ophalen dan.

Acties:
  • 0 Henk 'm!

  • KennieNL
  • Registratie: Mei 2007
  • Laatst online: 08-07 11:07
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? ;)

Acties:
  • 0 Henk 'm!

  • ValHallASW
  • Registratie: Februari 2003
  • Niet online
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 ]


Acties:
  • 0 Henk 'm!

  • KennieNL
  • Registratie: Mei 2007
  • Laatst online: 08-07 11:07
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 ]


Acties:
  • 0 Henk 'm!

  • The Eagle
  • Registratie: Januari 2002
  • Laatst online: 12:15

The Eagle

I wear my sunglasses at night

Dubbel ophalen :)
je krijgt dan zoiets:
code:
1
2
3
4
5
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 ]

Al is het nieuws nog zo slecht, het wordt leuker als je het op zijn Brabants zegt :)


Acties:
  • 0 Henk 'm!

  • ValHallASW
  • Registratie: Februari 2003
  • Niet online
Hoe kom je aan foto 99? Kan je niet tegelijk met foto 99 óók foto 103 ophalen?

Acties:
  • 0 Henk 'm!

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".

Acties:
  • 0 Henk 'm!

  • KennieNL
  • Registratie: Mei 2007
  • Laatst online: 08-07 11:07
@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

Acties:
  • 0 Henk 'm!

Anoniem: 26306

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

Acties:
  • 0 Henk 'm!

  • Killemov
  • Registratie: Januari 2000
  • Laatst online: 28-05 21:29

Killemov

Ik zoek nog een mooi icooi =)

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.

Hey ... maar dan heb je ook wat!


Acties:
  • 0 Henk 'm!

  • Wolfboy
  • Registratie: Januari 2001
  • Niet online

Wolfboy

ubi dubium ibi libertas

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 ]

Blog [Stackoverflow] [LinkedIn]


Acties:
  • 0 Henk 'm!

  • Remus
  • Registratie: Juli 2000
  • Laatst online: 15-08-2021
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 ]


Acties:
  • 0 Henk 'm!

  • KennieNL
  • Registratie: Mei 2007
  • Laatst online: 08-07 11:07
The Eagle schreef op zondag 19 september 2010 @ 23:10:
Dubbel ophalen :)
je krijgt dan zoiets:
code:
1
2
3
4
5
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? :)

Acties:
  • 0 Henk 'm!

  • Voutloos
  • Registratie: Januari 2002
  • Niet online
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. :z Waarschijnlijk wil je ook iets als a.horizontal = b.horizontal.

{signature}


Acties:
  • 0 Henk 'm!

  • P_de_B
  • Registratie: Juli 2003
  • Niet online
SQL:
1
2
3
4
5
6
7
8
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 ]

Oops! Google Chrome could not find www.rijks%20museum.nl


Acties:
  • 0 Henk 'm!

  • KennieNL
  • Registratie: Mei 2007
  • Laatst online: 08-07 11:07
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.

Acties:
  • 0 Henk 'm!

  • P_de_B
  • Registratie: Juli 2003
  • Niet online
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?

Oops! Google Chrome could not find www.rijks%20museum.nl


Acties:
  • 0 Henk 'm!

  • jbdeiman
  • Registratie: September 2008
  • Laatst online: 08:07
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 ]


Acties:
  • 0 Henk 'm!

  • KennieNL
  • Registratie: Mei 2007
  • Laatst online: 08-07 11:07
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.

Acties:
  • 0 Henk 'm!

  • ReenL
  • Registratie: Augustus 2010
  • Laatst online: 14-09-2022
PostgreSQL:
code:
1
LIMIT 1 OFFSET 0

de "volgende" is dan
code:
1
LIMIT 1 OFFSET 1

etc

Acties:
  • 0 Henk 'm!

  • P_de_B
  • Registratie: Juli 2003
  • Niet online
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 ]

Oops! Google Chrome could not find www.rijks%20museum.nl


Acties:
  • 0 Henk 'm!

  • KennieNL
  • Registratie: Mei 2007
  • Laatst online: 08-07 11:07
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.

Acties:
  • 0 Henk 'm!

  • P_de_B
  • Registratie: Juli 2003
  • Niet online
Zie mijn edit, Postgres heeft hier een functie voor.

Oops! Google Chrome could not find www.rijks%20museum.nl


Acties:
  • 0 Henk 'm!

  • KennieNL
  • Registratie: Mei 2007
  • Laatst online: 08-07 11:07
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

Acties:
  • 0 Henk 'm!

  • P_de_B
  • Registratie: Juli 2003
  • Niet online
Zoiets zou ook nog kunnen, niet echt fraai maar als je het echt aan de sql kant moet oplossen werkt het wel.
SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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'.

Oops! Google Chrome could not find www.rijks%20museum.nl


Acties:
  • 0 Henk 'm!

  • Wolfboy
  • Registratie: Januari 2001
  • Niet online

Wolfboy

ubi dubium ibi libertas

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 :P
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.

Blog [Stackoverflow] [LinkedIn]


Acties:
  • 0 Henk 'm!

  • P_de_B
  • Registratie: Juli 2003
  • Niet online
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?

Oops! Google Chrome could not find www.rijks%20museum.nl


Acties:
  • 0 Henk 'm!

  • ReenL
  • Registratie: Augustus 2010
  • Laatst online: 14-09-2022
PHP:
1
2
3
4
5
6
7
8
<?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>';

Acties:
  • 0 Henk 'm!

  • Wolfboy
  • Registratie: Januari 2001
  • Niet online

Wolfboy

ubi dubium ibi libertas

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 :P

Ik doelde op een tuple vergelijking:
SQL:
1
WHERE (horizontal, photo_id) > (x, y)

Blog [Stackoverflow] [LinkedIn]


Acties:
  • 0 Henk 'm!

  • cariolive23
  • Registratie: Januari 2007
  • Laatst online: 18-10-2024
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 :P
Neem dan direct de stap naar versie 9.0, die is vandaag uitgegeven.

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

Acties:
  • 0 Henk 'm!

  • Wolfboy
  • Registratie: Januari 2001
  • Niet online

Wolfboy

ubi dubium ibi libertas

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

Ik doelde op een tuple vergelijking:
SQL:
1
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:
1
2
SELECT
  ROW_NUMBER() OVER (ORDER BY horizontal, photo_id)

Blog [Stackoverflow] [LinkedIn]

Pagina: 1