How would you query not exist from a different table

[k0shk0sh]

Hi there, I just recently, decided its time to ditch Firebase and use supabase for alot of reasons and I already started the migration, however I suck upon an issue with querying the database where i would like to list users if I didn't block them, that's simple if I use the built in query

select * from users as t1 where LOWER(t1.name) like LOWER('%kosh%') 
and not exists (select * from blocks as t2 where t2.blocked_id = t1.id) 

Now I'm kinda stuck cuz of lack of documentation to how can I do achieve such query using the dart client or even the js client.

I tried the below:

const {data, error} = await supabase.from('users').select('*, blocks (id)')
    .neq('blocks.blocked_id', 'users.id')

but I got

{
  hint: null,
  details: null,
  code: '22P02',
  message: 'invalid input syntax for type uuid: "users.id"'
}

Also tried:

const {data, error} = await supabase.from('users').select(`*, blocks(*)&blocks.blocked_id!=users.id`)

but that returns users and their blocks

Any help will be appreciated.

[steve-chavez]

Hey @k0shk0sh,

You can create function of your query and call it through supabase rpc.

create or replace function unblocked_users(name text) returns users as $$
  select * from users as t1 where LOWER(t1.name) like LOWER('%' || name || '%') 
  and not exists (select * from blocks as t2 where t2.blocked_id = t1.id) 
$$ language sql;

const { data, error } = await supabase
  .rpc('unblocked_users')
// with rpc, you can also use select() or other filters like eq()

[k0shk0sh]

Hi Steve, just cameback to actually post that I figured it out using:

create or replace function search_user (
  query varchar, uid uuid
) 
	returns table (
    searched_name varchar, searched_id uuid, searched_profile_picture text
	) 
	language plpgsql
as $$
begin
	return query 
		select
			username, id, profile_picture
		from
			users as u
		where
			username ilike query
			and not exists (select * from blocks as b where b.creator_id = uid and b.blocked_id = u.id);
end;$$

but urs seems to be way simpler, I will try it out. thanks :))

[k0shk0sh]

@steve-chavez the code you posted returns errors

create or replace function search_users(query text, uid uuid) return users as $$
  select * from users as u where username ilike query
  and not exists (select * from blocks as b where b.blocked_id = u.id) 
$$ language sql;

syntax error at or near "return"

do you know why is that?

[k0shk0sh]

owh, figured it out, it supposed to be returns and not return