Hi everyone, I am fetching a collection of user profiles but want to provide the ability to filter the users by their interest tags which are specified through a multicategory select option. These tags are stored in a separate junction table. When a user filters for specific tag(s), I want to be able to filter the original collection of user profiles.
I can set up a pg function to input those tags as an array and return the applicable users, but that means I have to store the collection in a local object/array, and setting up custom pagination on that local variable seems like a pain.
I guess another alternative is to set up a trigger/function to store an array of values on the users table itself then set up a JS script on the collection filter to see if the column contains at least one value from the multiselect input array.
Is there any other way around this? I think a nice feature request would be Supabase collections w/ a pg function embedded (and I guess auto-fetch disabled) so that in workflows, I can fetch a collection from Supabase which is the output of one or more specific pg function so that we arenât only limited to the basic filter option on collections (while benefitting from the preset behavior of collections such as pagination, etc.)
Iâd suggest you set that up properly - e.g with a table of tags and a table of user_tags where youâll store a reference to the tag, and a reference to the user. Then you can join them, in the client in a very simple manner and do a filter on that join, effectively returning only the items that match your tags. Searching array is kind of not a very simple and effective approach.
Didnât know you can join in the client! Thank you. Can the join be set up as a single collection? Or are you saying pull collection A, pull collection B, then when I display collection A on my page, I also display collection B where id = id?
You actually can do JOINs in a single collection via using the Advanced Field mode Supabase data source | WeWeb Documentation
Oh I see what you mean. Yes, Iâve been using that for other stuff. But consider my use case:
Imagine an Amazon-like marketplace of items. Each item is displayed in a card, and letâs say the card also displays text tags for the item category. For example, you might see a lamp in card 1 with the category tags âfurnitureâ and âhome goods.â And so forth. So in this case, I have multiple category tags that relate back to the single item id.
Currently, I am fetching the collection of items. I am also fetching the collection of item category tags. And I am displaying the collection of items on my page then for each item, displaying the tags from the collection of item category tags that match the item id.
I believe by using join in advanced mode, you are implying that I should fetch just the collection of item category tags then pull columns that I need from the items table. Please correct me if I am misunderstanding you. In this case, how would I display items and avoid a situation in which the same item appears multiple times (for the number of category tags it is linked to)?
You can fetch whatever you need. The joins just spare you the job of doing a lookup which is most often a bad practice (lookups on the front-end). Some apps even fetch just one singe endpoint that gets the whole data for the app 
Thank you again for all your help, really appreciate it. But wanted to clarify your point: so letâs say I have an items table and a tags table. Are you saying instead of fetching items and tags separately (then filtering client-side to match the tags to the items), I should just fetch tags table like the below:
tag_name
item_id (id, name, etc.)
.
.
.
But I am still lost on how I would display the items on the page alongside the tags while avoiding the scenario where I display an item twice if it is associated with two tags. Let me know if I am not making any sense.
If you do fetch the items/products table instead, you can do
*,tags:id(*)
If your itemâs id is referenced in tags, youâll get an array of objects like such:
[
{
"name":"product-1",
"tags": [{"name":"tag-1"},{"name":"tag-2"} ...]
},
{
"name":"product-2",
"tags": [{"name":"tag-5"},{"name":"tag-3"} ...]
},
...
]
If you post your schema/table definitions regarding this issue here, I can write this for you.
1 Like
an absolute game changer. thanks so much!!! I didnât know this was possible.
1 Like
No worries! Itâs kind of tricky
1 Like
Hey, I have a follow up question after implementing your solution (I am fetching the items collection only and getting an array of objects from the tags collection that references the item id). If I wanted to implement a filter feature so that users can filter for just certain tag categories when looking through the items, it seems I cannot do this directly as a collection filter?
For example, I get an error when setting up a filter: tags [tag_id] has any of [1, 2, 3âŚetc.] from a client-side multiselect picker because a âtagsâ column doesnât exist in the primary table to begin with. Do you recommend the filtering to occur entirely client-side? Or is there a way to filter for nested values directly through Wewebâs collection fetching interface? Thanks again for all your time.
Hello, you could probably try using the workflows, I know they have some more advanced options. Other than that unfortunately you either have to filter it on the front-end via formulas, use views (backend) or make your own functions that you call with workflows. Filtering by nested values is currently not supported.
For anyone looking for a solution to this, I actually released a proper article about how to do this kind of complex queries
1 Like
This is a great article, thank you! One question- in the âSupa-efficient data filtering of the joined dataâ section, how do you get to the conditional filter interface (i.e. where in Weweb is that option)?
Itâs only in the Workflow Actions. You could work around this by first ⌠spreading the data and then filtering on top level
1 Like
Hey there, completely different topic- do you have any recommended readings/resources for working w/ REST APIs while using Weweb + Supabase? Iâm unsure where Iâm supposed to store my private keys, when to use edge functions, etc. Itâd be awesome to also see a Weweb-centric âdeploy your first Supabase edge functionâ tutorial if there is one alreadyâŚjust thought Iâd ask, but no worries if you canât think of any off the top of your head! Thank you.
As for the private keys, they should always be hidden behind a backend of some sort, either by an Edge Function, or a Postgres Stored Peocedure.
I think there already is something regarding the Edge Functions by @Joyce, I think she made a tutorial on the topic.
Which approach to use really depends on a âper-case basisâ, I usually discuss this on my 1:1 coaching sessions where people tell me their needs and we
- find the most optimal solution and
- I teach them how to make this solution work for them.