Best practice for table component with JOIN

App Building
1

Still trying to understand how Retool works. I have two tables: countries and clients. Client table has a country_id that is linked to the countries id table in PostGre.

When I add a Retool table component so that I can manage clients, I want to show in the table the name (not the id) of the country the client is linked to. However, if I change the getData query with a LEFT JOIN to the countries table, I can't update the table anymore with the updateRow query (that is generated automatically by Retool using the create app from database).

I understand this is a very basic question, but can't find the answer anywhere.

What is the best pattern to include linked tables on a Retool table component?

2

What does the query look like?
Have you tried INNER JOIN? or simply JOIN?

3

I've tried both INNER JOIN and LEFT JOIN, and that doesn't seem to affect the fact that updateRow query generate "0 updated records" after updating the form (both the updateRow and the form were auto-generated by Retool)

4

The specific getData query is select * from "companies" left join geographies on companies.geography_id = geographies.id where ({{ !searchBar.value }} or cast("name" as text) ilike {{ '%' + searchBar.value + '%' }});

5

Try
select * from companies c left join geographies g on g.id = c.geography_id where ({{ !searchBar.value }} or cast(name as text) ILIKE {{ '%' + searchBar.value + '%' }});

6

It doesn't make a difference - see the screen recording https://www.youtube.com/watch?v=zxl8g9GuaXE (thank you so much btw, for helping me sort this out!)

7

I think I fixed it.

I am not SQL-joining tables as the source of the Retool table component, but adding custom fields, using the following template:

{{ getPortfolios.data.portfolio_name[currentRow.portfolio_id] }}

8

Now it's done.

This is the final code for a custom table field.

{{ getInvestments.data.investment_name[(1+getInvestments.data.id.indexOf(currentRow.portfolio_id))] }}

What a nightmare. There should be easier to do such a simple task

9

Glad you got it resolved...but you COULD use SQL and if you post agaian, providing screenshots usually helps get to a solution faster

2 Likes