https://supabase.com/ logo
Join Discord
Powered by
Never thought (nested counting) this is possible u...
# sql
k
Never thought (nested counting) this is possible until I tried it šŸ™ˆ
Copy code
.select(
          "*, following_user:follower(id, username, profile_picture, follower_count:friends.following(count)), "
          "follower_user:following(id, username, profile_picture, follower_count:friends.following(count))",
        )
#motherofnesting
b
Whoh, this is cool. Can you post your entire 
supabase.select
call here so I can see the context?
What does 
friends.following(count)
refer to? Is 
count
a field or the SQL keyword 
count
, as in 'select count(*) from table'?
k
get my followers
Copy code
final response = await _supabase.client
        .from(FRIENDS_DB)
        .select(
          "*, following_user:follower(id, username, profile_picture, follower_count:friends.following(count)), "
          "follower_user:following(id, username, profile_picture, follower_count:friends.following(count))",
        )
        .eq('following', uid)
        .paged(page)
        .execute();
get my followings
Copy code
final response = await _supabase.client
        .from(FRIENDS_DB)
        .select(
          "*, follower_user:follower(id, username, profile_picture, follower_count:friends.following(count)), "
          "following_user:following(id, username, profile_picture, follower_count:friends.following(count))",
        )
        .eq('follower', uid)
        .paged(page)
        .execute();
it refer to 
count
as in 
select count(*)
another usecase get user by Id
Copy code
final response = await _supabase.client
        .from(USERS_DB)
        .select('*, ' +
            'following_count:friends.follower(count), follower_count:friends.following(count),' +
            'challenges_count:challenges.receiver_id(count)')
        .eq("id", uid)
        .eq("challenges_count.challenge_state", ChallengeModel.APPROVED)
        .execute();
as you can see, 
challenges_count
even has a condition
b
you're using some enumerations for the field names here, as in 
ChallengModel.APPROVED
?
k
nope, its just a string. Unfortunately dart doesn't have 
.name
so I went ahead with final strings
b
and I assume you have foreign keys set up between these tables, and that's how it's able to get the counts?
Yeah, I don't know dart, I'm just an old JS guy šŸ™‚
don't engage me or I'll bore you with stories about how, in the old days, we weren't sure whether JavaScript or VBScript would win the war.... it was a terrifying time.
k
yeeepie. users table
Copy code
create table users
(
    id uuid not null
        constraint users_pkey
            primary key
        constraint users_id_fkey
            references auth.users
                on delete cascade,
    username varchar(200) not null,
    locale varchar(30) not null,
    profile_picture text,
    created_at timestamp with time zone default timezone('utc'::text, now()) not null
);
friends table
Copy code
create table friends
(
    follower uuid not null
        constraint friends_follower_fkey
            references users
                on delete cascade,
    following uuid not null
        constraint friends_following_fkey
            references users
                on delete cascade,
    created_at timestamp with time zone default timezone('utc'::text, now()) not null,
    constraint friends_pkey
        primary key (follower, following)
);
b
This is very cool stuff. I wasn't aware they opened up 
count
in Postgrest. I'll need to go back and see what other cool things it can do.
Let me show you a cool page for that, hold on...
k
actually, u wont find any docs about it, few weeks ago, i did it entirely by luck and worked šŸ˜„
b
k
yep, my fav website of all times. jk šŸ˜„ https://postgrest.org/en/v7.0.0/api.html
b
You can play around with the postgrest operators, I think most of them should work in the api. Well, at least the JS api,not sure what Dart has implemented
k
the sdk provide most of them within the builder.
b
Generally I look at anything semi-complicated in the client API and jump right to a PostgreSQL function instead.
But if I can get more of these complex examples working I'll see about adding this stuff to the Supabase client docs.
k
yeah, I do the same, most of my queries are in functions, but for such 
count/selection
its fine to do it on client side.
b
I have to run out now, but thanks for pointing this out. It's gonna open some doors for me. šŸ™‚
k
let me know if you need any hand, I could provide some examples or use cases
b
šŸšŖšŸ˜®
I will reach out!
k
šŸƒšŸ»ā€ā™‚ļø
PreviousNext
Powered by