limit with subQuery: false returns missing data

[fkursun]

What are you doing?

I would like to order my query by association's column which is user. This is Creative model

module.exports = (sequelize, DataTypes) => {
  const Creative = sequelize.define('Creative', {
    id: {
      primaryKey: true,
      autoIncrement: true,
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    campaign_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    input_set_id: DataTypes.INTEGER,
    template_id: DataTypes.INTEGER,
    tracker_impression: {
      type: DataTypes.STRING,
      defaultValue: '',
    },
    tracker_click: {
      type: DataTypes.STRING,
      defaultValue: ''
    },
    tracker_viewability: {
      type: DataTypes.STRING,
      defaultValue: ''
    },
    is_external_traffic: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
      allowNull: false,
    },
    dsp: {
      type: DataTypes.STRING,
      allowNull: true,
    },
    snippet: DataTypes.STRING,
    created_by: DataTypes.INTEGER,
    updated_by: DataTypes.INTEGER,
    snippet_id: {
      type: DataTypes.CHAR(36),
      allowNull: true,
    }
  }, {
    tableName: 'creative',
    hooks: {
      afterDestroy: async (instance, options) => {
        const inputSetId = instance.dataValues.input_set_id;
        await sequelize.models.InputSet.destroy({where: {id: inputSetId}});

        const creativeTrackers = await instance.getTrackers();
        if (creativeTrackers.length) {
          await Promise.all(creativeTrackers.map((tracker) => {
            return sequelize.models.Tracker.destroy({where: {id: tracker.dataValues.id}});
          }));
          await instance.setTrackers(null);
        }

        const creativeZoneClicks = await instance.getZoneClicks();
        if (creativeZoneClicks.length) {
          await Promise.all(creativeZoneClicks.map((zone) => {
            return sequelize.models.ZoneClick.destroy({where: {id: zone.dataValues.id}});
          }));
          await instance.setZoneClicks(null);
        }
      },
    },
    scopes: {
      nameLike: (search) => {
        return { where: { name: { $like: `%${search}%` }}};
      },
      getUser: () => {
        return {
          include: [{ model: sequelize.models.User, as: 'user' }],
        };
      },
      trackers: {
        include: [{
          model: sequelize.models.Tracker,
          through: sequelize.models.CreativeTracker,
          as: 'trackers',
        }],
      },
      zoneClicks: {
        include: [{
          model: sequelize.models.ZoneClick,
          through: sequelize.models.CreativeZoneClick,
          as: 'zoneClicks',
          attributes: [],
        }],
      },
      getTemplate: () => {
        return {
          include: [{ model: sequelize.models.Template, as: 'template' }],
        };
      },
      getInputSet: () => {
        return {
          include: [{ model: sequelize.models.InputSet, as: 'input_set' }],
        };
      },
      createdByCompany: (company_id) => {
        return {
          include: [{
            where: { '$user.company_id$': company_id},
            model: sequelize.models.User, as: 'user'
          }]
        };
      },
    },
  });

  Creative.associate = (models) => {
    Creative.belongsTo(models.Campaign);
    Creative.belongsTo(models.User, { foreignKey: 'created_by', as: 'user' });
    Creative.belongsToMany(models.Tracker, {through: models.CreativeTracker, as: 'trackers'});
    Creative.belongsToMany(models.ZoneClick, {through: models.CreativeZoneClick, as: 'zoneClicks'});
  };

  /**
   * A helper method to get the supported queryOptions
   *
   * @param {Object} options Options parsed from client's request
   * @returns {void}
   */
  Creative.getQueryOptions = (options) => {
    const queryOptions = {
      scopes: ['getUser', 'getTemplate', 'getInputSet'],
      where: {},
      limit: options.limit,
      offset: options.offset,
      order: [],
    };

    if (options.sort_by) {
      if (options.sort_by === 'owner') {
        queryOptions.subQuery = false;
        queryOptions.order.push(
          [sequelize.literal(`SUBSTRING(user.first_name,1,1) ${options.sort_order || 'DESC'}`)],
          [sequelize.literal(`SUBSTRING(user.last_name,1,1) ${options.sort_order || 'DESC'}`)]);
      }
      else {
        queryOptions.order.push([options.sort_by, options.sort_order || 'DESC']);
      }
    }

    return queryOptions;
  };

  return Creative;
};

