Allow WHERE conditions to compare columns in same table

[five07]

Problem

Prisma should allow WHERE conditions to compare columns in same table

Suggested solution

Expand WHERE filters to include columns of the same type from the same table

Simple example model

model Shipment {
  id               String @id @default(cuid())
  quantityShipped  Int
  quantityReceived Int
}

Raw SQL:

SELECT * FROM Shipment WHERE quantityReceived < quantityShipped;

Currently for example model:

type IntFilter = {
  equals?: number | null
  not?: number | IntFilter | null
  in?: Enumerable<number> | null
  notIn?: Enumerable<number> | null
  lt?: number | null
  lte?: number | null
  gt?: number | null
  gte?: number | null
}

Could alternately be:

type IntFilter = {
  equals?: number | ShipmentIntColumnInput | null
  not?: number | IntFilter | ShipmentIntColumnInput | null
  in?: Enumerable<number> | Enumerable<ShipmentIntColumnInput> | null
  notIn?: Enumerable<number> | Enumerable<ShipmentIntColumnInput> | null
  lt?: number | ShipmentIntColumnInput | null
  lte?: number | ShipmentIntColumnInput | null
  gt?: number | ShipmentIntColumnInput | null
  gte?: number | ShipmentIntColumnInput | null
}

Alternatives

Currently only raw queries work

Thanks!

[thebiglabasky]

Thanks @five07 for this issue. It does indeed make a lot of sense for us to figure out a way to solve this problem.
We have prisma/prisma-client-js#655 which is related, and that I would like us to start looking into in the coming weeks, so I'll make sure we can consider your use case as well.

[matthewmueller]

One possible solution to represent:

SELECT * FROM Shipment WHERE quantityReceived < quantityShipped;

import { PrismaClient, shipment } from '@prisma/client'

const prisma = new PrismaClient()

prisma.shipment.findMany({
  where: {
    quantityReceived: {
      lt: shipment.quantityShipped
    }
  }
})

Could maybe also use a string lt: 'quantityShipped', but that probably wouldn't work with string values.

Exposing shipment.quantityShipped as a reference field would also cleanup: prisma/prisma-client-js#760
#2880

[thebiglabasky]

After discussing further with the team, this comes with different technical considerations than prisma/prisma-client-js#655
We are prioritizing prisma/prisma-client-js#655 for now and will stage this one for later.

[HarunKilic]

Is there anything new on this one?

[vktrl]

Would like to have it as well. Right now I'm using DB functions that calculate and set extra columns so I can filter my queries.

[ahmednawazkhan]

any update on this ?

[ArthurRF]

Same problem here, any updates?

[thebiglabasky]

The team is currently heads down in implementing support for composite types, which pushed features like this one by quite a bit.
This is still on the list, but will take time. Sorry about that.
We'll update the issue as soon as progress can be made. Stay tuned!

[sctnightcore]

any updates?

[deviprsd]

I would like to point out that this feature should be able to handle use cases such as these (note: the left join query in particular)

SELECT 
    COUNT(g.id) AS 'total_used', 
    s.`product_name`, 
    s.`current_period_start` AS 'current_period_start', 
    s.`current_period_end` AS 'current_period_end'
FROM `subscription` s
    JOIN `user` u ON s.`customer_id` = u.`customer_id`
    LEFT JOIN `goat` g ON g.`user_id` = u.`id` AND
          g.`date_created` BETWEEN s.`current_period_start` AND s.`current_period_end`
    WHERE u.`id` = "00000-00000" AND s.`status` = 'ACTIVE'

[danshapir]

Any updates?
This is a super important feature..

[TomerAberbach]

Here's another use case for this: #11646

[typehorror]

I'm curious if this is gonna be addressed ? I'm likely over-simplifying the issue but this seems like such a fundamental feature of relational database (amount payed < amount due, quantity received < quantity ordered, delivery date < expected delivery date...) that I can't figure out why it's not handled. I understand that it can be dealt with using raw query but then what is the value of Prisma if one has to resort to raw query for basic query.

[psaxde]

I'm also wondering why this is not possible yet. Feels very odd.

[Suleyman1406]

Any update?

[volkankorkmaz]

Any updates? Do you have any ETA about it?

[DuriDuri]

Any updates? Do you have any ETA about it?

[Riz-waan]

I also would like upvote this request. This is an important feature to me.

[Ontegor]

I see that the developer @cprieto is working on this task. Let's hope he solves this problem 👍

[matthewmueller]

@Ontegor yep, we started designing this feature. We'll post an update here once we have something worth sharing!

[volkankorkmaz]

@Ontegor yep, we started designing this feature. We'll post an update here once we have something worth sharing!

