aliasing joined tables in .select('*')

[c00]

Improve documentation

There's a super nifty feature: renaming columns / tables in a select(), to influence the shape of the resulting object. However, it is not really documented anywhere.

Link

This is the only place I've found where renaming is shown, but it's labeled "Querying the same foreign table multiple times".

Describe the problem

A vital bit of information is missing. tl;dr: I can do client.from('books').select('*, author:authors(*)'). This is super helpful, but is not documented anywhere.

Example tables:

books:
  id: number
  title: text
  isbn: text
  author_id: number # FK > author.id

authors:
  id: number
  name: text

Now, I want to fetch book id 1, with the author.

const result = client.from('books').select('*, authors(*)').eq('id', 1).single();
const book = result.data;
console.log(`The book ${book.title} is written by ${book.authors.name}.'`);

The fact that the property book.authors is named as a plural, but the value is a singular author, is not great. This is a pretty common scenario when joining tables. Luckily we can change that like so:

const result = client.from('books').select('*, author:authors(*)').eq('id', 1).single();
                                               //^ Here we add an alias for the table
const book = result.data;
console.log(`The book ${book.title} is written by ${book.author.name}.'`);

This is much more reasonable. However, documentation of this is missing.

Describe the improvement

On this page, in the section of fetch examples, add 2 examples. One called "rename columns" and one called "rename joined tables". The first example would be to showcase renaming normal columns such as .select('name:title'). The second example would be specifically about the use-case described above.

Additional context

In my code, getting more than one table's data at the same time is very common. And renaming columns like this is an essential tool for making the resulting objects easy to use. I would love it to be a lot clearer to new people reading the docs, that this is possible. It would've saved me a lot of refactoring at least :)

[rookledookle]

I would add that we need documentation for stating the alias name when you also specify the foreign key - can't find docs on this

[sim1029]

@rookledookle Do you have an example of how to alias with a foreign key? Trying to do that right now and can't figure it out

[vincent-vaur]

@rookledookle Do you have an example of how to alias with a foreign key? Trying to do that right now and can't figure it out

You can do it for now like @c00 said:

const result = client.from('books').select('*, author:authors(*)').eq('id', 1);

The alias:join_table syntax works for me 👍