Orderby in subquery missing

[oscaroox]

It seems that this plugin strips the order by in subqueries.

with plugin:

select `tour`.*, (select `price` from `booking_slot` where `tour_id` = `tour`.`id` limit ?) as `lowestPrice` from `tour`

without plugin:

select `tour`.*, (select `price` from `booking_slot` where `tour_id` = `tour`.`id` order by `price` asc limit ?) as `lowestPrice` from `tour`'

Here's a reproducible demo

const cursorMixin = require('objection-cursor');
const {Model, ref, compose} = require('objection');

const mixins = compose(
    cursorMixin({
        limit: 10,
        pageInfo: {
            // total: true,
            hasNext: true,
            hasPrevious: true,
        },
    }),
);

class BaseModel extends mixins(Model) {
}

const Knex = require('knex');

async function main() {
    await createSchema();

    await Tour.query().insertGraph({
        title: 'Tour title',

        bookingSlots: [
            {
                price: 9,
            },
            {
                price: 5,
            },
            {
                price: 2,
            }
        ]
    });

    await Tour.query().select(["tour.*", 
        BookingSlot.query()
            .select('price')
            .where('tour_id', ref('tour.id'))
            .orderBy('price', 'asc')
            .limit(1)
            .as('lowestPrice')
    ]);
}

///////////////////////////////////////////////////////////////
// Database
///////////////////////////////////////////////////////////////

const knex = Knex({
    client: 'sqlite3',
    useNullAsDefault: true,
    debug: true,
    connection: {
      filename: ':memory:'
    }
});

Model.knex(knex);

///////////////////////////////////////////////////////////////
// Models
///////////////////////////////////////////////////////////////

class Tour extends BaseModel {
    static get tableName() {
        return 'tour';
    }

    static get relationMappings() {
        return {
            bookingSlots: {
                relation: Model.HasManyRelation,
                modelClass: BookingSlot,
                join: {
                from: 'tour.id',
                to: 'booking_slot.tour_id'
                }
            },
        };
    }
}

class BookingSlot extends BaseModel {
    static get tableName() {
        return 'booking_slot';
    }
}

///////////////////////////////////////////////////////////////
// Schema
///////////////////////////////////////////////////////////////

async function createSchema() {
    await knex.schema
        .dropTableIfExists('tour')
        .dropTableIfExists('booking_slot');

    await knex.schema
        .createTable('tour', table => {
            table.increments('id').primary();
            table.string('title');
        })
        .createTable('booking_slot', table => {
            table.increments('id').primary();
            table.string('price');
            table
                .integer('tour_id')
                .unsigned()
                .references('id')
                .inTable('tour');
        });
}

main()
.then(() => {
    console.log('success')
    return knex.destroy()
})
.catch(err => {
    console.error(err)
    return knex.destroy()
});

[olavim]

Thanks for the issue. Which version are you using? I assume you're running on version 1.1.0 or earlier? Could you try version 1.2.0-alpha.2, which seems to work for me.

[oscaroox]

Yes I'm using 1.1.0. I will try again with 1.2.0-alpha.2 sometime today

[oscaroox]

Yes it works with 1.2.0-alpha.2. When will you release this version?

[olavim]

Released version 1.2.0 now.