nested tables on join

[matbalazsi]

Hi,
Using feathers-knex (postgresql), I am trying to nest my tables on a join. Here is an example on how to achieve this with node-postgres.

brianc/node-postgres#733

SELECT to_json(a.*) AS table1, to_json(b.*) AS table2 FROM some_table a LEFT JOIN another_table b ON a.id = b.id;
With this all rows will be structured as such:

{ table1: { ... },
table2: { ... } }

However, using:
const query = this.createQuery({ query: hook.params.query });
query.select(to_json(a.*) AS table1)

is giving me an error because it assumes that the table name is “to_json(a”. Is there a way to generate raw queries with feathers-knex?

[daffl]

You can create raw queries using this.Model which is the db instance you initially passed.

[matbalazsi]

thanks! I wanted to create the join query in a hook, but setting
hook.params.knex = hook.app.service('a').Model.raw('SELECT * FROM etc....');
doesn't work. Would you suggest creating a new custom service for this query, or is it possible through hooks?

[matbalazsi]

I'm closing the issue, thanks for the quick response! If possible, does anyone have any suggestion regarding the best practice for these types of joins (as mentioned in my previous comment)?

[roelvan]

This is what works for me (full before hook example):

const { mapKeys } = require('lodash');

module.exports = (options = {}) => { // eslint-disable-line no-unused-vars
  return async context => {
    // PREVENT: "column reference "[column name]" is ambiguous"
    const sort = mapKeys(context.params.query.$sort, (order, field) => {
      return `plannings.${field}`;
    });
    context.params.query.$sort = sort;
    
    const query = context.service.createQuery({ query: context.params.query });
    query
      .count('plannings.id as planninRuleCount')
      .leftJoin('planningRules as plr', 'plr.planningId', 'plannings.id')
      .groupBy('plannings.id');
    
    if (context.id) // Scope the query if it's a single GET call
      query.where('plannings.id', context.id);
    
    context.params.knex = query;
    return context;
  };
};

[xiaguoli]

you can try like this:

find: [
  function (hook) {
    

    const query = this.db().column('t_wip_type.product_type','t_wip_type.product_type_code','t_wip_type.code_rules','t_wip_code.*').select();

    query.leftOuterJoin('t_wip_type', 't_wip_code.wip_type_id', 't_wip_type.id');

    hook.params.knex = query;

    return hook;

    //console.log(query.toSQL().toNative())
  }],