Prisma text search "contains" with Join is not working

[arthurguedes375]

Bug description

I have a "FormAnswer" table that links to a User table that has a field called full_name.
I need to search all the "FormAnswer"s where the User name contains "x".

The problem is that it keeps returning me

prisma:query SELECT `DATABASE_NAME`.`FormAnswer`.`id`, `DATABASE_NAME`.`FormAnswer`.`updated_at`, `DATABASE_NAME`.`FormAnswer`.`user_id` FROM `DATABASE_NAME`.`FormAnswer` WHERE `DATABASE_NAME`.`FormAnswer`.`form_id` = ? ORDER BY MATCH (`DATABASE_NAME`.`User`.`full_name`)AGAINST (? IN BOOLEAN MODE) ASC, `DATABASE_NAME`.`FormAnswer`.`updated_at` DESC LIMIT ? OFFSET ?
web1_1   | prisma:error 
web1_1   | Invalid `prisma.formAnswer.findMany()` invocation in [...]


web1_1   | The column `DATABASE_NAME.User.full_name` does not exist in the current database.
web1_1   | PrismaClientKnownRequestError: 
web1_1   | Invalid `prisma.formAnswer.findMany()` invocation in [...]

The column `DATABASE_NAME.User.full_name` does not exist in the current database.
web1_1   |     at Hr.handleRequestError (/app/node_modules/@prisma/client/runtime/library.js:122:6999)
web1_1   |     at Hr.handleAndLogRequestError (/app/node_modules/@prisma/client/runtime/library.js:122:6388)
web1_1   |     at Hr.request (/app/node_modules/@prisma/client/runtime/library.js:122:6108)
web1_1   |     at l (/app/node_modules/@prisma/client/runtime/library.js:126:10298)
web1_1   |     at FormController.formAnswers (/app/src/app/controllers/Form.ts:185:27)
web1_1   |     at /app/src/app/Repositories/Router/ExpressRouter.ts:30:16 {
web1_1   |   code: 'P2022',
web1_1   |   clientVersion: '5.1.0',
web1_1   |   meta: { column: 'DATABASE_NAME.User.full_name' }

How to reproduce

Just run the "prisma" code on the prisma schema and you'll get the error

Expected behavior

This code should show me every FormAnswer that the userName contains certain "name" because the auto completion also gives me the option to add the "User" to the "Where" clause.
Either the autocompletion is wrong or the prisma functionality has not been implemented correctly.

Prisma information

My code looks like this:

// prisma/scheme.prisma
model User {
  id           String       @id @db.VarChar(40)
  full_name    String       @db.VarChar(100)
  created_at   DateTime     @default(now())
  updated_at   DateTime     @updatedAt
  FormsAnswers FormAnswer[]

  @@fulltext([full_name])
}

model FormAnswer {
  id           String        @id @db.VarChar(40)
  user_id      String        @db.VarChar(40)
  created_at   DateTime      @default(now())
  updated_at   DateTime      @updatedAt
  User         User          @relation(fields: [user_id], references: [id], onDelete: Cascade)
}

My query looks like this:

const query = 'Some name';
prisma.formAnswer.findMany({
    select: {
        id: true,
        updated_at: true,
        User: {
            select: {
                id: true,
                full_name: true,
            },
        },
    },
    where: {
        form_id: id,
 
        User: {
                 full_name: {
                     contains: query,
                 },
        },
    },
    orderBy: [
{
        User: {
            _relevance: {
                fields: ['full_name'],
                search:  query,
                sort: 'asc',
            },
        },
    },
   {
        updated_at: 'desc',
    }],
    take: 3,
});

Environment & setup

• OS: Official Node Docker Image -> node:18.17.1
• Database: Official Mysql Docker Image -> 8.0.27
• Node.js version: Official Node Docker Image -> node:18.17.1

Prisma Version

$ /app/node_modules/.bin/prisma -v
Environment variables loaded from .env
prisma                  : 5.1.0
@prisma/client          : 5.1.0
Current platform        : debian-openssl-3.0.x
Query Engine (Node-API) : libquery-engine a9b7003df90aa623086e4d6f4e43c72468e6339b (at node_modules/@prisma/engines/libquery_engine-debian-openssl-3.0.x.so.node)
Schema Engine           : schema-engine-cli a9b7003df90aa623086e4d6f4e43c72468e6339b (at node_modules/@prisma/engines/schema-engine-debian-openssl-3.0.x)
Schema Wasm             : @prisma/prisma-schema-wasm 5.1.0-28.a9b7003df90aa623086e4d6f4e43c72468e6339b
Default Engines Hash    : a9b7003df90aa623086e4d6f4e43c72468e6339b
Studio                  : 0.492.0
Preview Features        : fullTextIndex, fullTextSearch
Done in 0.87s.

[janpio]

This is an error message from your database. Are you sure you ran some migration that would create this table and column?

[arthurguedes375]

Yes, I made sure that it was in sync

[arthurguedes375]

The column doesn't actually exist because the "Formanswer.User" is an abstraction to a foreign key. But still, it should be able to work, right?

[janpio]

It is trying to access data in the column DATABASE_NAME.User.full_name, and then the database tells it, that does not exist. Per your Prisma schema, User.full_name should exist.

[arthurguedes375]

User.full_name does exist. Before I tried to implement the code like that I used to search by the user and just join the answers, I switched to searching the answers instead because I needed to implement cursor pagination in the answer timestamp.

[janpio]

I tried to reproduce your problem, but I can not run the code example in its current form as it references a field form_id that does not exist:

PS C:\Users\Jan\Documents\throwaway\21107> node .\script.js
PrismaClientValidationError: 
Invalid `prisma.formAnswer.findMany()` invocation in
C:\Users\Jan\Documents\throwaway\21107\script.js:8:43

  5 async function main() {
  6   const id = 1
  7   const query = 'Some name';
→ 8   const answers = await prisma.formAnswer.findMany({
        select: {
          id: true,
          updated_at: true,
          User: {
            select: {
              id: true,
              full_name: true
            }
          }
        },
        where: {
          form_id: 1,
          ~~~~~~~
          User: {
            full_name: {
              contains: "Some name"
            }
          },
      ?   AND?: FormAnswerWhereInput | FormAnswerWhereInput[],
      ?   OR?: FormAnswerWhereInput[],
      ?   NOT?: FormAnswerWhereInput | FormAnswerWhereInput[],
      ?   id?: StringFilter | String,
      ?   user_id?: StringFilter | String,
      ?   created_at?: DateTimeFilter | DateTime,
      ?   updated_at?: DateTimeFilter | DateTime
        },
        orderBy: [
          {
            User: {
              _relevance: {
                fields: [
                  "full_name"
                ],
                search: "Some name",
                sort: "asc"
              }
            }
          },
          {
            updated_at: "desc"
          }
        ],
        take: 3
      })

Unknown argument `form_id`. Available options are listed in green.
    at yn (C:\Users\Jan\Documents\throwaway\21107\node_modules\@prisma\client\runtime\library.js:116:5852)
    at wn.handleRequestError (C:\Users\Jan\Documents\throwaway\21107\node_modules\@prisma\client\runtime\library.js:123:6429)
    at wn.handleAndLogRequestError (C:\Users\Jan\Documents\throwaway\21107\node_modules\@prisma\client\runtime\library.js:123:6119)
    at wn.request (C:\Users\Jan\Documents\throwaway\21107\node_modules\@prisma\client\runtime\library.js:123:5839)
    at async l (C:\Users\Jan\Documents\throwaway\21107\node_modules\@prisma\client\runtime\library.js:128:9763)
    at async main (C:\Users\Jan\Documents\throwaway\21107\script.js:8:19) {
  clientVersion: '5.3.1'
}

Should I replace it? Adapt the schema? Or just comment it out @arthurguedes375?

[janpio]

When I comment it out, I can indeed reproduce your error:

> node .\script.js   
PrismaClientKnownRequestError: 
Invalid `prisma.formAnswer.findMany()` invocation in
C:\Users\Jan\Documents\throwaway\21107\script.js:8:43

  5 async function main() {
  6   const id = 1
  7   const query = 'Some name';
→ 8   const answers = await prisma.formAnswer.findMany(
The column `green_wombat.User.full_name` does not exist in the current database.
    at wn.handleRequestError (C:\Users\Jan\Documents\throwaway\21107\node_modules\@prisma\client\runtime\library.js:123:6730)
    at wn.handleAndLogRequestError (C:\Users\Jan\Documents\throwaway\21107\node_modules\@prisma\client\runtime\library.js:123:6119)
    at wn.request (C:\Users\Jan\Documents\throwaway\21107\node_modules\@prisma\client\runtime\library.js:123:5839)
    at async l (C:\Users\Jan\Documents\throwaway\21107\node_modules\@prisma\client\runtime\library.js:128:9763)
    at async main (C:\Users\Jan\Documents\throwaway\21107\script.js:8:19) {
  code: 'P2022',
  clientVersion: '5.3.1',
  meta: { column: 'green_wombat.User.full_name' }
}

The SQL query:

SELECT `green_wombat`.`FormAnswer`.`id`,
       `green_wombat`.`FormAnswer`.`updated_at`,
       `green_wombat`.`FormAnswer`.`user_id`
FROM `green_wombat`.`FormAnswer`
WHERE (`green_wombat`.`FormAnswer`.`id`) IN
    (SELECT `t0`.`id`
     FROM `green_wombat`.`FormAnswer` AS `t0`
     INNER JOIN `green_wombat`.`User` AS `j0` ON (`j0`.`id`) = (`t0`.`user_id`)
     WHERE (`j0`.`full_name` LIKE ?
            AND `t0`.`id` IS NOT NULL))
ORDER BY MATCH (`green_wombat`.`User`.`full_name`)AGAINST (? IN BOOLEAN MODE) ASC, `green_wombat`.`FormAnswer`.`updated_at` DESC
LIMIT ?
OFFSET ?

[janpio]

Ok, analysis what is going on.

This is the full query with params:

SELECT
	`green_wombat`.`FormAnswer`.`id`,
	`green_wombat`.`FormAnswer`.`updated_at`,
	`green_wombat`.`FormAnswer`.`user_id`
FROM
	`green_wombat`.`FormAnswer`
WHERE
	(`green_wombat`.`FormAnswer`.`id`) IN (
		SELECT
			`t0`.`id`
		FROM
			`green_wombat`.`FormAnswer` AS `t0`
		INNER JOIN `green_wombat`.`User` AS `j0` ON
			(`j0`.`id`) = (`t0`.`user_id`)
		WHERE
			(`j0`.`full_name` LIKE "%Some name%"
				AND `t0`.`id` IS NOT NULL)
	)
ORDER BY
	MATCH (`green_wombat`.`User`.`full_name`) AGAINST ("Some name" IN BOOLEAN MODE) ASC,
	`green_wombat`.`FormAnswer`.`updated_at` DESC
LIMIT 3 OFFSET 0

Note how the ORDER BY is using MATCH on green_wombat.User.full_name. That column and table do exist, but not in the context of this query - because the outer query never joined the User table - only the inner one inside the WHERE subquery.

That is also the fix we need to implement. Something like:

SELECT
	`green_wombat`.`FormAnswer`.`id`,
	`green_wombat`.`FormAnswer`.`updated_at`,
	`green_wombat`.`FormAnswer`.`user_id`
FROM
	`green_wombat`.`FormAnswer`
+ INNER JOIN `green_wombat`.`User` ON
+ 			(`green_wombat`.`User`.`id`) = (`green_wombat`.`FormAnswer`.`user_id`)	
WHERE
	(`green_wombat`.`FormAnswer`.`id`) IN (
		SELECT
			`t0`.`id`
		FROM
			`green_wombat`.`FormAnswer` AS `t0`
		INNER JOIN `green_wombat`.`User` AS `j0` ON
			(`j0`.`id`) = (`t0`.`user_id`)
		WHERE
			(`j0`.`full_name` LIKE "%Some name%"
				AND `t0`.`id` IS NOT NULL)
	)
ORDER BY
	MATCH (`green_wombat`.`User`.`full_name`) AGAINST ("Some name" IN BOOLEAN MODE) ASC,
	`green_wombat`.`FormAnswer`.`updated_at` DESC
LIMIT 3 OFFSET 0

Note the added INNER JOIN green_wombat.User ....

So this is a bug in the fullTextSearch preview feature that makes. Sorry @arthurguedes375.
For now you will need to write the SQL yourself and use $queryRaw to execute it.

[arthurguedes375]

Thank you for finding a solution to my problem!