CTE query not working properly | Prisma | Page 1

nova magnet Oct 18, 2024, 6:37 PM

#

Look at attached photo for error code and query. This query works when running against local DB, but doesn't work when running against deployed DB.

I'm able to run this query against both DBs using raw sql outside of Prisma.

I'd love some help here!

On v5.7.0

tawdry summit Oct 18, 2024, 8:19 PM

#

Are you using typedsql?

https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/typedsql

Writing Type-safe SQL with TypedSQL and Prisma Client | Prisma Docu...

Learn how to use TypedSQL to write fully type-safe SQL queries that are compatible with any SQL console and Prisma Client.

#

I haven't tried a CTE with it, but definitely like how this works much better than just having sql inline with your code

nova magnet Oct 18, 2024, 8:46 PM

#

Does that really change anything though? It's still the same query being executed

tawdry summit Oct 18, 2024, 8:58 PM

#

I had plenty of issues doing it the other way, using the typed sql I was actually able to get it to work

nova magnet Oct 18, 2024, 11:48 PM

#

Interesting, I'll give it a go

nova magnet Oct 19, 2024, 7:32 AM

#

I tried and it didn't work 😦

tawdry summit Oct 19, 2024, 2:25 PM

#

hrm, lemme play with my setup a sec and see if I can replicate it

#

Oh, check if you can issue a query without the CTE's

#

Ok, did a very basic CTE and it worked. I had issues, like omfg issues, using IN before. Also your ? has to be $1, $2... etc

#

Here's my initial test query

with something as (
    select * from "User"
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

and code I used to call it

const { cteTest } = require('@prisma/client/sql');
const { db } = require('../utils/db');

router.all('/', async (req, res, next) => {
  const response = await db.$queryRawTyped(cteTest());
  res.status(200).send(response);
});

Also, make sure you are using npx prisma generate --sql or it won't find your query

#

Ok, this query worked

with something as (
    select * from "User"
    where email in ($1, $2)
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

#

This did not work however

with something as (
    select * from "User"
    where email ANY($1)
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

nor did this

with something as (
    select * from "User"
    where email in ($1)
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

And this won't even run

with something as (
    select * from "User"
    where email in $1
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

#

@elder pelican @golden cargo I know the docs say to use ANY for these things, but I've yet to get it to work. All I see is this kind of output

    at async /home/jared/Work/mvp-rest-api/src/api/index.js:19:20 {
  code: 'P2010',
  clientVersion: '5.20.0',
  meta: {
    code: '42883',
    message: 'ERROR: operator does not exist: text = text[]\n' +
      'HINT: No operator matches the given name and argument types. You might need to add explicit type casts.'
  }
}

#

local: PostgreSQL 16.4 (Ubuntu 16.4-0ubuntu0.24.04.2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit

remote: PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.3.1 20240522 (Red Hat 13.3.1-1), 64-bit (Digital Ocean)

#CTE query not working properly