use group by and order by together

[huangyingjie]

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;

[dresende]

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.

[dresende]

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

[huangyingjie]

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

[dresende]

Coming right out! Just doing the unit tests.

[dresende]

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"
});

[huangyingjie]

so efficient.

[huangyingjie]

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.

[dxg]

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.

[dresende]

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