Fetch all records with entries that do not exist in a join table

[kvnam]

Hey,

I have a set of tables, say table users, projects and users_projects. The last is a join table with user_id and project_id as the composite primary key. Using Postgrest I want to query for all users that are not linked to a project. So all users with no entry in the users_projects table.

I tried users_projects.project_id!inner.id=is.null which does not work like I want because there are no entries with null values so the results are empty.

I know that in SQL we can use NOT EXISTS but how would I achieve that with Postgrest? Is there a way to do this? With regards to setting up a stored procedure, I need to retrieve different columns from these tables dynamically so I'm not sure how to address that in a stored procedure..

Would be great if anyone has any suggestions on how to approach this. Thanks!

[laurenceisla]

Hi! I think what you need here is the ANTI JOIN mentioned here: #2584 (comment) . In your case, the query would be:

GET /users?select=*,users_projects()&users_projects=is.null

Also, if the users_projects table references both foreign keys and has them as a composite PK like you mentioned, then you can get the same result embedding projects directly:

GET /users?select=*,projects()&projects=is.null

This is available in the latest pre-release.

[kvnam]

Thanks @laurence-isla - I'll give this a try and post back 👍

[kvnam]

@laurence-isla - Apologies for not getting back earlier I know it's been a while, but I would really appreciate it if you could take a look at the issue I'm facing. I'm not able to get this to work from a join table to a nested relation table. For example, say I had a join table called users_files which has a foreign key(part composite primary key) on users called file_id which links to the files table, and another join table files_tags. I want to retrieve all tags linked to a users files. So my query is

GET /users_files?select=files(id,path,files_tags(id, name))

Now if I want to fetch all users that don't have any tagged files, I tried

GET /users_files?select=files(id,path,files_tags(id, name))&files.files_tags=is.null

This throws an error 42703 error that column files_1.files_tags does not exist. I don't know where the files_1 is coming from, but this is not a column, similar to the example you gave. Will this not work with a related table? I would love to know if I'm doing something wrong, or if this is currently not supported. Thanks!

[laurenceisla]

According to these tests, that query should work with nested embedding. I get a similar error to files_1.files_tags when I made a typo in the relationship name when testing this. When you do this, does it give you a similar error?

GET /users_files?select=files(id,path,ft:files_tags(id, name))&files.ft=is.null

Also verify that you are using the latest pre-release and try reloading the Schema Cache. If it's still failing then a minimal reproducible example of this error (with SQL table creations), would help a lot.

[kvnam]

Thank you for confirming @laurence-isla! I tried the version you suggested with ft name, but I still get the same error except this time it's "column files_1.ft does not exist" does not exist. But this is all due to the fact that I'm not using the pre-release version of Postgrest, I should have checked this earlier 🤦‍♀️ apologies for that. I'll try and test against that and update if I face the same error, thanks again!