Perform $or filters

[IceOnFire]

Informations

• Node.js version: v10.15.3
• NPM version: 6.4.1
• Strapi version: 3.0.0-alpha.26.1
• Database: MongoDB v3.6.8
• Operating system: Linux Ubuntu 19.04 (Disco Dingo)
• (Optional) Link to your Project:

What is the current behavior?

After migrating from 3.0.0-alpha.24 to 3.0.0-alpha.26.1 I changed all my services so they use convertRestQueryParams and buildQuery. Everything works fine.

In fetchAll I used to intercept the incoming parameters in order to change them into custom where clauses. For example, if params.isBanned == true, then I used to add a where clause such as

{
  $and: [
    { banStartDate: { $gt: date } },
    { banEndDate: { $lt: date } },
  ]
}

otherwise if params.isBanned == false

I used to put

{
  $or: [
    { banStartDate: { $lte: date } },
    { banEndDate: { $gte: date } }
  ]
}

Now that we have convertRestQueryParams and buildQuery, the format of our where clause has changed so I adapted it to the new structure. For the $and case it changes into

[
  { field: 'banStartDate', operator: 'lte', value: date },
  { field: 'banEndDate', operator: 'gte', value: date },
]

but for the $or case I cannot seem to find a solution: from what I can see the buildQuery function supports the $or case only if I have the same operator and multiple values, but in my case I need to put completely different clauses in $or.

Steps to reproduce the problem

Just try to add some custom filters such as the one described above.

What is the expected behavior?

It would be great if buildQuery supported multiple clauses in $or, otherwise simple custom filters such as the one described cannot be defined without going to a lower level.

Suggested solutions

Maybe the buildQuery function could have a special case in which, if the operator is or, then it just builds the $or clause with an array of values taken from value.

[IceOnFire]

Maybe something like this?

const buildWhereClause = ({ field, operator, value }) => {
  if (Array.isArray(value) && !['in', 'nin', 'or'].includes(operator)) { // bypass 'or'
    return {
      $or: value.map(val => buildWhereClause({ field, operator, value: val })),
    };
  }

  const val = formatValue(value);

  switch (operator) {
    case 'eq':
      return { [field]: val };
    case 'ne':
      return { [field]: { $ne: val } };
    case 'lt':
      return { [field]: { $lt: val } };
    case 'lte':
      return { [field]: { $lte: val } };
    case 'gt':
      return { [field]: { $gt: val } };
    case 'gte':
      return { [field]: { $gte: val } };
    case 'or':
      return { $or: Array.isArray(val) ? val : [val] }; // this should do the trick
    case 'in':
      return {
        [field]: {
          $in: Array.isArray(val) ? val : [val],
        },
      };
    case 'nin':
      return {
        [field]: {
          $nin: Array.isArray(val) ? val : [val],
        },
      };
    case 'contains': {
      return {
        [field]: {
          $regex: `${val}`,
          $options: 'i',
        },
      };
    }
    case 'ncontains':
      return {
        [field]: {
          $not: new RegExp(val, 'i'),
        },
      };
    case 'containss':
      return {
        [field]: {
          $regex: `${val}`,
        },
      };
    case 'ncontainss':
      return {
        [field]: {
          $not: new RegExp(val),
        },
      };

    default:
      throw new Error(`Unhandled whereClause : ${fullField} ${operator} ${value}`);
  }
};

[IceOnFire]

Also, I cannot do any geospatial queries. This buildWhereClause function is a bit limiting apparently...

[ragnorc]

Is there maybe an ETA for this? I think it is a very important issue as any non-trivial use case will somehow need boolean operators in GraphQL queries.

[lauriejim]

Hello! We don't have ETA about this topic.
And we know it's important (high flag)

[samuelpetroline]

Is there any workaround until this is not implemented?

[alexandrebodin]

You can access a mongoose or bookshelf model through

strapi.query(modelName).model then you can run any query you can with the specific underlying orm.

[mehmetaydogduu]

When I switched from mongodb to postgresql, I could easily use AND. There is no solution for OR.

[petersg83]

Fixed by #6965

[dadaphl]

I can not find any information in the documentation on how to build an OR query with graphql in strapi. Google points to this issue. @petersg83, would you mind sharing an example here? Thank you.

[derrickmehaffy]

I can not find any information in the documentation on how to build an OR query with graphql in strapi. Google points to this issue. @petersg83, would you mind sharing an example here? Thank you.

Something like this (we are aware of the lacking documentation for this and plan to make some changes)

{
  addresses(where: { _or: [ { postal_coder: "92301"}, { postal_coder: "11111"}] }) {
    postal_coder
    id
  }
}

[FenixDeveloper]

@derrickmehaffy filter in this example will work with nested models? For example:

modelTop has fields "_id, color" and field "childs" related to many modelChild
modelChild has fields "_id, published, type" and field "parent" related to one modelTop

In this case can we build next query:
{ modelTop(where: { _or: [ { color_contains: "white", childs: { type: "test", published_ne: false } }, { color_contains: "white", childs: { type_ne: "test" } } ] }) { color } }

In current version i can query models with nested filters only without "or", but may be i did it wrong?

[derrickmehaffy]

@derrickmehaffy filter in this example will work with nested models? For example:

modelTop has fields "_id, color" and field "childs" related to many modelChild
modelChild has fields "_id, published, type" and field "parent" related to one modelTop

In this case can we build next query:
{ modelTop(where: { _or: [ { color_contains: "white", childs: { type: "test", published_ne: false } }, { color_contains: "white", childs: { type_ne: "test" } } ] }) { color } }

In current version i can query models with nested filters only without "or", but may be i did it wrong?

I don't believe so 🤔 I'll try and do some testing but we are planning a major rewrite this quarter on the filtering and deep filtering system especially with complex queries.

[derrickmehaffy]

This issue has been mentioned on Strapi Community Forum. There might be relevant details there:

https://forum.strapi.io/t/logical-operators-or-and-in-graphql-where-parameter-7795/1753/1