Hey guys, there is any way to query the relation table value using ilike? Thanks

const { data: user_inventory, error } = await Supabase.from("user_inventory")
    .select(
      `id,
      owner_id,
      p:product_id (
        id,
        name,
        type,
        alternative_name,
        code,
        hex_color,
        barcode,
        brand (name, logo)
      ),
      quantity`
    )
    .ilike("p.name", `%${term}%`)
    .ilike("p.alternative_name", `%${term}%`)
    .ilike("p.code", `%${term}%`);

My native sign in with Google/Apple has been broken ever since this PR was merged. https://github.com/supabase/gotrue/pull/927 I also noticed that someone else has been having the same issues as I, as shown from a comment in this PR. https://github.com/supabase/gotrue-js/pull/603 I'm concerned that since it's a Friday that this bug will go unfixed until Monday. What's the play here?

hi all i created a

posts

table, and built an index to maintain an invariant such that users can only post once per day (code attached below) when they haven't posted that day i can just use an insert query no problem when they do i can't figure out how to use the supabase API to manage this (perhaps using the

onConflict

on the

upsert

api?) - get an error:

"there is no unique or exclusion constraint matching the ON CONFLICT specification'

when i provide the following comma seperated cols:

user_id, inserted_at

all i want to do is update the same row for a given day with the post text. table query

create table posts (
  id bigint generated by default as identity primary key,
  user_id uuid references auth.users not null,
  post text check (char_length(post) > 0),
  inserted_at timestamp with time zone default timezone('utc'::text, now()) not null
);
alter table posts enable row level security;

create policy "Individuals can create posts." on posts for
    insert with check (auth.uid() = user_id);
create policy "Individuals can view their own posts. " on posts for
    select using (auth.uid() = user_id);
create policy "Individuals can update their own posts." on posts for
    update using (auth.uid() = user_id);
create policy "Individuals can delete their own posts." on posts for
    delete using (auth.uid() = user_id);

index query:

create unique index unique_posts_per_day
on posts (user_id, date_trunc('day', inserted_at at time zone 'utc'));

