CODEWITHSUNDEEP
sqlpostgresqllateral-join
hernanl
hernanl

Reputation: 127

Query with lateral join is returning a value with parenthesis. How to remove it?

I'm using Postgres for a chat app. There I have two tables, one with the conversation ids and participants, and another table with the messages.

I'm running the following query to get all the conversations for a user, and I use a lateral join to include the last message on the results:

select c.id, c.title, c.members, max(m.created_at) delivered_at, last_message
from conversations c
join messages m on c.id = m.conversation_id
left join lateral (select message from messages m where c.id = m.conversation_id order by created_at desc limit 1) last_message on true 
where array[4] <@ c.members 
group by c.id, last_message
order by delivered_at desc

which works, but the message is returned as (message) and not message

and I don't know why, really. Is there anyting I can do so I don't have to replace the parenthesis as an additional step?

Upvotes: 4

Views: 1683

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

Instead of table name or alias you need to mention column name in select list:

select c.id, c.title, c.members, max(m.created_at) delivered_at, last_message
from conversations c
join messages m on c.id = m.conversation_id
left join lateral (select message from messages m where c.id = m.conversation_id order by created_at desc limit 1) as t(last_message) on true 
where array[4] <@ c.members 
group by c.id, last_message
order by delivered_at desc

Explanation:

In PostgreSQL, a reference to a table name (or alias) in a query is effectively a reference to the composite value of the table's current row. For example, if we had a table inventory_item as shown above, we could write:

SELECT c FROM inventory_item c;

This query produces a single composite-valued column, so we might get output like:

      c
------------------------
 ("fuzzy dice",42,1.99)

(1 row)

Note however that simple names are matched to column names before table names, so this example works only because there is no column named c in the query's tables.

The ordinary qualified-column-name syntax table_name.column_name can be understood as applying field selection to the composite value of the table's current row. (For efficiency reasons, it's not actually implemented that way.)

When we write

SELECT c.* FROM inventory_item c;

then, according to the SQL standard, we should get the contents of the table expanded into separate columns:

name supplier_id price
fuzzy dice 42 1.99 
(1 row)

as if the query were

SELECT c.name, c.supplier_id, c.price FROM inventory_item c;

Quoted from below link: https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE

Upvotes: 5

Related Questions