Piwigo.org

roeleveld · 2011-01-01 18:14:52

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

Rodger · 2011-01-03 08:41:16

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

LucMorizur · 2011-01-03 16:29:51

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

mathiasm · 2011-01-03 23:27:14

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

roeleveld · 2011-01-04 08:01:46

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.

plg · 2011-02-11 12:57:01

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)

roeleveld · 2011-02-11 13:13:29

Hi plg,

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

Piwigo.org

Announcement

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

Postgresql - "ORDER BY" issue

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

Re: Postgresql - "ORDER BY" issue

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

Re: Postgresql - "ORDER BY" issue

Rodger wrote:

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

Re: Postgresql - "ORDER BY" issue

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

Re: Postgresql - "ORDER BY" issue

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

Re: Postgresql - "ORDER BY" issue

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

Re: Postgresql - "ORDER BY" issue

Board footer