Selecting against foreign table using OR condition when joining across tables

[OpenFieldsOfOz]

The app I'm building tracks books, and lets users filter them by tags. My schema is as depicted below

To filter by a tag, I need to join tags and book_tags to books, then select on tags. An example very simple query would be

SELECT * FROM books
INNER JOIN book_tags ON book_tags.book_id = books.id
INNER JOIN tags ON tags.id = book_tags.tag_id
WHERE (tags.slug = 'setting:contemporary' OR tags.slug = 'setting:fantasy') AND (tags.slug = 'rating:four-stars' OR tags.slug = 'rating:five-stars')

I'm having trouble using the JS integration to write a query like this. When I write

  useEffect(() => {
    const fetchBooks = async () => {
      try {
        let query = supabase
          .from("books")
          .select("*, book_tags!inner(book_id, tag_id, tags!inner(slug))")
          .order("rank", { ascending: false });
        for (const positive_tags of preferences.positive_tags) {
          if (positive_tags.length > 0) {
            query = query.or(positive_tags.map(tag => "public.tags.slug.eq." + tag).join(", "));
          }
        }
        for (const negative_tags of preferences.negative_tags) {
          for (const negative_tag in negative_tags) {
            query = query.neq("public.tags.slug", negative_tag);
          }
        }

        const { data, error } = await query;
        if (error) {
          throw error;
        }
        setBooks(data);
      } catch (error) {
        console.error("Error fetching books:", error);
      } finally {
        setLoading(false);
      }
    };

    if (preferences) {
      fetchBooks();
    } else {
      setLoading(false);
    }
  }, [preferences]);

I get the error

{
    "code": "PGRST100",
    "details": "unexpected \"t\" expecting \"not\" or operator (eq, gt, ...)",
    "hint": null,
    "message": "\"failed to parse logic tree ((public.tags.slug.eq.setting:fantasy))\" (line 1, column 11)"
}

I think this error is ocurring because I am trying to qualify the table name, because not doing so results in this error

{
    "code": "42703",
    "details": null,
    "hint": null,
    "message": "column books.slug does not exist"
}

[GaryAustin1]

Not sorting thru all the code, but .or() only works on the same table for all filters.

At some point you are better off just using an rpc call to postgres function or a view.

If I'm missing your context sorry, mainly going on your title "when joining across tables"

[OpenFieldsOfOz]

I was afraid of that, but it does seem like signs are pointing to an RPC call.

[otang]

@GaryAustin1 is there anyway for supabase to allow this functionality? or conditions across tables, using dot notation, would unlock a lot of very powerful functionality.

[GaryAustin1]

Supabase uses PostgREST for this. There is a limited ability to do this as shown here: https://postgrest.org/en/latest/references/api/resource_embedding.html#or-filtering-across-embedded-resources . Other than that you would need to use rpc calls.