Announcement

#1 2011-01-01 18:14:52

roeleveld
Member
2011-01-01
5

Postgresql - "ORDER BY" issue

When trying to access any picture, I get the following error message:

--
Warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 8: ORDER BY date_available DESC, file ASC, id ASC ^ in /var/www/admin/htdocs/piwigo/include/dblayer/functions_pgsql.inc.php on line 122
SELECT DISTINCT(image_id) FROM piwigo_image_category INNER JOIN piwigo_images ON id = image_id WHERE category_id = 2 ORDER BY date_available DESC, file ASC, id ASC ;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 8: ORDER BY date_available DESC, file ASC, id ASC ^
--

I managed to "resolve" this by changing the following file:
piwigo/include/config_default.inc.php

Line: 65
Original: $conf['order_by'] = ' ORDER BY date_available DESC, file ASC, id ASC'
New: $conf['order_by'] = '';

Can anyone recommend a better solution to this issue?

Thanks,

Joost

Offline

 

#2 2011-01-03 08:41:16

Rodger
Guest

Re: Postgresql - "ORDER BY" issue

I suspect the only real fix would be to replace instances of "select distinct(foo) ... order by bar" with "select foo ... group by foo order by bar", which would be a more postgresql (and probably ANSI) compliant way to do it.  Having only just installed piwigo I am not adequately familiar with the code base to work out where those changes need to be made 8).

 

#3 2011-01-03 16:29:51

LucMorizur
Member
Vienne (Isère) - France
2009-04-30
168

Re: Postgresql - "ORDER BY" issue

Rodger wrote:

I suspect the only real fix would be to replace instances of "select distinct(foo) ... order by bar" with "select foo ... group by foo order by bar", which would be a more postgresql (and probably ANSI) compliant way to do it.  Having only just installed piwigo I am not adequately familiar with the code base to work out where those changes need to be made 8).

The code base is quite simple as for its structure (I can understand it :-p !). But SQL queries are programmed "in hard" in it, thus you would need to change a lot of code so to replace all queries including a ORDER BY command.

Piwigo is originally based on MySQL; PostgreSQL integration started very recently. Thus it will be very useful to the Team to have feedback on this domain, cause it is known that still several changes are needed to properly use PostgreSQL :-) .


Our gallery : Le Site à Nous (a silly name in french, but here I don't care ;-) ! )
An event, a new gallery ? Plugin Event Cats
My test gallery : Tests Piwigo de Luc
Thanksalot for this beautiful project.

Offline

 

#4 2011-01-03 23:27:14

mathiasm
Former Piwigo Team
2006-02-06
2648

Re: Postgresql - "ORDER BY" issue

The syntax seems to be select distinct on:
http://www.postgresql.org/docs/8.1/stat … lists.html

If you can confirm it, we may come with a $sqldistinct variable which will ocntain the various syntaxes depending on the db module loaded.

regards

Offline

 

#5 2011-01-04 08:01:46

roeleveld
Member
2011-01-01
5

Re: Postgresql - "ORDER BY" issue

The issue is caused by trying to use "order by" with fields that are not in the select-statement.

The fields in the "order by" need to be added to the select-statement.
What I really need to know is if the code can handle the additional fields correctly?
If yes, then a clever piece of code can automatically add these fields to the select.

Offline

 

#6 2011-02-11 12:57:01

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13180

Re: Postgresql - "ORDER BY" issue

roeleveld, which version of Piwigo are you using? (because the DISTINCT(id) was added in Piwigo 2.1.6 and Piwigo 2.1.6 was released 23 days after your topic)


Latest blog post (November 9th 2018) Why Flickr could not remain free for ever

Offline

 

#7 2011-02-11 13:13:29

roeleveld
Member
2011-01-01
5

Re: Postgresql - "ORDER BY" issue

Hi plg,

I tested this with the, at that time, most recent release I could find on the website: 2.1.5.

Offline

 

Board footer

Powered by FluxBB

github twitter facebook google+ newsletter Donate Piwigo.org © 2002-2019 · Contact