Add full support for filtering by relations

[RobertCraigie]

Problem

Currently only support nested filtering (through the include argument), this means that even if nothing matches the nested filter the base filter still passes, which could be confusing.

Suggested solution

Add relational fields to WhereInput, e.g.

model Post {
  ...
  categories Category[] @relation(references: [id])
}

model Category {
  id    Int    @id @default(autoincrement())
  posts Post[] @relation(references: [id])
  name  String
}

class PostWhereInput(TypedDict, total=False):
    ...
    categories: Optional['CategoryListRelationalFilter']

class CategoryListRelationalFilter(TypedDict, total=False):
    every: Optional['CategoryWhereInput']
    some: Optional['CategoryWhereInput']
    none: Optional['CategoryWhereInput']

Additional context

Playground schema screenshot

[danielweil]

Is this feature available in v0.4?

I have been trying to use relation filter in where clauses but I keep getting Field does not exist on enclosing type.atQuery.findUniqueRole.where.RoleWhereUniqueInput.User.

I have tried it in some different ways:

[RobertCraigie]

@danielweil You need to wrap the relational field query with either is or is_not for one-to-one relations and either some, none or every for one-to many / many-to-many relations.

https://prisma-client-py.readthedocs.io/en/latest/reference/operations/#filtering-by-relational-fields

In your case it should be:

role = await client.role.find_first(
    where={
        'User': {
            'email': email,
        },
    },
)

Also it looks like you're using VSCode, if so you should definitely install the Pylance extension which will suggest the query arguments for you when you trigger auto-complete: https://github.com/RobertCraigie/prisma-client-py/blob/main/docs/showcase.gif

[danielweil]

Thanks!

[danielweil]

Do I have to apply any setting for the autocomplete to work? Here it still doesn't show up

[RobertCraigie]

You have to manually trigger autocomplete: https://code.visualstudio.com/docs/editor/intellisense#_intellisense-features

The Client instance also has to be resolvable by the type checker, i.e. if you hover over it should say something like client: Client

[danielweil]

It does not.

[RobertCraigie]

Ah so that's your problem, you need to annotate your get_client() function to return prisma.Client, e.g.

from prisma import Client

async def get_client() -> Client:
    ...

May I ask what your use case is for writing your own Client factory?

[danielweil]

It already is.

Can this be related to my python running in WSL?

I am writing it because I use Azure AD postgres JWT access to database.

[RobertCraigie]

The issue might be with any part of DBClient.instance() then, is that correctly resolved?

[danielweil]

About find_first relational query: It gave this error message....

Failed to validate the query: Field does not exist on enclosing type. at `Query.findFirstRole.where.RoleWhereInput.User.UserListRelationFilter.email

[RobertCraigie]

Can this be related to my python running in WSL?

I don't think so, I've used Python with WSL before without any issues

I am writing it because I use Azure AD postgres JWT access to database.

Ah I see, is this because you have to update the access token?

[RobertCraigie]

Failed to validate the query: Field does not exist on enclosing type. at `Query.findFirstRole.where.RoleWhereInput.User.UserListRelationFilter.email

@danielweil I'm so sorry I just realised I gave you the wrong query, it should be:

role = await client.role.find_first(
    where={
        'User': {
            'is': {
                'email': email,
            },
        },
    },
)

[danielweil]

Ah I see, is this because you have to update the access token?

Yep.

About the Client type, It is very weird why it doesn't work. Check this out!

[RobertCraigie]

That is bizarre, can you share how you're importing the client please?

[danielweil]

Failed to validate the query: Field does not exist on enclosing type. at `Query.findFirstRole.where.RoleWhereInput.User.UserListRelationFilter.email

@danielweil I'm so sorry I just realised I gave you the wrong query, it should be:

role = await client.role.find_first(
    where={
        'User': {
            'is': {
                'email': email,
            },
        },
    },
)

Might be something here!

Failed to validate the query: Field does not exist on enclosing type. at Query.findFirstRole.where.RoleWhereInput.User.UserListRelationFilter.is

[danielweil]

That is bizarre, can you share how you're importing the client please?

Very

[RobertCraigie]

Failed to validate the query: Field does not exist on enclosing type. at Query.findFirstRole.where.RoleWhereInput.User.UserListRelationFilter.is

Ah sorry I missed that it was a list relation, replace is with every

[danielweil]

Failed to validate the query: Field does not exist on enclosing type. at Query.findFirstRole.where.RoleWhereInput.User.UserListRelationFilter.is

Ah sorry I missed that it was a list relation, replace is with every

That worked out!

[RobertCraigie]

@danielweil Glad I could help!

About the Client type, does hovering over the import resolve correctly?

[RobertCraigie]

If that does, are you overriding the Client name anywhere?

[danielweil]

It does not and I do not override it anywhere

[RobertCraigie]

That sounds like VSCode is using the wrong python interpreter then. Are you using a venv?

[RobertCraigie]

At the bottom left of your screen you should see something like this:

Click on the button that says Python ... and select the python interpreter that you're using.

[danielweil]

I opened VSCode in a remote session and now the imports show the correct class. By my get_client method still does not. I guess it is because of the async, but I can't get it to work.

On other hand, I have another issue with upsert:

I am trying to use it as a findOrCreate idea, but something in the where clause is causing an error:

Failed to validate the query: Field does not exist on enclosing type. at `Mutation.upsertOneUser.where.UserWhereUniqueInput.email

[RobertCraigie]

Failed to validate the query: Field does not exist on enclosing type. at `Mutation.upsertOneUser.where.UserWhereUniqueInput.email

You need to add an @unique to the email field definition.

model User {
  //
  email String @db.VarChar(255) @unique
  //
}

[danielweil]

Failed to validate the query: Field does not exist on enclosing type. at `Mutation.upsertOneUser.where.UserWhereUniqueInput.email

You need to add an @unique to the email field definition.

model User {
  //
  email String @db.VarChar(255) @unique
  //
}

Thanks!

[RobertCraigie]

I opened VSCode in a remote session and now the imports show the correct class. By my get_client method still does not. I guess it is because of the async, but I can't get it to work.

Do you know what doesn't work? Is the Client return type resolved correctly?

[danielweil]

Not by my function:

How should it be with async functions in python? I tried Coroutine but failed.

[RobertCraigie]

The issue is that you've typed the instance() function incorrectly. It needs to be typed to return whatever class implements the get_client() method.

[danielweil]

Thanks! worked perfectly

[farzad-salimijazi]

Failed to validate the query: Field does not exist on enclosing type. at `Query.findFirstRole.where.RoleWhereInput.User.UserListRelationFilter.email

@danielweil I'm so sorry I just realised I gave you the wrong query, it should be:

role = await client.role.find_first(
    where={
        'User': {
            'is': {
                'email': email,
            },
        },
    },
)

Might be something here!

Failed to validate the query: Field does not exist on enclosing type. at Query.findFirstRole.where.RoleWhereInput.User.UserListRelationFilter.is

Thanks! worked perfectly

@danielweil how did you solve this ? we tried "is", and "every" according to the documents but we can't query based on relation fields? any suggestions?

[RobertCraigie]

@farzad-salimijazi I'm sorry you're encountering difficulties, could you share your schema and the query you're trying to write so I can offer some help :)