#1542: Field Wildcards in Query groupBy
--------------------------+-------------------------------------------------
Reporter: craig | Owner: jwage
Type: enhancement | Status: new
Priority: minor | Milestone:
Component: Attributes | Version: 1.0.2
Resolution: | Keywords:
Has_test: 1 | Mystatus: Pending Core Response
Has_patch: 1 |
--------------------------+-------------------------------------------------
Old description:
> 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.
New description:
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/1542#comment:1>
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
-~----------~----~----~----~------~----~------~--~---
