https://supabase.com/ logo
Powered by
Hello guys, I'm struggling with a join request wit...
# help
j
Hello guys, I'm struggling with a join request with filters. I have the following simplified database (see the picture). I have a playground table, a playground can have many contacts which can either be persons or organizations. A playground has one organization which created it. I want to be able to select playgrounds which: - have been created by my organization OR - have my organization in its contact list OR - have myself in its contact list But I can't find the request to select these playgrounds. I have tried many request among them the following one:
Copy code
ts
        return this.supabaseService.supabase
            .from('playground')
            .select(`
                *,
                playground_contact_person(
                    person
                ),
                playground_contact_organization(
                    organization
                )
            `)
            .or(`creative_organization.eq.${this.userService.userOrganizationId}`)
            .or(`person.eq.${this.userService.userProfile.id}`, { foreignTable: "playground_contact_person"})
            .or(`organization.eq.${this.userService.userOrganizationId}`, { foreignTable: "playground_contact_organization"})
But this doesn't give the expected result. I suppose that I'm doing a bad joining but also I think that I should only have one ``or`` statement? But if I should have only one ``or`` statement, how can I filter on multiple foreign table?
n
Hello @Julien! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User so that they can see your message and help you as soon as possible! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ``...`` menu) and select "Leave Thread" to unsubscribe from future updates. Want to change the title? Use the ``/title`` command! We have solved your problem? Click the button below to archive it.
g
You are not using .or correctly. It only does .or on multiple filters in the (). https://supabase.com/docs/reference/javascript/or Also I believe it is limited to all being in the same table (from table or a single foreign table).
An rpc function would normally be used for complex sql like this.
j
Oh ok, yes it seems to be limited to only one table
Okay, I will look into this, thanks!
Was hard since I'm new to postgresql but I think that I found my happiness with:
Copy code
postgresql
create or replace function get_playgrounds(orga_id int8, perso_id int8)
returns SETOF playground
language plpgsql
security INVOKER
as $$
BEGIN
  return query
    SELECT *
    FROM playground
    WHERE creative_organization_id=orga_id
    UNION
    SELECT playground.*
    FROM playground_contact_organization
    INNER JOIN playground 
    ON playground_contact_organization.playground_id=playground.id
    WHERE organization_id=orga_id
    UNION
    SELECT playground.*
    FROM playground_contact_person
    INNER JOIN playground 
    ON playground_contact_person.playground_id=playground.id
    WHERE person_id=perso_id;
END;
$$
😄
n
Thread was archived by @Julien. Anyone can send a message to unarchive it.
PreviousNext
Powered by