Topics

Supabase•3y ago

Can't filter by uuid...

Hey all! Trying to GET a table using .eq() filter. The column I want to get is the type of uuid and it's a foreign key for another table. Every other filter does work, but it's crucial for me to get it done by filtering by this uuid. I've tried everything: even putting a string manually into .eq() didn't work. In the console I'm getting err 400. RLS are disabled in both tables. Any ideas?

48 Replies

garyaustin•3y ago

You are going to need to provide some code samples, url from developer console if you can and your table and key column name/type. You have not given enough info to help you.

mavOP•3y ago

Sure! It looks like I am not able to attach screenshots here. This code doesn't work:

 const {data, err} = await supabase
            .from('donations')
            .select()
            .eq('to', id)

 const {data, err} = await supabase
            .from('donations')
            .select()
            .eq('to', id)

This code does work:

 const {data, err} = await supabase
            .from('donations')
            .select()
            .eq('amount', amount)

 const {data, err} = await supabase
            .from('donations')
            .select()
            .eq('amount', amount)

My table structure is the following: id -> uuid created_at -> timestamp from -> text to -> uuid (foreign key for another table with users) amount -> float8 transaction_hash -> text message -> text Basically I can filter by every parameter but the column 'to'. Url from the dev console is https://ykrdpfyfwvzazyztjbzt.supabase.co/rest/v1/donations?select=*&is=eq.38c71ccf-4d75-4a53-af24-65dae701f174 for example.

juanvilladev•3y ago

const getDonationsByUserId = (userID) => {
  const { data, err } = await supabase.from('donations').select('*').eq('id', userId)
}

const getDonationsByUserId = (userID) => {
  const { data, err } = await supabase.from('donations').select('*').eq('id', userId)
}

mavOP•3y ago

I'm using useEffect in react, the whole snippet works well in other places:

    useEffect( () => {
        const listDonations = async () => {
            const {data, err} = await supabase
            .from('donations')
            .select()
            .eq('to', id)

            if(err) { 
                console.log(err) 
            }

            if(data) { 
                setDonation(data);
            }
        }
        listDonations();
    },[id]);

    useEffect( () => {
        const listDonations = async () => {
            const {data, err} = await supabase
            .from('donations')
            .select()
            .eq('to', id)

            if(err) { 
                console.log(err) 
            }

            if(data) { 
                setDonation(data);
            }
        }
        listDonations();
    },[id]);

juanvilladev•3y ago

The word 'to' doesn't mean anything The way .eq works is it compares the first parm to the second and returns true if they're equal. So if you have the ability to pass your userID via a parm you can check it like .eq(userIdFromParm, 'id')

mavOP•3y ago

I'm trying to filter by the 'to' column?

juanvilladev•3y ago

Oh I see Where is the variable id coming from in your code, the second parm inside .eq?

mavOP•3y ago

It's a prop, shouldn't have issues as it's being printed in the console before I run the snippet...

juanvilladev•3y ago

Also if your to column is storing the UUIDs make sure that you didn't select text as the type inside postgres, it should be the same type 'UUID' Gotcha

garyaustin•3y ago

So your url makes no sense. select=*&is=eq.38c71ccf-4d75-4a53-af24-65dae701f174 &is= is saying your column is named is....

mavOP•3y ago

This doesn't work either https://ykrdpfyfwvzazyztjbzt.supabase.co/rest/v1/donations?select=*&to=eq.38c71ccf-4d75-4a53-af24-65dae701f174

garyaustin•3y ago

messages?select=*&id=eq.75 Is from a working select with.eq in my test environment. Is the is something we need to understand, or did you rename/user another column?

mavOP•3y ago

No, I think I was just trying different options, we can disregard that - there is no is column

garyaustin•3y ago

Is there any more error info in the response? Normally if nothing is found you get an empty array back. Not a 400 error.

mavOP•3y ago

It looks like I'm only getting any data if I'm filtering by any other column - getting error when trying to filter by id. It was all fine in the previous build when the id column was just an id, not uuid.

garyaustin•3y ago

What does "previous build mean?" Did you change the column type after it was set up as int type?

mavOP•3y ago

No, I had different databases with id's with the type of 'id' which I then deleted and created the same databases with the id's with the types of 'uuid'. I'm able to get list of users by uuid for example, but not donations.

