SQL operator compatibility

Accoding to the documentation the operator iLike should work for the storage type of sql. Using the provided example city data model, I was not able to use this operator, although this operator should be supported as per documentation. Please have a look at the following query


{
  cities(
        search :{field:name, operator:iLike, value:"B%"},
    order: [{field: name, order: ASC}], 
    pagination: {limit: 5}) {
        city_id
    name
    population

  }

}

I get the following error message

{ "errors": [ { "message": "SQLITE_ERROR: near \"ILIKE\": syntax error", "locations": [ { "line": 2, "column": 3 } ], "path": [ "cities" ] } ], "data": { "cities": null } }

The output from the zendro-graphql-server is as follows

Executing (default): SELECT city_id, name, population, country_id, createdAt, updatedAt FROM cities AS city WHERE city.name ILIKE 'B%' ORDER BY city.name ASC, city.city_id ASC LIMIT 5; SequelizeDatabaseError: SQLITE_ERROR: near "ILIKE": syntax error at Query.formatError (/usr/graphql-server/node_modules/sequelize/lib/dialects/sqlite/query.js:422:16) at Query._handleQueryResponse (/usr/graphql-server/node_modules/sequelize/lib/dialects/sqlite/query.js:73:18) at afterExecute (/usr/graphql-server/node_modules/sequelize/lib/dialects/sqlite/query.js:250:31) at Statement.errBack (/usr/graphql-server/node_modules/sqlite3/lib/sqlite3.js:14:21) {"message":"SQLITE_ERROR: near \"ILIKE\": syntax error","locations":[{"line":2,"column":3}],"path":["cities"],"source":{"body":"{\n cities(\n \t\tsearch :{field:name, operator:iLike, value:\"B%\"},\n order: [{field: name, order: ASC}], \n pagination: {limit: 5}) {\n\t\tcity_id\n name\n population\n\n }\n \n}","name":"GraphQL request","locationOffset":{"line":1,"column":1}},"positions":[4],"originalError":{"message":"SQLITE_ERROR: near \"ILIKE\": syntax error","name":"SequelizeDatabaseError","parent":{"message":"SQLITE_ERROR: near \"ILIKE\": syntax error","errno":1,"code":"SQLITE_ERROR","sql":"SELECT city_id, name, population, country_id, createdAt, updatedAt FROM cities AS city WHERE city.name ILIKE 'B%' ORDER BY city.name ASC, city.city_id ASC LIMIT 5;"},"original":{"message":"SQLITE_ERROR: near \"ILIKE\": syntax error","errno":1,"code":"SQLITE_ERROR","sql":"SELECT city_id, name, population, country_id, createdAt, updatedAt FROM cities AS city WHERE city.name ILIKE 'B%' ORDER BY city.name ASC, city.city_id ASC LIMIT 5;"},"sql":"SELECT city_id, name, population, country_id, createdAt, updatedAt FROM cities AS city WHERE city.name ILIKE 'B%' ORDER BY city.name ASC, city.city_id ASC LIMIT 5;"}}

Any help is much appreciated

Zendro-dev / Zendro-dev.github.io

SQL operator compatibility #44