Wrong SQL query generated for many-to-many relationships?

[andybar2]

Hi all,

We are using graphjin with the following database model:

Where we have 2 many-to-many relationships between organizations and users, which are organization_users and posts.

When we try to get all the organization_users joined to the users data, sometimes the query works fine and other times it doesn't return anything. This is the graphql query that we are using:

query { 
  organization_users (where: {organization_id: $id}) {
    role
    users {
      id
      name
      email
    }
  }
}

We inspected the SQL query that graphjin is generating, and found that when the query is not returning any results is because it is trying to do an EXISTS on the posts table, although we are not asking anything from the posts in our graphql query.

Here is the SQL query that graphjin generates in that case:

Questions:

• Is this a known bug?
• Is there a way to workaround this issue?

Thanks!

[dosco]

@andybar2 i'll take a look in the meanwhile try changing users to user

query { 
  organization_users (where: {organization_id: $id}) {
    role
    user {
      id
      name
      email
    }
  }
}

[dosco]

This SQL still does not match the query and I suspect this is because you have filters added in via the config file on the users table. I suspect the a filter similar to [{ organization_users: { organization_id: $id } }] is added to the users table and a very similiar filter [{ organization_id: $id }] is added on the organization_users table as well maybe for one of the roles like member maybe.

[andybar2]

Hi @dosco! That's right, we have the following filters in our configuration:

// users table
core.Query{
  Filters: []string{"{ organization: { id: { _eq: $id } } }"},
}

// organization_users table
core.Query{
  Filters: []string{"{ organization_id: { _eq: $id } }"},
}

// posts table
core.Query{
  Filters: []string{"{ organization_id: { _eq: $id } }"},
}

[andybar2]

Hi again @dosco,

Is it possible that the filter that we have in the users table is making the query go through the posts table sometimes instead of the organization_users table since we are not mentioning the organization_users table in that filter then?

// users table
core.Query{
  Filters: []string{"{ organization: { id: { _eq: $id } } }"},
}

[dosco]

Yes, by applying the filter { organization: { id: { _eq: $id } } } to users is causing GraphJin to find a path from the users table to the orginzation table and looking at the image you posted on top there are two similiar sized paths its just that GraphJin is picking the one though posts.

Solution: You actually don't need this filter as your original query will only show users related to the organization by nesting user under organization_users you are essentially doing the same.

Note: Filters are good for enforcing query wide things like { disabled: false } where you never want disabled users to be shown.

[andybar2]

Great, thanks @dosco !

I've just tried replacing that filter on the users table with the following one to make it deterministic and go through organization_users, and it seems to be working fine now:

core.Query{
  Filters: []string{"{ organization_users: { organization_id: { _eq: $id } } }"},
}