Wasp•2y ago

Writing raw queries

Hi, is there a way to write a raw query? It seems like context.$queryRaw is not a function

12 Replies

hmm we don't expose it through context right now, but trying to think if there is some workaround you could use right now. What do you need it for exactly?

Zeko369•2y ago

matijash•2y ago

nice @Zeko369 ! This should def solve the issue but just to note it isn't the part of the official API yet. It would still be great to learn about your specific use case @GreatValorKing 🙂

Matteo Kinkela (GreatValorKing)OP•2y ago

This is the first time using Prisma, I needed to join 3 tables. Prisma docs and ChatGPT weren't helpful. In the end, I actually came up with the came code as @Zeko369, but I thought that there's gonna be something more official. Anyways thanks for help, that was quite quick 🙂

Zeko369•2y ago

Hmm, what do you mean join 3 tables? You can with include I.e.

entities.User.findUnique({
  where: {id: 123},
  includes: {
    posts: {
      includes: {
        comments: true
      }
    }
  }
})

entities.User.findUnique({
  where: {id: 123},
  includes: {
    posts: {
      includes: {
        comments: true
      }
    }
  }
})

shayne•2y ago

thanks for the help on this @Zeko369 that is an "unofficial" way to do it but should be stable for now. and yes, using Prisma for relationship includes would be ideal if you can @GreatValorKing 👍

Filip•2y ago

And Prisma's API should generally be enough for most CRUD apps. Resorting to raw queries should rarely be necessary.

mm•5mo ago

I came accross a scenario where I need to write raw queries, is there a way for this now? Scenario,

Table A 
|____Table B
|____Table C

Table A 
|____Table B
|____Table C

Basically table B and Table C have different purpose but share the same ids

Table B:
- tableAId
- otherId

Table C
- tableAId
- otherId

Table B:
- tableAId
- otherId

Table C
- tableAId
- otherId

In short they are closely related, while querying Table B, I needed to get the matching data from Table C as well and merge them however they are not directly related (no FKs) What I need to do - Perform an inner join with Table B and C to merge the data However Prisma does not support this feature yet, you can only join those that are directly related (thru FKs) So the only solution is to perform a raw query

SELECT tb.*, tc.* 
FROM tableB tb
INNER JOIN tableC tc
ON tb.tableAId = tc.tableBId
AND tb.otherId = tc.otherId

SELECT tb.*, tc.* 
FROM tableB tb
INNER JOIN tableC tc
ON tb.tableAId = tc.tableBId
AND tb.otherId = tc.otherId

A workaround using prisma is querying both tables and mapping but that is not very efficient I think

Filip•5mo ago

Hey @mm, nice to see you again! 😄 You can import the Prisma cilent for full control, as documented here: https://wasp-lang.dev/docs/data-model/entities#using-entities-directly And then just that to perform the raw query.

Filip•5mo ago

Thanks for the detailed explanation of the problem, by the way (as always :)) And props for finding the old thread too!

mm•5mo ago

OMG I missed this one in the docs. thank you so much!

Filip•5mo ago

Glad I could help!

Gaming

Programming

Writing raw queries