allow objects in `.orderBy`

[Janpot]

If you want to order your result by 1 category at the top you can do something like

SELECT * FROM users ORDER BY country = 'US' DESC

or even

SELECT * FROM users ORDER BY country IN ('US', 'CA') DESC

It would be nice if we could specify these in an object like so

sql.select().from('users').orderBy({ country: 'US' });
sql.select().from('users').orderBy({ country: ['US', 'CA'] });

(There also doesn't seem to be a way to describe DESC or ASC)

now I have to do

sql.select().from('users').orderBy('country = "US" DESC');
sql.select().from('users').orderBy('country IN ("US", "CA") DESC');

[prust]

@Janpot,

Expressions aren't allowed in ORDER BY clauses in SQL 92; from the spec:

<order by clause> ::=
              ORDER BY <sort specification list>

         <sort specification list> ::=
              <sort specification> [ { <comma> <sort specification> }... ]

         <sort specification> ::=
              <sort key> [ <collate clause > ] [ <ordering specification> ]

         <sort key> ::=
                <column name>
              | <unsigned integer>

         <ordering specification> ::= ASC | DESC
...
         <collate clause> ::= COLLATE <collation name>

Expressions are allowed in SQLite and Postgresql; I'm not sure if they're included in a SQL standard after 1992 or if they're only allowed in engine-specific dialects.

I am committed to providing comprehensive support for SQL-92 (see #29 for an earlier -- and now deprecated -- discussion of asc()/desc() support), so I'll go ahead & make the following changes to the API:

Allow all the details for every column to be set by passing objects to .orderBy():

.orderBy({column: 'age', order: 'DESC'}, {column: 'name', collate: 'Latin1_General_CI_AS'})

This is how the data will be stored internally in SQLBricks (an _orderBy array of objects, each having a column property and optional order and collate properties).

Allow the most recent column to be modified by .desc(), .asc() and .collate() methods. So the above could also be written as:

.orderBy('age').desc().orderBy('name').collate('Latin1_General_CI_AS')

Note that in SQL-92, columns specified in a GROUP BY can also take a collation (but not a DESC/ASC order and, unlike ORDER BY, they cannot be specified by a 1-based unsigned integer representing the column index). So .groupBy() will need similar treatment (the ability to take an array of objects and for those objects to be modified by the .collate() method).

This complicates the implementation a little, because in the case of .groupBy('age').collate('Latin1_General_CI_AS'), the .collate() will need to modify the most recent groupBy column, not the most recent orderBy column. This kind of ambiguity is a natural result of the simple, single-level chaining API; it's not the first time it has happened and it won't be the last. To implement this, I'll store an internal _collateTarget property which will be an '_orderBy' or '_groupBy' string or a falsey value (undefined).

Regarding ORDER BY expression support (and other non-SQL-92 features): It has become clear to me that even fully supporting the two dialects that I use (SQLite and Postgres) is more than I want to do. Not only are they large specs (especially Postgres), but the specs change slightly with every new version of the respective libraries. Instead, I will focus (almost) exclusively on ensuring that I have comprehensive support for SQL-92 and I will create a robust extension mechanism (#36) so other can add (and maintain!) support for various dialects and for SQL-95, SQL-2000, etc. I recently did a major refactor (#38) to lay the groundwork for this. There are already a few Postgres and SQLite-specific things here; these will be moved out into separate files, which others can use as the beginning of Postgres and SQLite-specific dialect extensions.

[Janpot]

Thanks for implementing a version of this. I'm not that familiar with the spec. I just did this in MySql and assumed it was a standard thing. Will do my research next time.

[prust]

@Janpot: No problem, I wasn't very familiar with the spec before I started this project and I'm still discovering things in it.

Thanks for implementing a version of this

To be clear, I haven't yet implemented the API described above (there currently aren't any .desc(), .asc() or .collate() methods and you can't yet pass an array of objects to .orderBy()). But I should be able to get to it within the next few days (or someone else could implement it in a pull request :smile:)