support keyword distinct

[huangyingjie]

hello,
Here is another request:

      select distinct country from person;

How to get it?
And Can I execute any sql like hsql in hibernate?

[dresende]

There isn't. I started the project as a way for people to completely avoid using sql but now I'm thinking about having that possibility (with a warning about compatibility if switching to other dbms).

[dresende]

About the distinct, that's the query you want to do (in your example) or a more complex one? I was thinking about the aggregation functions..

[huangyingjie]

just the distinct. Using sql while some functions have not been developped.

[dresende]

This is probably going to appear a little odd, perhaps we can shorten the code in the future. I added DISTINCT to sql-query dependency and updated ORM. Also, added .as() to add aliases to functions instead of the auto-generated (if needed). Here's an example:

Person.aggregate().distinct('country').as('country').groupBy('country').get(function (err, countries) {
    // groupBy is needed or else it will return only the first one
});

As you can see, .groupBy shouldn't be needed since the query with or without it should return the same thing, but ORM returns only one row for aggregate functions when not using .groupBy.

[dresende]

This is because DISTINCT is not actually an aggregate function (I think!) but it works well with them.

[huangyingjie]

Acturally it is a bit odd, we use it temporarily.

[dresende]

You should now be able to do:

Person.aggregate().distinct('country').get(function (err, countries) {
    // countries should be an Array like [ 'country1', 'country2', ... ]
});

[dresende]

If you have a more complex query please post it, I'm not sure how it handles it using more functions.