Filter elements for a many2many database

Hi there !

I am very new to WeWeb but building my app has been pretty smooth so far, but I have this “How do I do this” question about filtering my elements in a many-to-many database (using supabase here)

Let’s say my db looks like this :

id:1 - name:book1 - pages:563
id:2 - name:book2 - pages:336

id:1 - name:manga
id:2 - name:fantasy
id:3 - name:action

id:1 - book_id:1 - type_id:1
id:2 - book_id:1 - type_id:3
id:3 - book_id:2 - type_id:2

So basically : book1 is an action manga and book2 is fantasy, I hope you get the idea.

Now in WeWeb I have my Collection List items set to “book_list”, which allows me to display all my books and the amount of pages, great !

I have also created a multiselect element, linked to my type_list collection, with something like this : rollup(type_list,“name”,“distinct”) to get all the different types (in this case manga fantasy and action).

But now, how do I actually filter my elements from my Collection List using the user selection from my multiselect ? In the filter form from the item collection I only have access to the rows from my book_list collection.

Thank you very much

Option 1. Set a where filter in the supabase plugin, and bind it to is equal to your select and the equivalent value. Select ignore if null. Then on change of the fetch the collection.

Option 2. Filter it via WeWeb, there is a tutorial somewhere, it’s fairly simple

I may have misunderstood though, could you provide more info?

Okay, so I read your post again, and you have two options, as currently, the Supabase Plugin is not very practical in some aspects, one of which is filtering foreign key relationships.

  1. Set references in your tables, you have book_type.book_id, make it bound to and the same for type_id
  2. I have the following tables, similar to yours, I have job, job_auto and auto, they’re bound via auto_id and job_id in the job_auto, so check the structure out

  1. Go to SQL, and create a VIEW, via a JOIN of the two tables like this, it’s all explained in the code snippet bellow

CREATE VIEW many_to_many_join -- Your View Name
select, auto.model, job.address, -- Your desired columns from each joined table
-- Here begins the JOIN tables part, so you're selecting FROM joined table ..
FROM auto -- This is the first table, in your case would be book_list
join job_auto on = job_auto.auto_id -- Now you join it with your intermediary table, book_type, on book_type.book_id =
join job on = job_auto.job_id -- Now you join the intermediary table with your other table type_list, book_type, on book_type.type_id =
  1. Now you can query the joined table via you Supabase Plugin, and filter it via the two ways I gave you above.

So far this was the simpler approach, you can make this happen in supabase plugin as well, following the PostgREST guide here, I’ve done it, and it’s terrible, even though very powerful. If you’d be interested in this guide as well, just say so, I’m not gonna be writing it now, because it’s very complicated.

Also, a tip for the future, you might want to name your tables a little better, for book_list I’d go with book, and for type_list I’d go with type, unless you already have a type and a book somewhere. It’s a convention, that would make you for example write those querries faster and it’s more natural. You might want to check this course out, it helped me a lot in the beginnings. This guy is amazing.

Thank you very much for the detailed answer and the tip. Greatly appreciated

1 Like