Using Postgresql 8.1

does a query more like

SELECT count(n.nid) FROM node n INNER JOIN acidfree on n.nid = acidfree.aid INNER JOIN acidfree_hierarchy ON acidfree_hierarchy.child = acidfree.aid WHERE parent = 5 GROUP BY weight, ASC aid DESC ORDER BY weight ASC, aid DESC

help any? I don't have a postgress server to test this on and am not a real SQL guru (I know enough to get myself in trouble, obviously).

I don't suppose you ever got this working? I attempted to use PostgreSQL 8 and 7 with Acidfree 4.7.0 on Drupal 4.7.2 and produced the same error. I did try to edit the acidfree.module file and add the GROUP BY clause into the particular query which did make the error go away, but ran into a significant number of errors when trying to actually post a photo.

vhmauery where should i put that query of the sql?...

The problem stems from appending the return of _acidfree_content_sort_clause() to a query using COUNT(). There is no reason to sort the results of a query when simply counting the number of rows (and in fact postgresql will not allow it without an aggregate function on the sort column). The problem can be solved by finding all queries that use COUNT() and removing the _acidfree_content_sort_clause() value appended to them.

In some cases, it is not possible to just remove the _acidfree_content_sort_clause(). Sometimes the count we want is the offset into the parent album, which means, we need to have a count of the sorted order of a selection from the parent album. What I need help on is to tell me where to put the 'group by' clause. It doesn't complain with mysql and I don't have a postgres server to play with. So does the above query work? (hint, you might have to change the parent=5 clause to something else (5 was the nid of an album from the original report, so just replace 5 with any album id))

Or, tell me how to rewrite the statement above to make it work right.

Hi, i have PostgreSQL 7.4 on my web server. I have modiffied this query:

    // Create count query. Drupal's pager function does not build the query properly
    // Modified for compatibility with PostgreSQL by Havran ( GROUP BY {acidfree}.weight, {acidfree}.aid, n.nid )
    $clauses = "FROM {node} n INNER JOIN {acidfree} on n.nid = {acidfree}.aid ". 
        "INNER JOIN {acidfree_hierarchy} ON {acidfree_hierarchy}.child = {acidfree}.aid ".
        "WHERE (moderate = 0 AND status = 1) AND parent = {$node->nid} $where_class GROUP BY {acidfree}.weight, {acidfree}.aid, n.nid ".
        _acidfree_content_sort_clause();
 

and now query work without errors.
I use CVS version Acidfree /* $Id: acidfree.module,v 1.91 2006/07/07 20:08:10 vhmauery Exp $ */ on Drupal 4.7.3
This maybe need test with MySQL.

Ok, query works without errors but return bad number :). I don't have better solution, only this -> node/78752. Now seems all in Acidfree module working correct. In this node/78749 i made minor changes for filemanager.module (corresponding with acidfree).

havran - thank you for the info - I was just removing the order by clause. I've attached a patch even though you say it isn't returning the correct number at this point it's better than nothing. Also are you having any problems with the following error, or was it addressed in one of your other patches? Thanks for the help... I wish postgreSQL was better supported. To those MySQL guys - it's REALLY easy to install and test! ;)

Here is the error I'm still getting on the edit pages:

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: argument of AND must be type boolean, not type integer in /Users/Shared/Localhost/drupal/includes/database.pgsql.inc on line 84.

    * user warning: query: SELECT n.nid FROM node n INNER JOIN acidfree on n.nid = acidfree.aid INNER JOIN acidfree_hierarchy ON acidfree_hierarchy.child = acidfree.aid WHERE (n.moderate = 0 AND n.status = 1) AND parent = 55 AND 1 GROUP BY acidfree.weight, acidfree.aid, n.nid ORDER BY weight ASC, aid DESC in /Users/Shared/Localhost/drupal/includes/database.pgsql.inc on line 103.

I'm having problems with this coding convention all over the place... here for example: http://drupal.org/node/78612
It seems like since MySQL is more flexible about working with strange code, that we all should be coding for postgreSQL first.

Thanks!

Finnaly this is better way (in function _acidfree_get_children()):

    // Create count query. Drupal's pager function does not build the query properly
    // Modified for compatibility with PostgreSQL by Havran ( change place for _acidfree_content_sort_clause() function )
    $clauses = "FROM {node} n INNER JOIN {acidfree} on n.nid = {acidfree}.aid ". 
        "INNER JOIN {acidfree_hierarchy} ON {acidfree_hierarchy}.child = {acidfree}.aid ".
        "WHERE (moderate = 0 AND status = 1) AND parent = {$node->nid} $where_class ";
    $query = db_rewrite_sql("SELECT n.nid " . $clauses . _acidfree_content_sort_clause());
    $count_query = db_rewrite_sql("SELECT count(n.nid) " . $clauses);

I move _acidfree_content_sort_clause() function from $clauses in to db_rewrite_sql().

Thanks for the push. I finally had to install my own postgres server so I could test it out myself. I came up with a simpler solution, which, might even fix mysql 3.x installations as well. The fix is in DRUPAL-4-7 branch of CVS, so check it out or wait a while until it gets repackaged (version 1.62.2.42)