Collection type search issues

System Information
  • Strapi Version: 3.6.1
  • Operating System: Windows
  • Database: MySql
  • Node Version: 12
  • NPM Version:
  • Yarn Version: 1.22.10

Hello,

I have inherited a strapi application and we have some troubles with the search functionality.

After setting up a new Strapi application, I can see that out of the box strapi + graphql + mysql is perfectable capable of supporting the global search functionality.

However, as you can see from the screenshot below the search within our instance just returns all results. It does this regardless of the value and on all collection types. It appears that the _q= statement is being ignored.

Interestingly the filters do work.

My question is, where should I be looking to debug this issue?

Beyond the “Enable Search” option I can’t see anything in the UI related to the search and within the code I can’t see anything obvious.

Any pointers in the correct direction greatly appreciated.

Thank you.

After some digging into the strapi code I discovered the issue was with the use of the mysql2 library to connect to the database.

Within the strapi-connector-bookshelf package, queries.js there is method:

const buildSearchQuery = ({ model, params }) => qb => {

  const query = params._q;

  const associations = model.associations.map(x => x.alias);

  const stringTypes = ['string', 'text', 'uid', 'email', 'enumeration', 'richtext'];

  const numberTypes = ['biginteger', 'integer', 'decimal', 'float'];

  const searchColumns = Object.keys(model._attributes)

    .filter(attribute => !associations.includes(attribute))

    .filter(attribute => stringTypes.includes(model._attributes[attribute].type))

    .filter(attribute => model._attributes[attribute].searchable !== false);

  if (!_.isNaN(_.toNumber(query))) {

    const numberColumns = Object.keys(model._attributes)

      .filter(attribute => !associations.includes(attribute))

      .filter(attribute => numberTypes.includes(model._attributes[attribute].type))

      .filter(attribute => model._attributes[attribute].searchable !== false);

    searchColumns.push(...numberColumns);

  }

  if ([...numberTypes, ...stringTypes].includes(model.primaryKeyType)) {

    searchColumns.push(model.primaryKey);

  }

  // Search in columns with text using index.

  switch (model.client) {

    case 'pg':

      searchColumns.forEach(attr =>

        qb.orWhereRaw(

          `"${model.collectionName}"."${attr}"::text ILIKE ?`,

          `%${escapeQuery(query, '*%\\')}%`

        )

      );

      break;

    case 'sqlite3':

      searchColumns.forEach(attr =>

        qb.orWhereRaw(

          `"${model.collectionName}"."${attr}" LIKE ? ESCAPE '\\'`,

          `%${escapeQuery(query, '*%\\')}%`

        )

      );

      break;

    case 'mysql':

      searchColumns.forEach(attr =>

        qb.orWhereRaw(

          `\`${model.collectionName}\`.\`${attr}\` LIKE ?`,

          `%${escapeQuery(query, '*%\\')}%`

        )

      );

      break;

  }

};

As the project was setup with mysql2 as the client within database.js it was skipping any of the where queries.

I see from the official docs that mysql is the supported connector so I’ve changed the config to use that package and it’s fixed the search.