_relevance error when utilizing orderBy relations

[malbaugh]

Bug description

Using _relevance fails when used with fields within a relation table.

prisma:query SELECT "public"."Parent"."id", "public"."Parent"."childId" FROM "public"."Parent" WHERE 1=1 ORDER BY ts_rank(to_tsvector(concat_ws(' ', "public"."Child"."name","public"."Child"."description","public"."Child"."notes")), to_tsquery($1)) DESC OFFSET $2
prisma:query ROLLBACK
Error - ParentUtils.fetch():  PrismaClientKnownRequestError: 
Invalid `prisma.parent.findMany()` invocation:


The table `(not available)` does not exist in the current database.
    at RequestHandler.handleRequestError (/node_modules/@prisma/client/runtime/index.js:35024:13)
    at RequestHandler.handleAndLogRequestError (/node_modules/@prisma/client/runtime/index.js:34996:12)
    at RequestHandler.request (/node_modules/@prisma/client/runtime/index.js:34991:12)
    at PrismaClient._request (/node_modules/@prisma/client/runtime/index.js:36082:16) {
  code: 'P2021',
  clientVersion: '4.8.1',
  meta: { table: '(not available)' },
  batchRequestIdx: 0
}
ERROR: Resource not found.

If orderBy: { ... } is removed, the query works without the table '{not available}' error.

How to reproduce

Setup a parent & child table relation. Preform the query.

Expected behavior

I would expect this to order results according to their relevance to the search query and the fields specified in the relation table.

Prisma information

model child {
  id          String              @id @default(uuid())
  name        String
  description String?
  notes       String?
  parent     Parent
}

model parent {
  id          String              @id @default(uuid())
  child     Child               @relation(fields: [childId], references: [id])
  childId  String              @unique
}

await prisma.parent.findMany({
          orderBy: {
            child: {
              _relevance: {
                fields: ['name', 'description', 'notes'],
                search: formattedQuery,
                sort: 'desc',
              },
            },
          },
})

Environment & setup

• OS: macOS
• Database: PostgreSQL
• Node.js version: v16.16.0

Prisma Version

