#1541: Field Wildcards in Query groupBy
-----------------------------------+----------------------------------------
Reporter: craig | Owner: jwage
Type: enhancement | Status: new
Priority: minor | Milestone:
Component: Attributes | Version: 1.0.2
Keywords: | Has_test: 0
Mystatus: Pending Core Response | Has_patch: 0
-----------------------------------+----------------------------------------
I'm a PostgreSQL user, and as such, this doesn't work for me (I hope I
have the YAML right; it's intended to be a simple one-to-many
relationship):
Parent:
columns:
pk_id:
type: integer(4)
autoincrement: true
primary: true
field2:
type: string(255)
field3:
type: string(255)
attributes:
export: all
validate: true
Child:
columns:
pk_id:
type: integer(4)
autoincrement: true
primary: true
fk_parentid:
type: integer(4)
field2:
type: string(255)
relations:
Parent:
class: Parent
local: fk_parentid
foreign: pk_id
type: one
$data = Doctrine_Query::create()->select( "p.*, COUNT( c.pk_id) AS
num_children_matched")
->from( "Parent p, p.Child c")
->where( "c.field2 = ?", array( $var))
->groupBy( "p.pk_id")
->execute();
It generates a SQL error:
ERROR: column "p.field2" must appear in the GROUP BY clause or be used in
an aggregate function
Adding field2 to the groupBy causes the error to change to field3, until
I've manually added every field in the parent table to the groupBy clause.
I understand that the original example works in MySQL. I haven't tested
others.
I'm annoyed that Postgres requires this. Since p.pk_id is a unique
constrait, adding the remaining columns to the groupBy clause is
redundant.
Since this is a simplified example, and the real table I'm querying has
more columns, I'd prefer to write:
$query->groupBy( "p.*"); // Changed from ->groupBy( "p.pk_id")
I've attached a patch and test case. I realize this isn't a good patch;
it has several side effects that I don't really like. For example, with
this patch it's possible to do:
$query->addWhere( "p.* = 1");
Doctrine will accept that, but the SQL parsing will fail later, since the
"p.* = 1" is expanded into the non-parseable "p.pk_id, p.field2, p.field3
= 1".
If anybody has some suggestions and/or desire to see this submitted, I'll
clean it up and re-attach it.
--
Ticket URL: <http://trac.phpdoctrine.org/ticket/1541>
Doctrine <http://www.phpdoctrine.org>
PHP Doctrine Object Relational Mapper
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"doctrine-svn" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.co.uk/group/doctrine-svn?hl=en-GB
-~----------~----~----~----~------~----~------~--~---
