2

I've been struggling with this for a while but with no success.

I have two tables that might have a relation but not necessarily.

FIRST
+----+-----------+------------+
| ID | LPR       | TIMESTAMP  |
+----+-----------+------------+
| 1  | QWE123RTY | 05-05-2020 |
+----+-----------+------------+
| 2  | ZXC789IOP | 05-05-2020 |
+----+-----------+------------+
| 3  | ASD567FGH | 05-05-2020 |
+----+-----------+------------+

SECOND
+----+-----------+------------+----------+
| ID | LPR       | TIMESTAMP  | OWNER_ID |
+----+-----------+------------+----------+
| 1  | AAA111BBB | 04-05-2020 | 3        |
+----+-----------+------------+----------+
| 2  | QWE123RTY | 02-05-2020 | 1        |
+----+-----------+------------+----------+
| 3  | BBB222CCC | 14-05-2020 | 1        |
+----+-----------+------------+----------+

I basically want to replicate SELECT * FROM FIRST JOIN SECOND WHERE LPR="QWE123RTY" in prisma but to no avail. I cannot use a foreign key (at least to my knowledge) since the foreign key in SECOND might not always be present in FIRST as vice-versa.

An alternative that I think might work is to run two separate queries where I retrieve the matching records in SECOND and then run

prisma.FIRST.findMany({
    where: {
        LPR: { in: ['QWE123RTY', 'BBB222CCC'] }
    }
})

Has anyone actually managed to do something like that?

V. S.
  • 43
  • 1
  • 7
  • Did you read the manual specifically for full joins in mysql https://www.prisma.io/dataguide/mysql/reading-and-querying-data/joining-tables NB you don't need foreign keys to join tables.. – P.Salmon May 13 '22 at 10:52
  • I forgot to specify that I wanted to use the PrismaClient to do so. – V. S. May 13 '22 at 12:05

1 Answers1

1

I have found a solution to my problem, by using prisma.$queryRaw which I actually didn't know it existed for the few months I've been using it and only stumbled upon it now. Here's the documentation link for reference: Prisma - Raw database access

The end solution was:

await prisma.$queryRaw`SELECT * FROM FIRST JOIN SECOND ON FIRST.LPR=SECOND.LPR`

P.S. The only issue I ran into by using the queryRaw method instead of the regular Prisma Client was that the BLOB type values are retrieved as strings, instead of as a Buffer, but that can be handled both on the front-end as well as in the back-end accordingly with minor modifications to the response.

V. S.
  • 43
  • 1
  • 7
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 14 '22 at 01:23
  • 1
    Thank you for your comment, will update it promptly. – V. S. May 14 '22 at 08:48