I'm porting a project (Drupal) from PHP/MySQL to PHP/PEAR atop
PostgresQL. The port has gone well except for one query which has
me a bit flummoxed:
SELECT n.*, l.*, u.uid, u.name, SUM(m.score) / COUNT(m.cid) AS
score, COUNT(m.cid) AS votes FROM node n LEFT JOIN $type l ON
n.lid = l.lid AND n.nid = l.nid LEFT JOIN user u ON n.author =
u.uid LEFT JOIN moderate m ON m.nid = n.nid WHERE $where GROUP BY
n.nid ORDER BY n.timestamp DESC
The value of $where is reasonable and not a problem. The problem is
the "l.*" and "FROM $type l". This is (ab)using one of the non-ANSI
"extensions" that MySQL makes:
--<cut>--
MySQL has extended the use of GROUP BY. You can use columns or
calculations in the SELECT expressions that don't appear in the
GROUP BY part. This stands for any possible value for this
group. You can use this to get better performance by avoiding
sorting and grouping on unnecessary items. For example, you don't
need to group on customer.name in the following query:
mysql> select order.custid,customer.name,max(payments)
from order,customer
where order.custid = customer.custid
GROUP BY order.custid;
In ANSI SQL, you would have to add customer.name to the GROUP BY
clause. In MySQL, the name is redundant if you don't run in ANSI
mode.
Don't use this feature if the columns you omit from the GROUP BY
part aren't unique in the group! You will get unpredictable
results.
--<cut>--
The specific problem above is that $type is determined at runtime
and will point any one of various tables, all of which have nid/lid
values, but whose other fields vary. Thus, given the "l.* FROM
$type l" I can't deterministically fill the fields for the GROUP BY.
This violates ANSI SQL, causes PostgresQL to barf, and presents a
problem.
Are there any sort of standard approaches to resolving this type of
deal? All the approaches I've come up with todate (mostly using a
temp table) have unwelcome side effect of also changing all the
field name which has unwelcome complications for the rest of the
app.
Ideas?
--
J C Lawrence
---------(*) Satan, oscillate my metallic sonatas.
claw@kanga.nu He lived as a devil, eh?
http://www.kanga.nu/~claw/ Evil is a name of a foeman, as I live.