How can I use a limit and a required in an include?

[MariaCamilaCubides]

Hi, I am trying to query an associated model via an include. I want to return only the models of the matched parents, returning only the first match of the children.

hook.params.sequelize = {
     include: [
          {
               model: 'executions',
               where: {
                    startedOn: null,
                    finishedOn: null,
               },
               limit: 1,
               required: true,
          }
     ]
}

Actual behavior

The result returns all the parents and it doesn't matter if have executions or not.

 "data": [
    {
       id: 'dcc0aedf-c0d6-4bbe-b34d-257b8e8e8263',
       action: 9,
       executions: [ [executions] ]
    },
    {
       id: '31dadc19-1b8b-4b59-8ae6-ab9e28afcfb5',
       action: 13,
       executions: []
    }, 
   {
       id: '442a8921-11fa-4e9c-ad8e-fe57ea6cc854',
       action: 8,
       executions: [ [executions] ]
    }, 
 ]

Expected behavior

I want that the result returns only the parents that have executions.

 "data": [
    {
       id: 'dcc0aedf-c0d6-4bbe-b34d-257b8e8e8263',
       action: 9,
       executions: [ [executions] ]
    },
   {
       id: '442a8921-11fa-4e9c-ad8e-fe57ea6cc854',
       action: 8,
       executions: [ [executions] ]
    }, 
 ]

System configuration

"feathers-sequelize": "^6.2.0"

[koraniar]

I have the same problem too, anyone knows how to do this?

[DaddyWarbucks]

This is actually returning the proper results for your query. Your query is saying "return all results, and include executions where startedOn and finsihed on are null on those results", not "return only results that that have executions where startedOn and finishedOn are null". You are looking for "top level" queries via $nested.column.syntax$. Check this link out: https://sequelize.org/master/manual/eager-loading.html#complex-where-clauses-at-the-top-level

[DaddyWarbucks]

This is the query you are looking for

.find({
  query: {
     required: true,
    '$executions.startedOn$': null,
    '$executions. finishedOn $': null,
    $limit: 1
  },
  sequelize: {
    include: [{
      model: excutionsModel,
      as: 'executions'
    }]
  }
})

[MariaCamilaCubides]

Hi @DaddyWarbucks, thank you for your response. I tried that query but it doesn't work as I expected, the result of the query brings all the data of both tables.

hook.arguments.query = {
    required: true,
    '$executions.startedOn$': null,
    '$executions.finishedOn$': null,
    $limit: 1,
  };
  const association = {
    include: [
      {
        model: hook.app.service('executions').Model,
        as: 'executions',
      },
    ],
  };
  hook.params.sequelize = Object.assign(association, { raw: false });

I solved it by denormalizing the table Executions with the necessary information of the columns startedOn and finishedOn for each execution.

[DaddyWarbucks]

I am not sure what arguments is. It should be hook.params.query instead of hook.arguments.query.

[MariaCamilaCubides]

You are right. I change for hook.params.query but I got this error Invalid query parameter $executions.startedOn$ at find execution-groups. I got this error in the past months but I couldn't solve it.

[DaddyWarbucks]

You have to whitelist the $executions.startedOn$ in the service options. See the README for more info: https://github.com/feathersjs-ecosystem/feathers-sequelize#serviceoptions

const options = {
    Model,
    paginate,
    whitelist: ['$executions.startedOn$']
  };

[MariaCamilaCubides]

Thank you so much, that works with a little change. I had to add the property duplicating: false because before that I had missing FROM-clause entry for table "executions" at find execution-groups error, so I searched and found a solution here https://github.com/feathersjs-ecosystem/feathers-sequelize/issues/248.

hook.params.query = {
    '$executions.startedOn$': null,
    '$executions.finishedOn$': null,
    $limit: 1,
  };
  const association = {
    include: [
      {
        model: hook.app.service('executions').Model,
        as: 'executions',
        duplicating: false,
      },
    ],
  };
  hook.params.sequelize = Object.assign(association, { raw: false });

[chrisbag]

For those ending up on this thread, duplicating false does solve the issue but messes up the limit and offset. For this to work you need to make separate queries (separate:true). This works for one-to-many relations but does not work with many-to-many as is indicated in this thread which is 7 years old and has still has not been solved https://github.com/sequelize/sequelize/pull/4525 and https://github.com/sequelize/sequelize/issues/8457