And this is the function

async getCreatives(params = {}) {
    const creatives = {};
    const findOptions = this.model.getQueryOptions(params);
    findOptions.include = [
      {
        model: this.models.Tracker,
        as: 'trackers',
        attributes: ['id', 'tracker_value', 'tracker_category'],
        through: {
          attributes: [],
        },
      },
      {
        model: this.models.ZoneClick,
        as: 'zoneClicks',
        attributes: ['id', 'zone_name', 'click_category', 'click_value'],
        through: {
          attributes: [],
        },
      },
    ];
    findOptions.distinct = true;
    const response = await this.findAndCountAll(findOptions);
    creatives.total = response.count;
    creatives.data = response.rows;
    return creatives;
  }

If I delete subQuery = false, it throws an error saying: Unknown column 'user' in 'order clause'. If I set subQuery = true it works but returns missing data. Let's say I need to see 6 results, but it returns 3 results.

What do you expect to happen?

It should output array with 6 records.

What is actually happening?

Here's the query that returns missing data with 3 records while I was waiting 6

SELECT 
  `Creative`.`id`, 
  `Creative`.`name`, 
  `Creative`.`campaign_id`, 
  `Creative`.`input_set_id`, 
  `Creative`.`template_id`, 
  `Creative`.`tracker_impression`, 
  `Creative`.`tracker_click`, 
  `Creative`.`tracker_viewability`, 
  `Creative`.`is_external_traffic`, 
  `Creative`.`dsp`, 
  `Creative`.`snippet`, 
  `Creative`.`created_by`, 
  `Creative`.`updated_by`, 
  `Creative`.`snippet_id`, 
  `Creative`.`created_at`, 
  `Creative`.`updated_at`, 
  `Creative`.`deleted_at`, 
  `trackers`.`id` AS `trackers.id`, 
  `trackers`.`tracker_value` AS `trackers.tracker_value`, 
  `trackers`.`tracker_category` AS `trackers.tracker_category`, 
  `trackers->CreativeTracker`.`creative_id` AS `trackers.CreativeTracker.creative_id`, 
  `trackers->CreativeTracker`.`tracker_id` AS `trackers.CreativeTracker.tracker_id`, 
  `zoneClicks`.`id` AS `zoneClicks.id`, 
  `zoneClicks`.`zone_name` AS `zoneClicks.zone_name`, 
  `zoneClicks`.`click_category` AS `zoneClicks.click_category`, 
  `zoneClicks`.`click_value` AS `zoneClicks.click_value`, 
  `zoneClicks->CreativeZoneClick`.`creative_id` AS `zoneClicks.CreativeZoneClick.creative_id`, 
  `zoneClicks->CreativeZoneClick`.`zone_click_id` AS `zoneClicks.CreativeZoneClick.zone_click_id`, 
  `user`.`id` AS `user.id`, 
  `user`.`email` AS `user.email`, 
  `user`.`first_name` AS `user.first_name`, 
  `user`.`last_name` AS `user.last_name`, 
  `user`.`company_id` AS `user.company_id`, 
  `user`.`admin` AS `user.admin`, 
  `user`.`created_at` AS `user.created_at`, 
  `user`.`updated_at` AS `user.updated_at`, 
  `user`.`deleted_at` AS `user.deleted_at`, 
  `input_set`.`id` AS `input_set.id`, 
  `input_set`.`input_set_id` AS `input_set.input_set_id`, 
  `input_set`.`main_image` AS `input_set.main_image`, 
  `input_set`.`video` AS `input_set.video`, 
  `input_set`.`logo` AS `input_set.logo`, 
  `input_set`.`title` AS `input_set.title`, 
  `input_set`.`description` AS `input_set.description`, 
  `input_set`.`sponsor` AS `input_set.sponsor`, 
  `input_set`.`cta` AS `input_set.cta`, 
  `input_set`.`click_url` AS `input_set.click_url`, 
  `input_set`.`handler` AS `input_set.handler`, 
  `input_set`.`post_url` AS `input_set.post_url`, 
  `input_set`.`background_color` AS `input_set.background_color`, 
  `input_set`.`created_at` AS `input_set.created_at`, 
  `input_set`.`updated_at` AS `input_set.updated_at`, 
  `input_set`.`deleted_at` AS `input_set.deleted_at`, 
  `template`.`id` AS `template.id`, 
  `template`.`shortname` AS `template.shortname`, 
  `template`.`name` AS `template.name`, 
  `template`.`url` AS `template.url`, 
  `template`.`url_staging` AS `template.url_staging`, 
  `template`.`hover` AS `template.hover`, 
  `template`.`sidekick` AS `template.sidekick`, 
  `template`.`is_social` AS `template.is_social`, 
  `template`.`created_at` AS `template.created_at`, 
  `template`.`updated_at` AS `template.updated_at`, 
  `template`.`deleted_at` AS `template.deleted_at` 