Sounds good!

[shtse8]

any updates?

[volkankorkmaz]

Hi,

Do you have any ETA about it?

Thx

[matthewmueller]

Hey folks 👋

We've started designing this feature and we’re ready to share our proposal. Please let us know what you think! I'd also like to invite you to jump on a quick call with us to share your thoughts.

Design

We aim to support type-safe field references on all filters (e.g. where, having, etc.):

Let’s consider the following snippet from a schema.prisma:

model Order {
  id     String @id
  paid   Int
  due    Int
  user   User   @relation(fields: [userId], references: [id])
  userId String
}

model User {
  id     String  @id
  email  String  @unique
  name   String
  tags   String[]
  orders Order[]
}

You can now express a query to find all the orders where paid < due.

await prisma.order.findMany({
  where: {
    paid: {
      lt: prisma.order.fields.due // paid < due
    }
  }
})

This proposal adds a special fields property to every model that contains the list of fields for that model. Using the models above, fields contains the following properties:

const { id, paid, due, userId } = prisma.order.fields
const { id, email, name, tags } prisma.user.fields

Compile-Time Type-Safety

The Typescript compiler ensures that comparisons are only possible on fields of the same type. For example, the following will trigger an error while type-checking:

await prisma.order.findMany({
  where: {
    id: { equals: prisma.order.fields.due }
    // ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    // Type error: id is a string, while amountDue is for integers
  }
})

Comparisons are also currently restricted to being on the same model:

await prisma.order.findMany({
  where: {
    id: { equals: prisma.user.fields.name }
    // ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    // Type error: name is a field on the User model
  }
})

Referencing Scalar Lists

For data sources that support scalar lists (e.g. PostgreSQL), you’ll be also able to reference scalar lists with the in and notIn filters, so you can now express “find all results where one field is within a list of other fields. For example,

await prisma.user.findMany({
  where: {
    // find all users where name is within a list of tags
    name: { in: prisma.user.fields.tags }  
  }
})

We hope you’ll find a better use case for this feature than our example… 😅 Let us know!

Limitations

Referencing fields will only be available in non-unique filters for now. For example, the following would not be possible:

await prisma.user.findUnique({
  where: {
    email: prisma.user.fields.name
  }
})

[shtse8]

Good to see there is a type safety check. But what if I want to compare timestamp (int) and datetime (datetime) column in database? Is it possible to do something like:

await prisma.order.findMany({
  where: {
    id: { equals: { field: prisma.order.fields.due, convertTo: prisma.type.Integer } }
  }
})

await prisma.order.findMany({
  where: {
    createdAtDatetime: { lte: { field: prisma.order.fields.createdAtTimestamp, convertTo: prisma.type.Datetime} }
  }
})

[danshapir]

That looks great, but I'm unsure as to how it works with JOINs. Those are the more realistic scenarios of needing such a feature.
Moreover I think there should be a solution for complex comparison, even if it will be a small part directly written in SQL. Otherwise it will require writing everything in SQL.

[matthewmueller]

But what if I want to compare timestamp (int) and datetime (datetime) column in database?

Thanks for your comment @shtse8. Do you have a use case in mind where you'd like to do this? I'd also be curious what the SQL alternative would look like for this if you have something in mind.

Those are the more realistic scenarios of needing such a feature.

Thanks for your comment @danshapir, can you share an example where you'd need to join with column references?

[danshapir]

But what if I want to compare timestamp (int) and datetime (datetime) column in database?

Thanks for your comment @shtse8. Do you have a use case in mind where you'd like to do this? I'd also be curious what the SQL alternative would look like for this if you have something in mind.

Those are the more realistic scenarios of needing such a feature.

Thanks for your comment @danshapir, can you share an example where you'd need to join with column references?

@matthewmueller Sure.
Assume we have 2 tables.
Accounts and Transactions
We have a field on the Accounts called LinkTime, a datetime.
We want to find all transactions where their createdAt is larger than the LinkTime of their parent account.

That's for the simple query, just comparison of fields from a join.
For the more complex scenario, but something we all do a thousand times using SQL, we create a dynamic property as part of the select (computed of multiple fields or any other logic, or maybe aggregated SUM/MIN) and we want to only fetch items that are larger or equal to it.

[danshapir]

@SevInf How is this closed? The solution doesn't support JOIN queries... The basically main reason for this requirement.

[matthewmueller]

Hey @danshapir, thanks for the feedback!

We've closed this issue because we've implemented the original use case, but the use case around references across tables could be a solid improvement that we'll consider separately.

I've taken a note of your feedback in this issue. We'll be reviewing feedback in that issue in case you have any further suggestions!