Following exception gets thrown when i try to access the page "imagepicker/browse_public":

PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "WHERE" LINE 9: WHERE (u.uid = 'iug.uid') AND (iug.public = '1') AND( (iug.... ^: SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {users} u INNER JOIN {imagepicker} i LEFT OUTER JOIN {imagepicker_group_images} g ON g.img_id = i.img_id LEFT OUTER JOIN {imagepicker_user_groups} iug ON iug.gid = g.gid WHERE (u.uid = :db_condition_placeholder_0) AND (iug.public = :db_condition_placeholder_1) AND( (iug.avail_roles = :db_condition_placeholder_2) OR (iug.avail_roles ILIKE :db_condition_placeholder_3) OR (iug.avail_roles ILIKE :db_condition_placeholder_4) )) subquery; Array ( [:db_condition_placeholder_0] => iug.uid [:db_condition_placeholder_1] => 1 [:db_condition_placeholder_2] => all [:db_condition_placeholder_3] => %authenticated user% [:db_condition_placeholder_4] => %blog author% ) in PagerDefault->execute()

What might be interesting is, that I'm running my drupal on a PostgreSQL Database...

Comments

hutch’s picture

hutch commented

Oh dear, postgres can be difficult. Does it throw these errors when you access via "admin/config/media/imagepicker/images/browse_public"?
This uses the same function, it would help narrow it down a bit.

guedressel’s picture

guedressel commented

Nope. No exception at "admin/config/media/imagepicker/images/browse_public".

But there is one at "admin/config/media/imagepicker/images/stats":

PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "WHERE" LINE 7: WHERE (u.uid = 'iug.uid') AND (iug.public = '1') ^: SELECT COUNT(i.img_id) AS ct FROM {users} u INNER JOIN {imagepicker} i LEFT OUTER JOIN {imagepicker_group_images} g ON g.img_id = i.img_id LEFT OUTER JOIN {imagepicker_user_groups} iug ON iug.gid = g.gid WHERE (u.uid = :db_condition_placeholder_0) AND (iug.public = :db_condition_placeholder_1) ; Array ( [:db_condition_placeholder_0] => iug.uid [:db_condition_placeholder_1] => 1 ) in imagepicker_group_stats() (line 2035 of ..../sites/all/modules/imagepicker/imagepicker.functions.inc).

And one at "admin/config/media/imagepicker/groups":

PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "u.name" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ..., g.group_name AS group_name, g.public AS public, u.name AS ... ^: SELECT g.group_name AS ggroup_name, g.gid AS gid, g.uid AS uid, g.group_name AS group_name, g.public AS public, u.name AS name, COUNT(i.img_id) AS ct FROM {imagepicker_user_groups} g LEFT OUTER JOIN {imagepicker_group_images} i ON g.gid = i.gid LEFT OUTER JOIN {users} u ON g.uid = u.uid GROUP BY g.gid ORDER BY g.group_name ASC LIMIT 25 OFFSET 0; Array ( ) in PagerDefault->execute() (line 79 of .../includes/pager.inc).

Thanks for picking up this issue.
Hope my info helps...

hutch’s picture

hutch commented

1)
PDO is treating 'iug.uid' as data, not an aliased field and pgsql is not recognizing 'iug.uid' as a reference to a field as mysql does.
Perhaps (and this is a wild guess) pgsql needs one-letter aliases.
You could try changing, in imagepicker.functions.inc line 2033

    $query->leftjoin('imagepicker_user_groups', 'iug', 'iug.gid = g.gid');
    $query->condition('u.uid', 'iug.uid')->condition('iug.public', 1);

to

    $query->leftjoin('imagepicker_user_groups', 'h', 'h.gid = g.gid');
    $query->condition('u.uid', 'h.uid')->condition('h.public', 1);

Please let me know ASAP what does and does not work with pgsql

2)
I think this will fix it, pgsql has issues with grouping.
Could you please try editing imagepicker.admin.inc, line 932
replace

$query->groupBy('g.gid');

with

$query->groupBy('g.gid')->groupBy('u.name');