Come together with the global Drupal community in Rotterdam, 28 Sept – 1 Oct 2026. Sessions, contribution, connection, and Early Bird savings until 8 June.Hello,
http://www.mysite.com/admin/store/reports/products
fails with the error:
# Query failed: ERREUR: la colonne « nc.totalcount » doit apparaître dans la clause GROUP BY ou doit être utilisé dans une fonction d'agrégat LINE 1: ...IN ('completed') AND uop.nid = n.nid) AS revenue, nc.totalco... ^ in /home/html/********/www/includes/database.pgsql.inc on line 139.
# user warning: query: SELECT n.nid, n.title, (SELECT SUM(uop.qty) FROM uc_order_products AS uop LEFT JOIN uc_orders AS uo ON uop.order_id = uo.order_id WHERE uo.order_status IN ('completed') AND uop.nid = n.nid) AS sold, (SELECT (SUM(uop.price * uop.qty) - SUM(uop.cost * uop.qty)) FROM uc_order_products AS uop LEFT JOIN uc_orders AS uo ON uop.order_id = uo.order_id WHERE uo.order_status IN ('completed') AND uop.nid = n.nid) AS gross, (SELECT (SUM(uop.price * uop.qty)) FROM uc_order_products AS uop LEFT JOIN uc_orders AS uo ON uop.order_id = uo.order_id WHERE uo.order_status IN ('completed') AND uop.nid = n.nid) AS revenue, nc.totalcount FROM node as n LEFT JOIN node_counter AS nc ON n.nid = nc.nid WHERE n.type IN ('product', 'license') GROUP BY n.nid, n.title ORDER BY revenue DESC LIMIT 40 OFFSET 0 in
The correct SQL query would be:
SELECT n.nid, n.title, (SELECT SUM(uop.qty) FROM uc_order_products AS uop LEFT JOIN uc_orders AS uo ON uop.order_id = uo.order_id WHERE uo.order_status IN ('completed') AND uop.nid = n.nid) AS sold, (SELECT (SUM(uop.price * uop.qty) - SUM(uop.cost * uop.qty)) FROM uc_order_products AS uop LEFT JOIN uc_orders AS uo ON uop.order_id = uo.order_id WHERE uo.order_status IN ('completed') AND uop.nid = n.nid) AS gross, (SELECT (SUM(uop.price * uop.qty)) FROM uc_order_products AS uop LEFT JOIN uc_orders AS uo ON uop.order_id = uo.order_id WHERE uo.order_status IN ('completed') AND uop.nid = n.nid) AS revenue, nc.totalcount FROM node as n LEFT JOIN node_counter AS nc ON n.nid = nc.nid WHERE n.type IN ('product', 'license') GROUP BY n.nid, n.title,nc.totalcount ORDER BY revenue DESC LIMIT 40 OFFSET 0
Line 189 should be:
$products = pager_query("SELECT $sql_vars FROM $sql_tables WHERE $sql_conditions GROUP BY n.nid, n.title,nc.totalcount" . tablesort_sql($header), $page_size, 0, $sql_count);
I don't have time to generate a patch, please apply this very simple fix...
Comments
Comment #1
grub3 commentedHoops ...
http://www.mysite.com/admin/store/reports/products/custom
fails with the same error:
*
Line 366 should be:
$sql = "SELECT n.nid, n.title, c.totalcount, (SELECT SUM(qty) FROM {uc_order_products} AS p LEFT JOIN {uc_orders} AS o ON p.order_id = o.order_id WHERE o.order_status IN $order_statuses AND p.nid = n.nid AND $time_condition) AS sold, (SELECT (SUM(p2.price * p2.qty)) FROM {uc_order_products} AS p2 LEFT JOIN {uc_orders} AS o ON p2.order_id = o.order_id WHERE o.order_status IN $order_statuses AND p2.nid = n.nid AND $time_condition) AS revenue, (SELECT (SUM(p3.price * p3.qty) - SUM(p3.cost * p3.qty)) FROM {uc_order_products} AS p3 LEFT JOIN {uc_orders} AS o ON p3.order_id = o.order_id WHERE o.order_status IN $order_statuses AND p3.nid = n.nid AND $time_condition) AS gross FROM {node} AS n LEFT JOIN {node_counter} AS c ON n.nid = c.nid WHERE type IN (". implode(", ", $product_types) .") GROUP BY n.nid, n.title, c.totalcount";I only modified GROUP BY n.nid, n.title, c.totalcount.
Comment #2
tr commentedTagging.
Comment #3
tr commentedWell please consider that other people are pressed for time too, and by not making a patch you are asking others to put in FAR more work than you, since we have to comb through your text and try to figure out what you've changed (and what files you've changed too - you don't say...) then translate those changes into the current version of Ubercart. Without a patch we have no idea what version of -dev you're working from. And really, it's too much effort to do a
diff -up uc_reports.module uc_reports.module.orig? Don't expect others to rush to fix things that you yourself aren't willing to put some work into.That said, your first fix doesn't work. When the statistic module is disabled or you have content views statistics turned off, $sql_vars won't contain nc.totalcount. And if nc.totalcount is not one of the columns selected in the query, you can't use it in your GROUP BY.
For your second fix it seems you've also removed the DESC that was part of the pgsql query. I don't see any need to maintain separate queries for mysql and pgsql here, so any fix should apply to both. Also, I think this second query will fail in the same way under the same conditions as the first, although this second query doesn't do any checking for the statistics module.
I'm not running PostgreSQL so I can't test this. However, if you provide a patch and make it easy for others to test, then perhaps we'll get this finished.
Comment #4
tr commentedRemoving tag.
Comment #5
tr commentedThis issue is waiting for a PostgreSQL user who is willing to create a patch.
Comment #6
tr commented