I am making a admin dashboard and i need to list the emails of all users, but i am having a error. Dart/Flutter code: List list_subsecoes = await Supabase.instance.client.rpc('list_users_just_email'); Function code: create or replace function list_users_just_email() returns table (email varchar) as $$ select auth.users.email as email from auth.users $$ language sql; Error: [ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled Exception: PostgrestException(message: permission denied for table users, code: 42501, details: Forbidden, hint: null)

Hey I'm having an issue using the NEW variable inside my function below:

sql
DECLARE
  match_count float;
  count_losses float;
  total_bets float;
  percent_match NUMERIC;
BEGIN

  -- count the number of rows that match the specified value in the specificed column
  SELECT COUNT(*) INTO match_count FROM plays WHERE status = 'WIN' AND profile = NEW.profile;
  SELECT COUNT(*) INTO count_losses FROM plays WHERE status = 'LOSS' and profile = NEW.profile;

  if (match_count + count_losses) > 0 then
    -- calculate percentage.
    total_bets = (match_count + count_losses)::float;
    percent_match = (match_count / total_bets)::float;
   update profiles set win_record=percent_match where id= NEW.profile;
  end if;

  return new;
end

When we use a static string value in place of

NEW.profile

that is the ID of a profile we know exists in the table, this function works perfectly.

profile

is a foreign relation UUID column. Can anyone point me in the right direction here?

I'm trying to figure out how to implement this using the JavaScript API: These are my updates:

const updates = {
        team_id: user.id,
        edition: edition_id,
        round: round_id,
        question: question_id,
        answer: formdata.answer,
        music_answer: formdata.music_answer,
        created_at: new Date().toISOString(),
      };

team_id

is a uuid and the same as the User uuid from auth. If the

team_id

,

edition_id

,

round_id

, and

question_id

are all the same as in an existing row, that row should be updated with the other data (

answer,   music_answer, created_at

) If there's no that has the same values for those, then a new row should be created. Is this possible?

Hi everyone, I'm building something with supabase Auth Providers. I got this error when signing with my GitHub account. Does this mean I've already have an account with the same email? So accounts can't be connected? BTW, Can I have ONLY one account with both GitHub and gitlab auth?

This isn't just a supabase question, but I am posting as a hail mary cause I've been stuck for two days on this. I have a query right now which looks like this:

with clusters as (
    WITH pts AS (
        select
            ST_Point(p.longitude, p.latitude) as geom,
            p.selfie_path as selfie_path,
            p.created_at
        from
            public.profiles as p
        where
            ST_Within(
                ST_Point(p.longitude, p.latitude),
                ST_MakeBox2D(
                    ST_Point(-123.16979495879038, 36.9067914918534),
                    ST_Point(-121.52930534951477, 38.988128574691444)
                )
            )
    )
    SELECT
        ST_ClusterKMeans(geom, 3) OVER() AS clst_id,
        ST_AsText(geom) AS geom,
        selfie_path
    FROM
        pts
)
select
    ST_Y(st_centroid(ST_Collect(geom))) as latitude,
    ST_X(st_centroid(ST_Collect(geom))) as longitude,
    json_agg(selfie_path) as selfie_paths
from
    clusters
group by
    clst_id

I've attached what the return looks like. What I am really struggling to do now is just limit the number of items in "selfie_paths". This right now would return every single selfie_path from the database, but I want it to return no more than 5. I am really struggling to figure out how to do this. Do I need another subquery that replicates the same PostGIS query with a limit set on it? Is there Any way to limit the size of "selfie_paths" in the query as I've made it? Any help would be so appreciated...

hello, is there any deep course to learn and master supabase as I'm a beginner to use it ? thanks in advance

Hey, suppose I have a table

user

and a table

notes

.

notes

has a column

user_id

to enable a foreign key relation to

user

. I've set the foreignkey constraint to

on delete cascade

. So whenever a user gets deleted it should also delete all

notes

associated with this user. So far so good. Now, I want to enable RLS for both table that only authenticated users can delete entries with their

user_id

. So making an API call to delete an entry from

notes

and passing the

user_id

works. However, when I want to make use of on delete cascade and only send a delete request to the

user

entry, on delete cascade fails because there's no user_id forwarded.

I can't find supabase built-in avatars on dashboard, or any doc to it. Is it deprecated?

Hi, I use supabase for authentication in my angular app with google provider and I want to get tokens providers but there is only supabase token in session after login. I used basic auth way :

this.supabaseService.auth.signInWithOAuth({
      provider: 'google',
      options: {
        redirectTo: this.getURL()
      }
    });

anyone know what is the problem ?

I want to detect when a user click on the magic link in his email and the url doesn't function, in the link there's after the route:

#error=unauthorized_client&error_code=401&error_description=Email+link+is+invalid+or+has+expired

I want to detect if an error occurred in the magic link, so If the url loads with

#error=unauthorized_client&error_code=401&error_description=Email+link+is+invalid+or+has+expired

do something. There's some native module of supabase to do that? Or how can I do this in Next.js?

Hi. I have a table of logs with date for each of them. I want to query the earliest log for each date, how may I do that with supabase client JS SDK? Im using nuxt btw, any help would be greatly appreciated! Also I'm also considering creating a view for this. Would love an advice which is better method

Is it a normal process to always get a new Signed URL everytime for each time a user visits for storage? Is that normal practise and good? (Im a bit of a newbie with this stuff). Is there a limit to signed urls etc?

A bit of a dumb question, but how do I view all my triggers/functions in my local env with a SQL query? I know how to view them in prod with the UI, but I'm having trouble figuring out which table the triggers/functions are stored.

Anyone seen the following error before?

Hey, i'm wondering if there's a way to access provider data (such as Discord data) from a trigger function that is ran on every user first login?

hey, im wondering how to reference a specific schema in my client side queries. When I use this as a test (uuid is the schema in question): await supabase.from('org_c0987bba_c107_4b01_8306_df33b74467c8.profiles').select('*'). I get a public.org_c0987bba_c107_4b01_8306_df33b74467c8.profiles\" does not exist". I obviously don't want it to default to public... is there anyway to specify that I don't want to use the public schema in my request?

I have a json object that sometimes contains strings with escaped unicode characters e.g.

"name": "Pobret\u00e3o"

that I want to store to a db table. Should I store it like this or as

Pobretão

? I'm going to retrieve it later and show on a website.

Can I safely use plv8 queries in my functions with text values being parsed or do I have to be concerned about SQL Injection?

Need help with serialization when using the community supabase-kt function to execute database functions. I'm completely unfamiliar with this topic and am getting the error :Exception in thread "AWT-EventQueue-0" kotlinx.serialization.SerializationException: Serializer for class 'Any' is not found. when I try to execute a database function and pass parameters in

Im trying to edit my table name but i received "An error has occurred: Failed to fetch supabase" I also follow one thread here that i need to restart my project and relogin anyone can help me , thanks in advance

Hi all, I hope you are great. I nearly have 13 columns, and their RLS policies are fine. They are working. But in my 14. column, it says "message: "new row violates row-level security policy for table \"details\""" - can anybody help? Thanks

Trying to set up unit testing with my local supabase via the CLI. I'm hitting a dead end and getting a "Cannot detect source of '/tmp/supabasetests'!" error. I've posted the details here but I would like to run it by this community too in case someone has a clue. https://github.com/supabase/cli/issues/843

As the title says 🙂

Does anyone experience that too? I know the credentials are correct, but trying to login getting error invalid credentials. Reset password does not send reset mail, and sign up again showing error that my account already exists. Does anyone else having issues logging in today?

Is this possible now or in the works? Would be a great feature!

From the Javascript 2.0 docs on `upsert`: "if a row with the corresponding onConflict columns doesn't exist, or if it does exist, perform an alternative action depending on ignoreDuplicates." What is ignoreduplicates, and how do I use it?

I have followed through the official documentation on self-hosting and changed .env to include a password for Postgres but I keep getting this error