DISTINCT ON LEAST() GREATEST()
SELECT DISTINCT ON (LEAST(author_id, recipient_id), GREATEST(author_id, recipient_id))
id,
text,
author_id,
recipient_id
FROM private_messages
WHERE author_id = ? OR recipient_id = ?
ORDER BY LEAST(author_id, recipient_id), GREATEST(author_id, recipient_id), inserted_at DESC;
This is the query that I would like to run. I am not quite sure after combing through the Ash docs, unfortunately
Ash.Query.distinct([:author_id, recipient_id])
does not quite get me what I am looking for.
The point of this query is to hydrate a view in my application similar to whatsapp, groupme, facebook messages that displays for each user you have messages with the last message sent.
The way I set my database up I did not create an inbox for each user which would have made this a ton easier. Instead I have a private message that has exactly whats listed above, an id, text, author_id, recipient_id and an inserted_at.
I want to basically do a window function or distinct and grab the greatest inserted at for each unique combination of author and recipient.
A conversation would be englishified by saying that its all private_messages for a user_id that is equal to the author_id OR the recipient_id
Is there an easier way to go about doing this?
If not, how do I even run this as a fragment? Is it possible to return my PrivateMessage resource as a result?
I realize I could solve all of this by reworking my database, and I'm more than willing to do that, the thing is I know I'll need to be doing these sorts of queries in the future for analytics purposes as well. Any suggestions? I've spun my head around this for a bit.
5 Replies
raw query?
Gonna go that route I think is best. Just wondering if there's a better way someone smarter with SQL or a way to write it that sort of still fits into the nicely compact Ash box
Can you represent your query with Ecto? If so you can always create a manual read action and use Ecto directly
I certainly can, will still need fragments I think for least and greatest i think, but that makes some sense.
You can add calculations and you should then be able to distinct on them, IIRC