Paged custom query

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.

3 Likes