I am trying to filter data based upon the value of an associated table, it works fine when i do a find all but when i try to search by name i get a column does not exist error
i have set up a hook like explained in https://github.com/feathersjs-ecosystem/feathers-sequelize#associations
In postman i can see the association as a subkey but i cannot search by any columns
fetching code
service.find({
query: {
include: ["policyholders"],
Name: {
$like: `%somename%`,
},
},
});
policies model
// See http://docs.sequelizejs.com/en/latest/docs/models-definition/
// for more of what you can do here.
const Sequelize = require("sequelize");
const DataTypes = Sequelize.DataTypes;
module.exports = function (app) {
const sequelizeClient = app.get("sequelizeClient");
const policies = sequelizeClient.define(
"policies",
{
PolicyId: {
autoIncrement: true,
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true,
},
PolicyNumber: {
type: DataTypes.STRING(30),
allowNull: true,
},
PolicyHolderId: {
type: DataTypes.INTEGER,
allowNull: true
},
},
{
hooks: {
beforeCount(options) {
options.raw = true;
},
},
}
);
// eslint-disable-next-line no-unused-vars
policies.associate = function (models) {
// Define associations here
// See http://docs.sequelizejs.com/en/latest/docs/associations/
const { policyholders } = models;
policies.belongsTo(policyholders, { foreignKey: "PolicyholderId" });
};
return policies;
};
policyholders model
// See http://docs.sequelizejs.com/en/latest/docs/models-definition/
// for more of what you can do here.
const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;
module.exports = function (app) {
const sequelizeClient = app.get('sequelizeClient');
const policyholders = sequelizeClient.define(
'policyholders',
{
PolicyholderId: {
autoIncrement: true,
type: DataTypes.INTEGER,
allowNull: false,
primaryKey: true,
},
Name: {
type: DataTypes.STRING(250),
allowNull: true,
},
},
{
hooks: {
beforeCount(options) {
options.raw = true;
},
},
}
);
// eslint-disable-next-line no-unused-vars
policyholders.associate = function (models) {
// Define associations here
// See http://docs.sequelizejs.com/en/latest/docs/associations/
const { policies } = models;
policyholders.hasMany(policies, { foreignKey: { name: 'PolicyholderId' } });
};
return policyholders;
};
policies hook
const { authenticate } = require('@feathersjs/authentication').hooks;
const getRelated = require('../../hooks/get-related');
module.exports = {
before: {
all: [ authenticate('jwt') ],
find: [getRelated()],
get: [getRelated()],
create: [],
update: [],
patch: [],
remove: []
},
after: {
all: [],
find: [],
get: [],
create: [],
update: [],
patch: [],
remove: []
},
error: {
all: [],
find: [],
get: [],
create: [],
update: [],
patch: [],
remove: []
}
};
hooks get related
module.exports = function (options = {}) {
return async (context) => {
const { include, ...query } = context.params.query;
if (include) {
const AssociatedModel = context.app.services.policyholders.Model;
context.params.sequelize = {
include: [{ model: AssociatedModel }],
raw: false
};
// Update the query to not include `include`
context.params.query = query;
}
return context;
};
};
The answer is too
"$policyholder.Name%": {
$like: `%somename%`,
},
Then in the feathers service add the string to whitelist array