I am using the Retool Database features for now with SQL queries. The data is being displayed in a horizontal table. There are so many columns that the user will need to scroll horizontally to read the last ones. In addition, some of the fields contain several sentences of text and the user will have to increase the column width for those fields if they need to see the entire content.
I would like the user to be able to see all the pairs without needing to scroll or widen column lengths.
Is there a way to display the items in a vertical table? I would then display the results in two tables, the first showing key/values 1-10 and hiding 11-20 and the second showing key/values 11-20 and hiding 1-10.
note: Values are displayed in various formats: text, numbers and dates etc.
If you are displaying multiple columns, you need the Table. Key Value can only show two columns, the Key and the Value.
The Table also has a Cell Tooltip Add On that will show the entire text on hover so you don't have to make your column super wide.
I am not clear by what you mean by vertical table. A few ways this has been interpreted in the past. Maybe you can sketch out what you have in mind for your UI and share it here?
You have full control over your datatypes and how they are displayed in a Table. You can do that with a little more work in a Key Value - you just have to transform your data first.
Thanks @bradlymathews . I want to show the columns vertically rather than horizontally to take advantage of screen real estate as you can see in the bottom table versus the top table:
I did some research and it looks like a SQL Cross Join lateral query can do this with some of the data:
select users_sample_data.id, t.*
CROSS JOIN LATERAL (
) as t(column_name, column_value)
ORDER BY id, column_name DESC;
But then when i try to add integers or booleans such as the Enabled column, I get a SQL error: VALUES types text and boolean cannot be matched
Chat GPT suggests this:
"In Postgres how do I create a query that returns the results of a single row as a key/value pair where the key of the field name."
In PostgreSQL, you can achieve this using the
jsonb_object_agg function along with the
jsonb_build_object function to convert a single row's columns into key/value pairs.
Let's say you have a table called
my_table and it has columns
col3. Here's a way to turn a single row's columns into key/value pairs:
WITH CTE AS (
jsonb_build_object('col1', col1) AS col1,
jsonb_build_object('col2', col2) AS col2,
jsonb_build_object('col3', col3) AS col3
LIMIT 1 -- Assuming you want just one row.
This query will return a single JSONB object with each column from the row as a key and the corresponding value as the value.
Do note that if
my_table contains more rows, the
LIMIT 1 will only take one of them, but which one is not predictable unless you add an
ORDER BY clause. Adjust the query according to your requirements.
Me again. I assume you will need to have a where clause of some sort to only return one specific row rather than a dumb
Hey@bradleymathews thanks for this. I used another chat GPT suggestion to get an array of objects.
Then I created an additional SQL query and strung together where clauses to choose which array objects I wanted to show as rows-- I only wanted to show some of them. When I strung together a small number of "where "queries", I got back an array of objects but the order of the array does not transfer to the order of rows. In other words the fourth where clause is not always the fourth row. Is there any way to set a specific order of the rows?
You can add an order by clause to the end. The order or operations in the where clause has no bearing on the order of the rows. For a simple query like this the database will usually (but not always) return the rows in the order they are physically on the hard drive, which is usually the order they were added to the table.
ORDER BY column_name
will tell it in what specific order you want the returned result.