garyaustin•3y ago

I would expect something an error message back with something about column not matching. If you enter a number instead of uuid does it come back without error?

juanvilladev•3y ago

Can you do a simple select(*) and does it return UUIDs? Without the filter

mavOP•3y ago

For example, this code works:

        const findUser = async () => {
            const {data, err} = await supabase
            .from('users')
            .select()
            .eq('id', '38c71ccf-4d75-4a53-af24-65dae701f174')
            .single()

        const findUser = async () => {
            const {data, err} = await supabase
            .from('users')
            .select()
            .eq('id', '38c71ccf-4d75-4a53-af24-65dae701f174')
            .single()

garyaustin•3y ago

Something is wrong with your column type, or the cache. For sure eq works with UUID.

juanvilladev•3y ago

yeah I've used it before too Gary

mavOP•3y ago

Yes it does

juanvilladev•3y ago

Example, the profiles table created from user management started, I constantly query it with UUID

mavOP•3y ago

It works for me on one page too, but not on the other one... How do I deal with cache?

garyaustin•3y ago

Can you run this in the sql editor: NOTIFY pgrst, 'reload schema'; And you have not said if you get an error message. What does this mean? And what cache are you referring to?

mavOP•3y ago

You wrote that something might be wrong with my cache Success. No rows returned All I'm getting in the console is this: GET https://ykrdpfyfwvzazyztjbzt.supabase.co/rest/v1/donations?select=*&to=eq.38c71ccf-4d75-4a53-af24-65dae701f174 400

garyaustin•3y ago

What are data and error? On the cache, that was the SQL command I sent to clear the API's schema cache. Sometimes if you have a table, then change a column type or foreign keys you have to reload it.

mavOP•3y ago

Getting this https://codeshare.io/N3Mpjm

garyaustin•3y ago

What is that?

mavOP•3y ago

Error in my console

garyaustin•3y ago

The error from supabase select ?

mavOP•3y ago

Yes. When I'm sending GET request, this is what I'm getting in the console.

garyaustin•3y ago

{data, error} = await supabase.... That error is what I'm asking for, and you have it called err which won't work. and is data null or []?

mavOP•3y ago

I'm not sure that this is the {error}, just a default fetch error. Yesh, I'm struggling to understand why.

garyaustin•3y ago

You need to change {data, err} to {data,error:err}

mavOP•3y ago

Oh

code
: 
"22P02"
details
: 
null
hint
: 
null
message
: 
"invalid input syntax for type bigint: \"38c71ccf-4d75-4a53-af24-65dae701f174\""

code
: 
"22P02"
details
: 
null
hint
: 
null
message
: 
"invalid input syntax for type bigint: \"38c71ccf-4d75-4a53-af24-65dae701f174\""

garyaustin•3y ago

Did you run the SQL command I showed? That error is the key.

mavOP•3y ago

Yes, I have. I didn't see it earlier.

garyaustin•3y ago

It says your column is or the API thinks it is an int8 column Can you show a pic of your table column in the table UI?

mavOP•3y ago

This is my user database: https://snipboard.io/Yyldoh.jpg This is my donations database: https://snipboard.io/ZEkPqh.jpg

garyaustin•3y ago

Can you show the actual to column in editor? Also, I hate to ask this.... Any chance your code is going to a different instance (like your old one) instead of the one you are showing?

mavOP•3y ago

Users: https://snipboard.io/hQB2e4.jpg Donations: https://snipboard.io/Jja8qy.jpg Everything is possible... No, I tried everything to check, it does talk to a new instance of my database. It shows data from a new database when I filter by any other column

garyaustin•3y ago

I'm out of ideas. For some reason the API thinks you have int8 column, but the UI is showing a uuid column.

mavOP•3y ago

Ok, gotcha. At least now I know what the error is, didn't know I had a bug in my code! Thanks a lot for that, I won't be taking your time!

garyaustin•3y ago

You could just delete the column, create a new one. First without the fk link, then with.

mavOP•3y ago

LOL. Simply renaming the database worked. I'm speechless. Thanks a lot for your help! 🙏

garyaustin•3y ago

Some sort of caching thing likely.

Supabase gives you the tools, documentation, and community that makes managing databases, authentication, and backend infrastructure a lot less overwhelming.

41KMembers

View on Discord

Did you find this page helpful?