Filter nodes based on related aggregations

[matthiasak]

I'd like to add a few more pragmas for filtering. Currently we can filter a direct entity for certain in, contains, startsWith, etc filters, like:

{
  allGroups(filter: {
    members_every: { verified: true }
  }){
    name
  }
}

But, we can't make a query on allGroups filtering by metadata or aggregates. One idea is to enable special filters that test sub-entities such as the below example query where I would like to filter for groups who have atleast 1 member

{
  allGroups(filter: {
    count_gte: [0, {members: {verified: true}}]
  }){
    name
  }
}

[marktani]

Related to #70.

[kabriel]

I need to be able to query for all objects (lets say Cars) that do not have an owner (User) relation set. I'm not sure of the best syntax:

{
  allCars(
    filter: {
      owner_none
    }
  ) {
    id
  }
}

[marktani]

I think for to-one relations this syntax would work best:

{
  allCars(
    filter: {
      owner: null
    }
  ) {
    id
  }
}

and for to-many:

{
  allOwners(
    filter: {
      cars: []
    }
  ) {
    id
  }
}

[marktani]

@kabriel, it's now possible to query nodes that are not related in a to-one relation:

{
  noOwner: allCars(
    filter: {
      owner: null
    }
  ) {
    id
  }
  someOwner: allCars(
    filter: {
      owner_not: null
    }
  ) {
    id
  }
}

[matthiasak]

@marktani @kabriel this feature seems incomplete.

Graphcool console is giving errors on the following:

null works, but I can't check for not_null or exists...

[matthiasak]

workaround is slightly varied checks based on nesting level and ways to check...

persons: allPersons(
            filter:{
                verified: false
                ${valid ? `, avatar: {id_not:null}, name_not: "", bio_not: null, bio_not: "", user: {id_not:null}` : ''}
                ${invalid ? `, OR: [{avatar: {id: null}}, {name: ""}, {bio: null}, {user: {id: null}}]` : ''}
            }
            , first: ${pageSize}
            , skip: ${pageSize * page}
            , orderBy: ${orderBy}
        )

[marktani]

@matthiasak the syntax is avatar: null and avatar_not: null. Did you try that?

[matthiasak]

I did. avatar_not was giving errors Cheers, Matt Keas http://mkeas.org

…

On Apr 25, 2017 3:43 AM, "Nilan Marktanner" ***@***.***> wrote: @matthiasak <https://github.com/matthiasak> the syntax is avatar: null and avatar_not: null. Did you try that? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://github.com/graphcool/feature-requests/issues/110#issuecomment-296961670>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA2-p6Uic2r2ijA-pcDkuhpvsfMvCa2Xks5rzbI0gaJpZM4MG5iF> .

[marktani]

Please create a issue report here: https://github.com/graphcool/api-bugs/issues 🙂

[marktani]

Thanks to @mewdriller for this workaround to query nodes that are not connected in a to-many relation:

{
  allOwners(
    filter: {
      cars_every: {
        id: "-1"
      }
    }
  ) {
    id
  }
}

[adrianocola]

The following syntax seems to be working now for filtering nodes that are not connected in a to-many relation:

{
  allOwners(
    filter: {
      cars_every: {
        id: null
      }
    }
  ) {
    id
  }
}

And to do the opposite (filter nodes that have connections in a to-many relation), this is working:

{
  allOwners(
    filter: {
      cars_some: {
        id_not: null
      }
    }
  ) {
    id
  }
}

cars_some: { id_not: "-1" } works too.

This is the right way to do it, comparing id to null?

[sorenbs]

Thank you for this great suggestion!

Please have a look in the spec proposal to see if all your needs are met:-) https://github.com/graphcool/framework/issues/1279

[samuela]

I came across this problem today as well. I have a data model that looks something like:

type User {
  id: ID! @unique
  createdAt: DateTime!
  updatedAt: DateTime!

  emailAndPassAuth: EmailAndPasswordAuth @relation(name: "UserEmailAndPassAuth", onDelete: CASCADE)
  # githubAuth: GithubAuth
  # etc...
}

type EmailAndPasswordAuth {
  id: ID! @unique
  email: String! @unique
  passwordHashed: String!
  user: User! @relation(name: "UserEmailAndPassAuth")
}

Now, I get a new login attempt with an email and a password. I can find the corresponding EmailAndPasswordAuth node without any trouble, but getting the ID of the corresponding User is a real pain. In SQL it wouldn't be an issue since it's right there in the joint table, but now I'm stuck with two different queries: one to find the EmailAndPasswordAuth and then check that it matches, and yet another to then find the User node matching it.

[sorenbs]

@samuela - I might be missing something obvious, so please let me know if I am :-)

Can you not do a single query like this?

query {
  EmailAndPasswordAuths(where: email: $email, passwordHashed: $password) {
    user {
      id
    }
  }
}

[samuela]

@sorenbs Ah, so it is a little bit trickier than that because the salt is also contained in passwordHashed! So what I really need to is something like

• fetch the EmailAndPasswordAuths record for the given email,
• bcrypt.compare(password, passwordHashed) or something like that,
• if things check out, get the User id for that EmailAndPasswordAuths record. In SQL this would just be a foreign key in that table or whatever. But AFAIU prisma client doesn't offer a way to do this without a second db query: db.emailAndPasswordAuth({ id: nodeId }).user().

I came across this issue after a bit of googling about the problem, but on further inspection it looks like this may not be immediately relevant to the issue in this thread, so I'd be happy to move this elsewhere if you think that makes sense.