feathers-sequelize Left Join Database Postgresql

Question

I am very new at feathersjs, i'm stuck on a database query which uses LEFT JOIN. I have read the sequelizejs, but i'm stuck. I have 2 tables.

Table 1:

                                              Table "public.ek_jabatan"
     Column     |           Type           |                            Modifiers                            
----------------+--------------------------+-----------------------------------------------------------------
 id_jabatan     | integer                  | not null default nextval('ek_jabatan_id_jabatan_seq'::regclass)
 id_instansi    | integer                  | not null
 nama_jabatan   | character varying(100)   | not null
 urutan_jabatan | integer                  | not null
 createdAt      | timestamp with time zone | 
 updatedAt      | timestamp with time zone | 
Indexes:
    "ek_jabatan_pkey" PRIMARY KEY, btree (id_jabatan)

Table 2 :

                                          Table "public.ek_instansi"
    Column     |           Type           |                             Modifiers                             
---------------+--------------------------+-------------------------------------------------------------------
 id_instansi   | integer                  | not null default nextval('ek_instansi_id_instansi_seq'::regclass)
 nama_instansi | character varying(100)   | not null
 lokasi        | character varying(200)   | not null
 createdAt     | timestamp with time zone | 
 updatedAt     | timestamp with time zone | 
Indexes:
    "ek_instansi_pkey" PRIMARY KEY, btree (id_instansi)

In PHP it is not difficult to make Query:

SELECT id_jabatan, a.id_instansi, nama_jabatan, urutan_jabatan, nama_instansi, lokasi 
FROM ek_jabatan a 
LEFT JOIN ek_instansi b 
ON b.id_instansi = a.id_instansi

How to make Query like that on FeatherJS ???

i'have tried,

users.model.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const users = sequelizeClient.define('users', {

    email: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false
    },
    statusId: {
      type: Sequelize.INTEGER,
      field: 'status_id'
    }


  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });

  // eslint-disable-next-line no-unused-vars
  users.associate = function(models){
    users.hasOne(models.userStatus, {
        as: 'status',
        foreignKey: 'id'
    });
};

  return users;
};

user_status.model.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const userStatus = sequelizeClient.define('user_status', {
    name: {
      type: DataTypes.STRING,
      allowNull: true
    }
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });

  userStatus.associate = function (models) {
  };

  return userStatus;
};

users.hooks.js

    before: {
    all: [],
    find: [ 
      authenticate('jwt'),

      context => {
        const sequelize = context.params.sequelize || {};
        sequelize.raw = true;
        sequelize.include = [
          {
            model: context.app.services['userStatus'].Model,
            as: 'status'
          }
        ];
        return context;
      },



    ],
    get: [ authenticate('jwt') ],
    create: [ hashPassword() ],
    update: [ hashPassword(),  authenticate('jwt') ],
    patch: [ hashPassword(),  authenticate('jwt') ],
    remove: [ authenticate('jwt') ]
  },

When npm start, i got error. throw new Error(this.name + '.' + Utils.lowercaseFirst(Type.toString()) + ' called with something that\'s not a subclass of Sequelize.Model');

The problem you are having is more of a sequelize issue. Here is how you do a LEFT JOIN with sequelize: stackoverflow.com/questions/27561915/…. From there, you can read the docs on feathers-sequelize for how to integrate that into your feathers app. — Ryan Wheale
– Ryan Wheale, Commented Nov 29, 2018 at 17:43
I advise reading the docs for findAll - in particular options.include, options.include.where, and options.include.required: docs.sequelizejs.com/class/lib/… — Ryan Wheale
– Ryan Wheale, Commented Nov 29, 2018 at 17:51

Ryan Wheale · Accepted Answer · 2018-11-29 19:44:36Z

1

You're not setting the "context.params.sequelize" correctly in your hook. You need to do something like this:

context => {
  // Make sure the object exists
  if(!context.params.sequelize) {
    context.params.sequelize = {};
  }

  const sequelize = context.params.sequelize;
  sequelize.raw = true;
  sequelize.include = [
    {
      model: context.app.services['userStatus'].Model,
      as: 'status',
      required: false, // Will always ensure LEFT JOIN
    }
  ];
  return context;
}

answered Nov 29, 2018 at 19:44

Ryan Wheale

28.7k11 gold badges87 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

feathers-sequelize Left Join Database Postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related