The Wayback Machine - https://web.archive.org/web/20201202155501/https://github.com/strapi/strapi/issues/7854
Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Admin Panel. Can't search by ID in relation in with `postgres` database. function lower(integer) does not exist #7854

Open
qunabu opened this issue Sep 11, 2020 · 2 comments · May be fixed by #8419

Comments

@qunabu
Copy link
Contributor

@qunabu qunabu commented Sep 11, 2020

Describe the bug

Can't search by ID in relation in with postgres database

Steps to reproduce the behavior

  1. Create a new project yarn create strapi-app my-project --quickstart
  2. Change database to postgres
  3. Run the application yarn develop
  4. Create a dummy model with Content-Type Builder that has a relation with User example Documents

image

{
  "kind": "collectionType",
  "collectionName": "documents",
  "info": {
    "name": "Documents"
  },
  "options": {
    "increments": true,
    "timestamps": true
  },
  "attributes": {
    "Name": {
      "type": "string"
    },
    "user": {
      "plugin": "users-permissions",
      "model": "user",
      "via": "documents"
    }
  }
}
  1. Click "Configure the view" and for "User" select "id" field as "Entry Title"

image
6. Create at least 2 users in the panel
7. Try to create a new model, in User field when you try to find users by id field error occurs and you cannot find this

image

Once you start to autocomplete search you get admin error, which is trying to find that model by given type input
calling Admin API with /content-manager/explorer/application::XXX.XXX?id_contains=23&_limit=20&_start=0

Console throws an error

error error: function lower(integer) does not exist
    at Parser.parseErrorMessage (/Users/qunabu/Desktop/localhost/quickstart-strapi/node_modules/pg-protocol/dist/parser.js:278:15)
    at Parser.handlePacket (/Users/qunabu/Desktop/localhost/quickstart-strapi/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/qunabu/Desktop/localhost/quickstart-strapi/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/Users/qunabu/Desktop/localhost/quickstart-strapi/node_modules/pg-protocol/dist/index.js:8:42)
    at Socket.emit (events.js:311:20)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11)
    at Socket.Readable.push (_stream_readable.js:209:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)

Expected behavior

No error, should be able to find model by id

Code snippets

This can be fixed by extending postgres and adding lower(integer) function definition to database by adding the following code to config/functions/bootstrap.js

  try {
    if (strapi.connections.default.client.config.client === "pg") {
      await strapi.connections.default.raw(
        `CREATE OR REPLACE FUNCTION lower(id INTEGER)
    RETURNS TEXT AS $$
BEGIN
    -- casts integer to text
    RETURN lower(cast(id as TEXT));
END; $$
LANGUAGE plpgsql`
      );
    }
  } catch (err) {
    console.log("error", err);
  }

System

  • Node.js version: v12.16.1
  • NPM version:6.13.4
  • Strapi version: 3.1.4
  • Database: postgres
  • Operating system: mac os 10.15.6

Additional context
Error is because you cannot run lower function on non-text fields in postgres query. They should be cast to text before the query. The framework is trying to the the following query select distinct "XXX".* from "XXX" where LOWER("XXX"."id") LIKE LOWER(?) limit ?(where XXX is your model name) which is invalid in postgres

@derrickmehaffy
Copy link
Member

@derrickmehaffy derrickmehaffy commented Sep 11, 2020

Hello!

Thank you for reporting this behavior.

Something I would greatly appreciate is if you can try to submit a PR to fix this issue.

The topic you are pointing out has to be fixed and it could be done by someone from the Community (you 🤗).

Here is the contributing guide to help you to getting started for contributions to Strapi.

Do not hesitate to reach us if you have any questions regarding the contribution.

Thank you and have a good day.

@qunabu
Copy link
Contributor Author

@qunabu qunabu commented Sep 11, 2020

@derrickmehaffy I would yet I don't think my solution is fully correct. I'm JavaScript expert not SQL/Postgres at all and this issue does concern more SQL than JavaScript in my option.

Other solution I'm thinking is to change knex somewhere to cast fields to text before lowering them - yet I don't know how to do this properly as well.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

2 participants
You can’t perform that action at this time.