Is it possible to order by a junction table?

[richardkyk]

I have the following schema:

I am using the following query to get the results

 const { data, error } = await supabase
      .from("categories")
      .select(
        `
        category_id, category_name, 
            groups(
                *, members(*)
            )
        `
      )
      .order("group_order", {
        foreignTable: "category_groups",
        ascending: true,
      });

However, the order method doesn't appear to be doing anything in this example. Is there a way order by the junction table?

[soedirgo]

Edit: Final answer here: #2990 (reply in thread)

Can you give an example of the expected & actual value for data?

[richardkyk]

This is what I'd like to get (expected)

[
  {
    "category_id": "67ea17d5-267a-4d51-8064-bca70696de79",
    "category_name": "Category 1",
    "groups": []
  },
  {
    "category_id": "fd2f1a37-79a3-4bd0-9174-f636a4e7254a",
    "category_name": "Category 2",
    "groups": [
      {
        "group_id": "100927ab-23c0-4cf2-b1e5-0df138ec99e9",
        "group_name": "Group 1",
        "members": []
      },
      {
        "group_id": "61b522fc-72ba-4c29-bbac-69399de6b556",
        "group_name": "Group 2",
        "members": []
      }
      {
        "group_id": "4b208d18-2393-425c-be19-35f775cc436a",
        "group_name": "Group 3",
        "members": []
      },
    ]
  }
]

This is the current output that I get (actual)

[
  {
    "category_id": "67ea17d5-267a-4d51-8064-bca70696de79",
    "category_name": "Category 1",
    "groups": []
  },
  {
    "category_id": "fd2f1a37-79a3-4bd0-9174-f636a4e7254a",
    "category_name": "Category 2",
    "groups": [
      {
        "group_id": "4b208d18-2393-425c-be19-35f775cc436a",
        "group_name": "Group 3",
        "members": []
      },
      {
        "group_id": "100927ab-23c0-4cf2-b1e5-0df138ec99e9",
        "group_name": "Group 1",
        "members": []
      },
      {
        "group_id": "61b522fc-72ba-4c29-bbac-69399de6b556",
        "group_name": "Group 2",
        "members": []
      }
    ]
  }
]

I would like the order of the groups array in "Category 2" to be sorted by the group_order in the category_groups table.

category_groups

[soedirgo]

I don't think this is possible right now, and on that note, it's not possible to select junction table columns either, pending on this: https://github.com/PostgREST/postgrest/issues/1733. So right now the easiest way would probably be to include the junction table in the select, e.g. *, category_groups(groups(*)), and then modify the response client-side.

@steve-chavez could be a useful use case for the PostgREST issue above ☝️

[steve-chavez]

@soedirgo Thanks for the heads up.

Actually this one seems related to supabase/postgrest-js#198 (and hence to PostgREST/postgrest#1414), where the client wants to order the source table of a many-to-one relationship based on the target table, e.g. ordering categories by category_groups. Then there's the issue of whether an embedded resource needs to appear on the select part(which is not enforced currently, so it could be optional).

[binajmen]

Hello @richardkyk @soedirgo @steve-chavez

I was facing the same issue and made a small example with the well known authors and books relationship 😄

authors

id	name
1	Alice
2	Bob

books

id	name
1	Book 2
2	Book 1
3	Book 3

author_books

author_id	book_id	order
1	1	2
1	2	1
2	3	1

I understood from the different discussions on the subject that it is (currently?) not possible to order the result on a column of a bridge table (in a m-to-n relationship).

What I ended up with is a view books_ordered defined as follow:

create or replace view books_ordered as
select
  b.*,
  ab.order
from
  books as b
inner join author_books ab on b.id = ab.book_id
order by
  ab.author_id asc,
  ab.order asc

When executing:

  await supabase
    .from("authors")
    .select(`
      name,
      books:books_ordered!author_books (
        title, order
      )
    `);

The result is the following:

[
  {
    "name": "Alice",
    "books": [
      {
        "title": "Book #1",
        "order": 1
      },
      {
        "title": "Book #2",
        "order": 2
      }
    ]
  },
  {
    "name": "Bob",
    "books": [
      {
        "title": "Book #3",
        "order": 1
      }
    ]
  }
]

The books are in the right order 🥳

I would love to hear from you to

• challenge/destroy this workaround in the case I've forgot something
• validate this workaround otherwise

With a bit of advance, happy new year!

UPDATE

Although it works so I'm happy, I'm wondering how books:books_ordered!author_books works: how books_ordered.id is correctly associated to author_books.book_id? Is it due to the fact id come from books when defining the view and is somehow known as being a book's_id?

[otang]

+1 I would like to see this feature

[moradlabrid]

It works well

export function useIdeaWithRelations(id: string) {
return useQuery({
queryKey: ["idea-with-relations", id],
queryFn: async () => {
const { data } = await supabase
.from("ideas")
.select(",scripts(),thumbnails(),analyses()")
.eq("id", id)
.order("created_at", { ascending: true, foreignTable: "thumbnails" })
.single();

  return data;
},
enabled: !!id,

});
}