use group by and order by together

hello,

Here is an example:

select avg(weight) as w , age from person group by age order by w desc;

I have get the result by using 'group by'，and next what about to use 'order by' together?

And more complex，adding "where" after "from":

select avg(weight) as w , age from person where country='US' group by age order by w desc;

I think this is possible:

Person.aggregate([ "age" ], { country: "US" }).avg("weight").groupBy("age").get(function (err, groups) {
    // groups should have "age" and "avg_weight"
});

The only thing still missing is the order.

One thing I will probably allow is to define additional properties after the .aggregate() so the syntax gets more clear to read.

Good! Maybe I shall try to read the source code. I will expect the order.

Coming right out! Just doing the unit tests.

Adds possibility of order to aggregate method (#114)

Adds .select() aggregate function to support additional properties to…

Now this is possible:

Person.aggregate({ country: "US" })
      .select("age") // the previous syntax is still available; to pass more properties just pass an Array or each one as a different argument
      .avg("weight")
      .groupBy("age")
      .order("name", "Z")
.get(function (err, groups) {
    // groups should have "age" and "avg_weight"
});

so efficient.

I'm worrying about that, '.order("name", "Z")' which the 'name' is an existing table column, what about 'order("w", "Z")'? The 'w' is avg("weight") in my example above.

Just something to be aware of: mysql doesn't follow the sql standard and helps you out a bit with invalid queries; any fields which are in the order statement must also be included in the groupBy one.

This is a very common bug, I've seen it come up in every ORM I've ever used, and the internets is littered with examples of this.

Here it's not a huge problem since you can just add .select('name') to the query and it'll work, but I imagine someone will open an issue over this sooner or later.

Thank you for pointing that out. I'll keep that in mind when changing #115 .

Navigation Menu

Repository navigation

use group by and order by together #114

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

use group by and order by together #114

Description

Activity

dresende commented on Apr 11, 2013

dresende commented on Apr 11, 2013

huangyingjie commented on Apr 11, 2013

dresende commented on Apr 11, 2013

dresende commented on Apr 11, 2013

huangyingjie commented on Apr 11, 2013

huangyingjie commented on Apr 11, 2013

dxg commented on Apr 11, 2013

dresende commented on Apr 11, 2013

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions