Sub include relations with Sequelize: Query erro

[Deithso]

Hello,
I got an error in the generated query by sequelize. (error at the bottom of the post)

What i have ?

i have some relationships as bellow
Activities > Places > Properties
I'm looking for activities by properties
For this i created a custom route named : search where i'm passing an array of properties

Models description

// Activities Model
const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;
module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const activities = sequelizeClient.define('activities', {
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },  
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });
  // eslint-disable-next-line no-unused-vars
  activities.associate = function (models) {
    // Define associations here
    // See http://docs.sequelizejs.com/en/latest/docs/associations/
    activities.hasMany(models.places);
  };
  return activities;
};

// Places Model
const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;
module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const places = sequelizeClient.define('places', {
    address: {
      type: DataTypes.STRING,
      allowNull: false
    }
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });

  // eslint-disable-next-line no-unused-vars
  places.associate = function (models) {
   // Other relationships ...
    places.belongsTo(models.activities);
    places.belongsToMany(models.properties, {
      through: {
        model: models.places_properties,
        unique: false,
      },
      foreignKey: 'placeId',
      constraints: false
    });
  };
  return places;
};

// Properties model
const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

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

  // eslint-disable-next-line no-unused-vars
  properties.associate = function (models) {
    properties.belongsToMany(models.places, {
      through: {
        model: models.places_properties,
        unique: false,
      },
      foreignKey: 'propertyId',
      constraints: false
    });
  };

  return properties;
};

// Places_Properties properties
const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

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

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

  // eslint-disable-next-line no-unused-vars
  placesProperties.associate = function (models) {
    // Define associations here
    // See http://docs.sequelizejs.com/en/latest/docs/associations/
  };

  return placesProperties;
};

Services

// Service Activites
// Initializes the `activities` service on path `/activities`
// Initializes the `activities` service on path `/activities/search`
const createService = require('feathers-sequelize');
const createModel = require('../../models/activities.model');
const hooks = require('./activities.hooks');
const search = require('./search');
const searchHooks = require('./search/hooks.js');
module.exports = function (app) {
  const Model = createModel(app);
  const paginate = app.get('paginate');

  const options = {
    name: 'activities',
    Model,
    paginate
  };

  // Initialize our service with any options it requires
  app.use('/activities/search', new search());
  app.use('/activities', createService(options));

  // Get our initialized service so that we can register hooks and filters
  const searchService = app.service('activities/search');
  const service = app.service('activities');

  searchService.hooks(searchHooks);
  service.hooks(hooks);
};

I created a sub folder search under services/activities and hooks/ to handle the search endpoint and push models to sequelize include parameter

//Activites/Search/ Hooks
const properties = require('../../../hooks/search/properties ');
module.exports = {
  before: {
    all: [],
    find: [
      properties (),
    ],
    get: [],
    create: [],
    update: [],
    patch: [],
    remove: []
  },
};

//hooks/search/properties.js
module.exports = function (options = {}) {
  return async context => {
    const { app, params } = context;

    if (params.query.properties !== undefined){

      const properties = app.service('properties').Model;
      context.service.include.push({
        model: properties,
        attributes: ['code', 'name'],
        where: { 'code': params.query.properties},
      });
      delete context.params.query.properties;
    }

  };
};

//services/activities/search/index.js
module.exports = class Search {
  setup(app) {
    this.activities = app.service('activities');
    const placesModel = app.service('places').Model;
    this.association = { include: [
      {
        model: placesModel,
        attributes: ['*'],
      }
    ]};
    this.include = [];

  }

  find(params) {
    if (this.include.length > 0){
      this.association.include[0].include = this.include;
      params.sequelize = Object.assign(this.association, { raw: false });
    }

    const activities = this.activities.find(params);

    if (this.include.length > 0){
    this.association = { include: [
      {
        model: placesModel,
        attributes: ['*'],
      }
    ]};
    }
    return activities;
  }
}

Expected behavior

Get all activities with places having properties

Actual behavior

I get this error, i dont really understand what's wrong ?

is it correct to have this syntax places->properties`.`code` AS `places.properties.code in the generated sql query ?

Thank you in advance.

Generated query

    Executing (default): SELECT `activities`.*, `places`.* AS `places.*`, `places->properties`.`id` AS `places.properties.id`, `places->properties`.`code` AS `places.properties.code`, `places->properties`.`name` AS `places.properties.name`, `places->properties->places_properties`.`createdAt` AS `places.properties.places_properties.createdAt`, `places->properties->places_properties`.`updatedAt` AS `places.properties.places_properties.updatedAt`, `places->properties->places_properties`.`placeId` AS `places.properties.places_properties.placeId`, `places->properties->places_properties`.`propertyId` AS `places.properties.places_properties.propertyId` FROM (SELECT `activities`.`id`, `activities`.`name`, `activities`.`rating`, `activities`.`isEvent`, `activities`.`isMain`, `activities`.`image`, `activities`.`ageMin`, `activities`.`ageMax`, `activities`.`price`, `activities`.`createdAt`, `activities`.`updatedAt`, `activities`.`companyId`, `activities`.`ageId` FROM `activities` AS `activities` WHERE ( SELECT `activityId` FROM `places` AS `places` WHERE (`places`.`activityId` = `activities`.`id`) LIMIT 1 ) IS NOT NULL AND ( SELECT `places`.`id` FROM `places` AS `places` INNER JOIN ( `places_properties` AS `properties->places_properties` INNER JOIN `properties` AS `properties` ON `properties`.`id` = `properties->places_properties`.`propertyId`) ON `places`.`id` = `properties->places_properties`.`placeId` AND `properties`.`code` IN ('PFCB') WHERE `activities`.`id` = `places`.`activityId` LIMIT 1 ) IS NOT NULL LIMIT 10) AS `activities` LEFT OUTER JOIN ( `places` AS `places` INNER JOIN ( `places_properties` AS `places->properties->places_properties` INNER JOIN `properties` AS `places->properties` ON `places->properties`.`id` = `places->properties->places_properties`.`propertyId`) ON `places`.`id` = `places->properties->places_properties`.`placeId` AND `places->properties`.`code` IN ('PFCB') ) ON `activities`.`id` = `places`.`activityId`;

The error

error:  SequelizeDatabaseError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS `places.*`, `places->properties`.`id` AS `places.properties.id`, `places->pro' at line 1

System configuration

Module versions
"feathers-sequelize": "^3.0.1",
"sequelize": "^4.36.0",

NodeJS version:
5.6.0
Operating System:
Windows 7 64bits

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Apologies if the issue could not be resolved. FeathersJS ecosystem modules are community maintained so there may be a chance that there isn't anybody available to address the issue at the moment. For other ways to get help see here.

[DesignByOnyx]

Sorry for the late reply. You have a lot going on here - too much to try and recreate the problem for debugging. But if I had to guess, get rid of the attributes: ['*'], in services/activities/search/index.js. Sequelize will always select all fields by default, and using * requires an extra lookup. With problems like this, it's generally not a feathers issue but a sequelize one. See if you can get your query working in isolation and then copy the relevant parts into your hooks.

[DesignByOnyx]

Duplicate of #204 (which is closed).