Hi @patrick,
I’m trying to extend the paged query.
Can you let me know what is the structure of options.sql object so I can reuse as much code as possible from the standard?
Unless I’m better off executing the custom query and calculating the returning values and structure App Connect expects.
Thanks
Anyway I already moved on making the structure match what App Connect expects and using knex for my queries.
exports.anytable = async function(options) {
// connection option is required. In my cased it's fixed to 'db'
const connection = this.parseRequired('db', 'string', 'dbconnector.paged: connection is required.');
// read table name from options
const table = this.parseRequired(options.table, 'string', 'table is required.');
// get the database connection
const db = this.getDbConnection(connection);
if (!db) throw new Error(`Connection "${connection}" doesn't exist.`);
// parse options
if (typeof options.offset != 'number') options.offset = Number(this.parseOptional('{{ $_GET.offset }}', '*', 0));
if (typeof options.limit != 'number') options.limit = Number(this.parseOptional('{{ $_GET.limit }}', '*', 25));
if (typeof options.sort != 'string') options.sort = this.parseOptional('{{ $_GET.sort }}', 'string', null);
if (typeof options.dir != 'string') options.dir = this.parseOptional('{{ $_GET.dir }}', 'string', 'asc');
// get total count of records. It needs to match the same filters you use in your custom query.
let total = await db.select().from(table).count();
let count = parseInt(total[0].count)
// your custom query
let data = await db.select('*').from(table).limit(options.limit).offset(options.offset).orderBy(options.sort, options.dir);
// same response structure expected by App Connect to manage paged queries
return {
offset: options.offset,
limit: options.limit,
total: count,
page: {
offset: {
first: 0,
prev: options.offset - options.limit > 0 ? options.offset - options.limit : 0,
next: options.offset + options.limit < count ? options.offset + options.limit : options.offset,
last: (Math.ceil(count / options.limit) - 1) * options.limit
},
current: Math.floor(options.offset / options.limit) + 1,
total: Math.ceil(count / options.limit)
},
data
}
}
In hindsight I think it was a better and more efficient solution as the options.sql seems to be pretty tied to the standard.
I used this for an old project that didn’t go live so I think I no longer have that file.