prisma                  : 4.8.1
@prisma/client          : 4.8.1
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt d6e67a83f971b175a593ccc12e15c4a757f93ffe (at node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.8.0-61.d6e67a83f971b175a593ccc12e15c4a757f93ffe
Default Engines Hash    : d6e67a83f971b175a593ccc12e15c4a757f93ffe
Studio                  : 0.479.0
Preview Features        : fullTextSearch

[SimonRosengren]

Any updates on this? Im having the same issue in the same scenario.

[janpio]

Can one of you please enable query logging and share the SQL queries that are created for this under the hood? What happens when you run them manually against the database?

[reubenjh]

Hi @janpio, i'm seeing the same result - here are the generated sql queries for a prisma statement with a regular orderBy _relevance clause vs one with child orderBy _relevance clauses.

...working

const cards = await ctx.prisma.card.findMany({
        where: {
          OR: [
            {
              name: {
                search: sanitisedQuery,
              },
            },
            {
              guardian: { typeText: { search: sanitisedQuery } },
            },
            {
              guardian: { rulesText: { search: sanitisedQuery } },
            },
          ],
        },
        orderBy: {
          _relevance: {
            fields: "name",
            search: sanitisedQuery,
            sort: "desc",
          },
        },
      });
      
SELECT 
    `sorcery`.`Card`.`id`, 
    `sorcery`.`Card`.`slug`, 
    `sorcery`.`Card`.`name`, 
    `sorcery`.`Card`.`hotscore`
FROM 
    `sorcery`.`Card`
LEFT JOIN 
    `sorcery`.`Guardian` AS `j1` ON (`j1`.`cardId`) = (`sorcery`.`Card`.`id`)
LEFT JOIN 
    `sorcery`.`Guardian` AS `j2` ON (`j2`.`cardId`) = (`sorcery`.`Card`.`id`)
WHERE 
    ((MATCH (`j1`.`typeText`) AGAINST (? IN BOOLEAN MODE) AND (`j1`.`id` IS NOT NULL))
    OR (MATCH (`j2`.`rulesText`) AGAINST (? IN BOOLEAN MODE) AND (`j2`.`id` IS NOT NULL))
    OR MATCH (`sorcery`.`Card`.`name`) AGAINST (? IN BOOLEAN MODE))
ORDER BY 
    MATCH (`sorcery`.`Card`.`name`) AGAINST (? IN BOOLEAN MODE) DESC
LIMIT 
    ? OFFSET ?;

... not working, throws Invalid prisma.card.findMany()invocation: The column(not available) does not exist in the current database.

  const cards = await ctx.prisma.card.findMany({
        where: {
          OR: [
            {
              name: {
                search: sanitisedQuery,
              },
            },
            {
              guardian: { typeText: { search: sanitisedQuery } },
            },
            {
              guardian: { rulesText: { search: sanitisedQuery } },
            },
          ],
        },
        orderBy: [
          {
            _relevance: {
              fields: "name",
              search: sanitisedQuery,
              sort: "desc",
            },
          },
          {
            guardian: {
              _relevance: {
                fields: ["typeText", "rulesText"],
                search: sanitisedQuery,
                sort: "asc",
              },
            },
          },
        ],
      });
      
SELECT 
    `sorcery`.`Card`.`id`, 
    `sorcery`.`Card`.`slug`, 
    `sorcery`.`Card`.`name`, 
    `sorcery`.`Card`.`hotscore`
FROM 
    `sorcery`.`Card`
LEFT JOIN 
    `sorcery`.`Guardian` AS `j1` ON (`j1`.`cardId`) = (`sorcery`.`Card`.`id`)
LEFT JOIN 
    `sorcery`.`Guardian` AS `j2` ON (`j2`.`cardId`) = (`sorcery`.`Card`.`id`)
WHERE 
    ((MATCH (`j1`.`typeText`) AGAINST (? IN BOOLEAN MODE) AND (`j1`.`id` IS NOT NULL))
    OR (MATCH (`j2`.`rulesText`) AGAINST (? IN BOOLEAN MODE) AND (`j2`.`id` IS NOT NULL))
    OR MATCH (`sorcery`.`Card`.`name`) AGAINST (? IN BOOLEAN MODE))
ORDER BY 
    MATCH (`sorcery`.`Card`.`name`) AGAINST (? IN BOOLEAN MODE) DESC, 
    MATCH (`sorcery`.`Guardian`.`typeText`, `sorcery`.`Guardian`.`rulesText`) AGAINST (? IN BOOLEAN MODE) ASC
LIMIT 
    ? OFFSET ?;

[reubenjh]

Ran the SQL direct against the database and got the following err

target: sorcery.-.primary: vttablet: rpc error: code = NotFound desc = Unknown column 'Guardian.typeText' in 'order clause' (errno 1054) (sqlstate 42S22) (CallerID: c8wo5ic6laxzc2b6yedl): Sql: "select Card.id, Card.slug, Card.`name`, Card.hotscore from Card left join Guardian as j1 on j1.cardId = Card.id left join Guardian as j2 on j2.cardId = Card.id where match(j1.typeText) against (:vtg1 /* VARCHAR */ in boolean mode) and j1.id is not null or match(j2.rulesText) against (:vtg1 /* VARCHAR */ in boolean mode) and j2.id is not null or match(Card.`name`) against (:vtg1 /* VARCHAR */ in boolean mode) order by match(Card.`name`) against (:vtg1 /* VARCHAR */ in boolean mode) desc, match(Guardian.typeText, Guardian.rulesText) against (:vtg1 /* VARCHAR */ in boolean mode) asc limit :vtg2 /* INT64 */, :vtg3 /* INT64 */", BindVars: {REDACTED}

Direct from ChatGPT when plugging the error in in context with the SQL:

The error you're encountering suggests that the column Guardian.typeText is not recognized in the ORDER BY clause. This is because the LEFT JOIN alias j1 and j2 are used in the WHERE clause but not directly accessible in the ORDER BY clause due to how MySQL processes joins and aliases.

To resolve this, ensure that the columns you are ordering by are accessible within the context of the query. One approach is to use derived tables (subqueries) to make sure the columns are available in the final selection. 

I went back to the regular orderBy syntax that references child relation's fields to get the SQL for how its maintaining the reference to the joined column in the orderby clause. See below:

SELECT 
    `sorcery`.`Card`.`id`, 
    `sorcery`.`Card`.`slug`, 
    `sorcery`.`Card`.`name`, 
    `sorcery`.`Card`.`hotscore`
FROM 
    `sorcery`.`Card`
LEFT JOIN 
    `sorcery`.`Guardian` AS `orderby_1` ON (`orderby_1`.`cardId`) = (`sorcery`.`Card`.`id`)
LEFT JOIN 
    `sorcery`.`Guardian` AS `orderby_2` ON (`orderby_2`.`cardId`) = (`sorcery`.`Card`.`id`)
LEFT JOIN 
    `sorcery`.`Guardian` AS `j1` ON (`j1`.`cardId`) = (`sorcery`.`Card`.`id`)
LEFT JOIN 
    `sorcery`.`Guardian` AS `j2` ON (`j2`.`cardId`) = (`sorcery`.`Card`.`id`)
WHERE 
    ((MATCH (`j1`.`typeText`) AGAINST ("teleport" IN BOOLEAN MODE) AND (`j1`.`id` IS NOT NULL))
    OR (MATCH (`j2`.`rulesText`) AGAINST ("teleport" IN BOOLEAN MODE) AND (`j2`.`id` IS NOT NULL))
    OR MATCH (`sorcery`.`Card`.`name`) AGAINST ("teleport" IN BOOLEAN MODE))
ORDER BY 
    `sorcery`.`Card`.`name` ASC, 
    `orderby_1`.`typeText` ASC, 
    `orderby_2`.`rulesText` ASC
LIMIT 
    10 OFFSET 0;

It looks like the key difference is the regular orderBy makes those additional table aliases then references those in the orderby clause. Edited the original SQL to use that pattern and successfully ran that against the database, so it looks like that's the fix here.

SELECT `sorcery`.`Card`.`id`, 
       `sorcery`.`Card`.`slug`, 
       `sorcery`.`Card`.`name`, 
       `sorcery`.`Card`.`hotscore`
FROM `sorcery`.`Card`
LEFT JOIN `sorcery`.`Guardian` AS `orderby_1` ON (`orderby_1`.`cardId`) = (`sorcery`.`Card`.`id`)
LEFT JOIN `sorcery`.`Guardian` AS `orderby_2` ON (`orderby_2`.`cardId`) = (`sorcery`.`Card`.`id`)
LEFT JOIN `sorcery`.`Guardian` AS `j1` ON (`j1`.`cardId`) = (`sorcery`.`Card`.`id`)
LEFT JOIN `sorcery`.`Guardian` AS `j2` ON (`j2`.`cardId`) = (`sorcery`.`Card`.`id`)
WHERE ((MATCH (`j1`.`typeText`) AGAINST ("teleport" IN BOOLEAN MODE) AND (`j1`.`id` IS NOT NULL))
    OR (MATCH (`j2`.`rulesText`) AGAINST ("teleport" IN BOOLEAN MODE) AND (`j2`.`id` IS NOT NULL))
    OR MATCH (`sorcery`.`Card`.`name`) AGAINST ("teleport" IN BOOLEAN MODE))
ORDER BY MATCH (`sorcery`.`Card`.`name`) AGAINST ("teleport" IN BOOLEAN MODE) DESC, 
         MATCH (`orderby_1`.`typeText`) AGAINST ("teleport" IN BOOLEAN MODE) DESC, 
         MATCH (`orderby_2`.`rulesText`) AGAINST ("teleport" IN BOOLEAN MODE) DESC
LIMIT 10 OFFSET 0;

Hope this helps 👍 i love prisma