Hey all
Just curious if this is new or has been there for a long time.
When creating or doing something with collections I’ll occasionally have a play around to see if there’s any functionality that’s not in the docs and I haven’t seen mentioned.
I just found out that you can count relational data in the collections advanced query field.
eg.
id,
name,
date,
related_table(count)
Just wondering if this is a new feature and what else there might be if anyone knows any?
This has been a thing for a while, @Broberto has made an awesome article going indepth about advanced queries!
I’ve used this guide so many times (thanks Boberto) I don’t remember ever seeing anything about using count like this though in that specific guide.
Guessing you might be able to find this information if you dig into the SDK V2 update possibly just not directly in Bobertos guide as far as I can see.
I’m only recently started on supabase and postgres but fairly confident at coding so I’m still learning what’s possible.
Pretty familiar with all the advanced join stuff now but would be interested to hear if there’s any other tips or tricks you have for collection/select advanced stuff that’s not mentioned in Bobertos guide.
Definitely dive into their SDK, the count stuff is referenced in there!
Honestly still learning all this backend stuff myself, so I don’t know too much more. 
Yea I think even this itself is a little different from what I’ve done this is the way that weweb does the select count under the hood with the prefer optional parameter. Which means this isn’t available to do in advanced field collections on a related table as it’s an extra parameter and weweb does this automatically on the main table. Example from the call in network tab…
But the way I’ve found it is actually within rest call url itself which allows relational table calls. Here’s the difference
Relational table count
Main table count
They look similar but obviously being able to count on a related table without pulling in all the rows can be super beneficial.
You might be able to do this with the Workflow actions? I know there is an option to do bunch of actions on fk tables
You can do aggregate functions if you allow them in supabase (it’s a postgres command but it is advised not to do this, I haven’t tested what you can do with these yet but I imagine you can do the same thing)
This itself doesn’t require that. Just saying this is actually doable in collection configuration advanced fields.
I’ve just tested again and interestingly enough if you do an ambiguous column name it asks you do verify which one you want and suggests using an fkey to use. I’ve had to crop some things out sorry, but this is the general gist. I imagine if you also allow aggregate functions in supabase you can do a similar thing with those.
I’ve double checked in supabase and this count is correct. I haven’t tested how far you can take this as my original purpose was to just get a count of related records to each row of data I was fetching.
Sorry if I’ve misunderstood you though but you don’t need to do this from the Select workflow action although you can do the same thing in the advanced fields there as that’s where I originally tested. I just haven’t seen this mentioned at all before and thought it was interesting.
I’m currently unsure if it’s currently looking at a column name or table name but if you just put the table name you want that has a relation to the table you’re fetching you’ll get an error with suggestions back that you can try.
Yeah I mean, you can do much with this syntax, but at some point it gets super heavy. Especially if you have to disambiguate the columns often, you’ll get a messy thing, like just queries inside queries inside queries. That’s why I most of the time prefer to use the Select, it makes it tidier and more readable when I need to debug something fast, or when working with someone else. Nontheless, you’re right, you can do all kinds of queries inside the Advanced tab of Collections as well.
Yea I don’t advise people to do this on unfiltered collections either as it’ll probably have huge performance issues. Curious as to how you’d do this in the Select that would make it easier to read?
I actually find the select worse cause you can’t increase the size of the advanced fields box and only get like 3-4 lines in a view and have to keep scrolling, so editing the advanced fields on big queries is a pain, but I believe that’s just a matter of preference. I only tend to use the select in certain scenarios where I can’t do what I need with a collection or I’ll use a postgres function.
I usually try to use selects the least possible as well, but sometimes, it’s just more confortable to “make your own collection” because you might need to do some other things, before actually using the data - with the data. Having a Global Workflow as a Collection is a great way of doing that.
Yea agreed if things get to complex, I tend to gravitate towards Postgres functions as the make your own collection. I can do multiple different things with them that might require 2+ selects in weweb and return it as 1 and it feels easier to read complex joins but obviously require some PostgreSQL knowledge so aren’t for everyone. With some good documentation they’re pretty easy to manage as well.
I like to use explain and analyse a lot as well to see what’s happening with queries and figure out where I can improve performance which can be a pain to read from doing it in weweb.
It always just depends on the situation though which one to use.
But in general, just thought this was a good bit of knowledge if you’re only doing something simple and need a count from a related table and was curious to see if anyone else would have some tips or tricks to share.