FROM 
  `creative` AS `Creative` 
  LEFT OUTER JOIN (
    `creative_tracker` AS `trackers->CreativeTracker` 
    INNER JOIN `trackers` AS `trackers` ON `trackers`.`id` = `trackers->CreativeTracker`.`tracker_id`
  ) ON `Creative`.`id` = `trackers->CreativeTracker`.`creative_id` 
  LEFT OUTER JOIN (
    `creative_zone_click` AS `zoneClicks->CreativeZoneClick` 
    INNER JOIN `zone_click` AS `zoneClicks` ON `zoneClicks`.`id` = `zoneClicks->CreativeZoneClick`.`zone_click_id`
  ) ON `Creative`.`id` = `zoneClicks->CreativeZoneClick`.`creative_id` 
  INNER JOIN `user` AS `user` ON `Creative`.`created_by` = `user`.`id` 
  AND (
    (
      `user`.`deleted_at` > '2022-01-24 13:24:19' 
      OR `user`.`deleted_at` IS NULL
    ) 
    AND `user`.`company_id` = 1
  ) 
  LEFT OUTER JOIN `input_set` AS `input_set` ON `Creative`.`input_set_id` = `input_set`.`id` 
  AND (
    `input_set`.`deleted_at` > '2022-01-24 13:24:19' 
    OR `input_set`.`deleted_at` IS NULL
  ) 
  LEFT OUTER JOIN `template` AS `template` ON `Creative`.`template_id` = `template`.`id` 
  AND (
    `template`.`deleted_at` > '2022-01-24 13:24:19' 
    OR `template`.`deleted_at` IS NULL
  ) 
WHERE 
  (
    (
      `Creative`.`deleted_at` > '2022-01-24 13:24:19' 
      OR `Creative`.`deleted_at` IS NULL
    ) 
    AND `Creative`.`campaign_id` = '112'
  ) 
ORDER BY 
  SUBSTRING(user.first_name, 1, 1) desc, 
  SUBSTRING(user.last_name, 1, 1) desc 
LIMIT 
  0, 25;

Sequelize version: 4.37.10
I have tried with latest version

[github-actions]

This issue has been automatically marked as stale because it has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[ephys]

The query data having the "wrong" limit is expected since subQuery is needed to properly limit only one table without limiting its associations

Column references in Sequelize.literal not working is also expected since it's selected differently when subQuery is set and the column reference should be updated accordingly (which sequelize can't do automatically as you're using literal)

[tttaisgt]

sorry I wanted to ask if there has been any progress here?

[ephys]

No progress yet, although it depends on what your issue is:

• if your issue is that subQuery: false results in an incorrect number of results, that's expected. The query is run in a subQuery to be able to limit the 'right' model and forcing sequelize to not use a subquery will result in limit not working as expected. See limit with subQuery: false return wrong result #9605 (comment)
• if your issue is that you can't reference some tables when subQuery is true, that's this issue: $association.references$ are incompatible with limit and one-to-many & many-to-many includes/associations #12971

Since these two issues are the ones centralizing the discussion on this problem, I'll close this issue and redirect the discussion there.