Add support for using table column in orderBy

[kir4ik]

Hi

Currently, when a column is not being selected but needs to be used for sorting, the only way to do this is by using connection.rawFragment to manually add the column name to the customizeQuery method.

For example:

const {format} = require('mysql');
const {MySqlConnection} = require('ts-sql-query/connections/MySqlConnection');
const {MySqlPoolQueryRunner} = require('ts-sql-query/queryRunners/MySqlPoolQueryRunner');
const {Table} = require('ts-sql-query/Table');

const mysqlPool = {};

class Connection extends MySqlConnection {
  allowEmptyString = true;
}

const createMysqlConnection = () => new Connection(new MySqlPoolQueryRunner(mysqlPool, 'mySql'));

class SomeTable extends Table {
  id = this.autogeneratedPrimaryKey('id', 'int');
  name = this.column('name', 'string');
  order = this.column('order', 'int');

  constructor() {
    super('some_table');
  }
}

const someTable = new SomeTable();

async function main() {
  const connection = createMysqlConnection();

  const q = connection
    .selectFrom(someTable)
    .select({
      id: someTable.id,
      name: someTable.name
    })
    /** 
     * I can use orderBy('id', 'desc) or orderByFromString('id desc')
     * but I can't use orderBy(someTable.order, 'desc')
     */
    .customizeQuery({
      beforeOrderByItems: connection.rawFragment`${someTable.order} desc`
    });

  console.log(format(q.query(), q.params()));
}

main().catch(console.error).finally(() => {});

I think, It would be more convenient and less error-prone if we could use the column directly in the orderBy method, like so:

const q = connection
    .selectFrom(someTable)
    .select({
        id: someTable.id,
        name: someTable.name
    })
    .orderBy(someTable.order, 'desc');

Please consider adding support for ordering by table columns.

Proposed solution: Add support for using table columns in the orderBy method directly, like

orderBy(someTable.order, 'desc')

or a similar method with such support

Thank you

[juanluispaz]

Hi,

this restriction was already removed in ts-sql-query 1.52.0 see details here

Let me know if that works for you.

[kir4ik]

Thank you for letting me know. I'm glad to hear that this issue has been resolved. Thank you for your time and help