How could I write the following query in JS?

I'm having difficulties translating this query:

SELECT location.name
FROM location
JOIN facility
    ON location.id = facility.location_id
JOIN facility_category
    ON facility_category.id = facility.facility_category_id
    WHERE facility_category.name = 'Imaging Center'

SELECT location.name
FROM location
JOIN facility
    ON location.id = facility.location_id
JOIN facility_category
    ON facility_category.id = facility.facility_category_id
    WHERE facility_category.name = 'Imaging Center'

SELECT location.name
FROM location
JOIN facility
    ON location.id = facility.location_id
JOIN facility_category
    ON facility_category.id = facility.facility_category_id
    WHERE facility_category.name = 'Imaging Center'

SELECT location.name
FROM location
JOIN facility
    ON location.id = facility.location_id
JOIN facility_category
    ON facility_category.id = facility.facility_category_id
    WHERE facility_category.name = 'Imaging Center'

So far I have something likke this:

.from('location')
      .select('name, facility!inner(*), facility_category(*)')
      .eq('facility_category.name', 'Imaging Center')

.from('location')
      .select('name, facility!inner(*), facility_category(*)')
      .eq('facility_category.name', 'Imaging Center')

.from('location')
      .select('name, facility!inner(*), facility_category(*)')
      .eq('facility_category.name', 'Imaging Center')

.from('location')
      .select('name, facility!inner(*), facility_category(*)')
      .eq('facility_category.name', 'Imaging Center')

But I just get the follwing error: "Could not find a relationship between 'location' and 'facility_category' in the schema cache", even though the query works fine in the SQL editor.

Also, I'd like to make it distinct, but my understanding is that it is not possible via the API?

garyaustin•8/29/22, 6:37 PM

At a minimum, I think you the !inner on facility_category to use it in a filter.

Julien ConnaultOP•8/29/22, 7:06 PM

Well, the problem seem to be that in my location table, I have no foreign key leading to facility_category. That fk is in facility. But I don't know how to solve that.

I tried the modification you suggested, but I get the exact same error message

garyaustin•8/29/22, 7:27 PM

I'm not the greatest at this without trial and error, but possibly ('name, facility(*, facility_category!inner(*))') ('name, facility(*, facility_category!inner(*))')
Although this is not in the javascript format, it is the ultimate source of what can be done:https://postgrest.org/en/stable/api.html#resource-embedding

Julien ConnaultOP•8/29/22, 8:08 PM

Thanks a lot for trying to help me.
Your suggestion yields the same result as before.

The thing is, the query works well in the supabase SQL editor, and returns what I want. So the problem is with the API js syntax.
I'm gonna take a look at the RPC, but I have never used them and I don't know how to pass a parameter to it.

garyaustin•8/29/22, 8:56 PM

What you want should be doable in the API, I'm not somewhere though were I can play with it. Github might have a solution also, but hard to figure out search terms.

There is a slim chance if you changed something in the foreign keys it is a real cache error, but that is rare. https://postgrest.org/en/stable/schema_cache.html#reloading-with-notify

ortegaxc•9/13/22, 12:56 AM

@Julien Connault Hello, I hope you are well, I have the same problem, did you manage to solve it?

garyaustin•9/13/22, 1:15 AM

@ortegaxc unless you have the exact same table structure you should probably ask a new question. If you are just getting the error message, that is common for lots of different cases.

tonyhart•9/13/22, 1:49 AM

You can define table view for that particular query and then you can query like table in front end

Oortegaxc @Julien Connault Hello, I hope you are well, I have the same problem, did you ma...

Julien ConnaultOP•9/13/22, 11:41 AM

Not sure if it's gonna help you, but after a long time searching, here is the code I came up with:

.from(facility)
        .select(`*, facility_category!inner(*), location!inner(*)`)
        .eq(`facility_category.name`, 'Imaging Center')

.from(facility)
        .select(`*, facility_category!inner(*), location!inner(*)`)
        .eq(`facility_category.name`, 'Imaging Center')

How could I write the following query in JS?

Similar Threads

Similar Threads

Similar Threads