I need to join two tables that don't have a direct link, by using nested data
I have table 1 that looks like
{
NumberIdentifier: "Test",
NestedData: {
OtherIdentifier: "Test2"
}
}
And table 2 that looks like
{
NumberIdentifier: "Test",
OtherIdentifer: "Test2"
}
Number identifer cannot be guaranteed to be unique, so I'm trying to join on both
I've gotten as far as
Table1.findMany( {
select: {
Table2: {
OtherIdentifier: true
}}}}
But can't find any way to finish this without doing post query data processing, which I really want to avoid if possible.
I'm open to computed fields, but those don't seem to work on nested data either.
3 Replies
You've gone full bot mode! Let me fetch that AI answer for you ASAP.
I'm sorry, but Prisma currently does not support joining two tables on arbitrary fields (including nested fields or fields that are not defined as relations/foreign keys) using the Prisma Client API. Prisma's relation queries (
- Direct joins on arbitrary or nested fields are not supported in Prisma Client. - Workarounds: Use
include, select on relations) require explicit relations defined in your Prisma schema, typically via foreign keys. There is no built-in way to perform a join on multiple arbitrary fields or on nested JSON fields directly in a Prisma query.
This limitation is a known feature request in the Prisma community. The only current workaround is to use raw SQL queries via $queryRaw, but this loses the type safety and convenience of Prisma's fluent API. Post-processing in JavaScript after fetching the data is also a common workaround, but as you noted, it is not ideal for performance or code clarity. Computed fields and virtual relations also do not solve this, as they cannot reference nested or arbitrary fields for joining in the way you describe SQL joins without relations/foreign keys/raw query Select query, join table which is not directly referenced via Foreign Key.
In summary:- Direct joins on arbitrary or nested fields are not supported in Prisma Client. - Workarounds: Use
$queryRaw for custom SQL, or fetch and join data in your application code.
If this is a critical feature for your use case, consider upvoting or commenting on the related GitHub issue to help prioritize its development.
🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly.
In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.Hi team member, I'd like to request info on whether any of the following are in any future plans for prisma releases:
- allowing a join between unrelated tables on fields they have in common
- allowing creation of a computed field that uses a value in a nested item on a table
- where clauses that allow you to say I want everything from table2 where it matches this other field of table1 (example: select ..... from table1 left join table2 where table1.table2id = table2.id && table1.category = table2.category)
We have a related feature request here:
https://github.com/prisma/prisma/issues/13517
I would recommend adding a 👍 to the request so that our team can prioritise it
GitHub
SQL joins without relations/foreign keys/raw query · Issue #13517 ...
Problem I sometimes find myself wanting to join 2 tables that are related by a column, but don't have referential integrity. For example, let's say I have 2 tables, Transaction and SmartCon...