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)
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.
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.
Set references in your tables, you have book_type.book_id, make it bound to book.id and the same for type_id
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
CREATE VIEW many_to_many_join -- Your View Name
AS
select auto.name, auto.model, job.address, job.city -- 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 auto.id = job_auto.auto_id -- Now you join it with your intermediary table, book_type, on book_type.book_id = book_list.id
join job on job.id = job_auto.job_id -- Now you join the intermediary table with your other table type_list, book_type, on book_type.type_id = type_list.id
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.
