Is it possible to make a join query with Prisma without a foreign key?

Question

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?

Answer 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.