Hello DEV community,
I'm writing here as I'm a new in KnexJS query builder and need help to how to make a specific
SQL script in KnexJS.
SELECT c.id, c.topic, c.origin_type, c.origin_id, c.created_at, MAX(m.updated_at) updated_at FROM conversation AS c JOIN message m ON m.conversation_id = c.id GROUP BY c.id, c.topic, c.origin_type, c.origin_id, c.created_at ORDER BY updated_at DESC
The script joins 2 tables
The result is all conversations ordered by the last created_at message in message table
example of an output result:
|2||xx||aaa||bbb||2021-04-07 14:47:55.55+00||2021-04-07 18:47:43.558+00|
|1||xx||aaa||bbb||2021-04-07 14:37:45.446+00||2021-04-07 18:12:01.91+00|
|3||xx||aaa||bbb||2021-04-07 14:48:04.055+00||2021-04-07 14:56:52.709884+00|
updated_at is deducted from the message table where the last created message as
created_at gives which is the newest
All what I need is to convert to KnexJs but I have no idea how to make it.
I hope what I explained is understandable and hope to get some help with that so I may learn the ideal way of doing it.
Thank you for yor time spent reading my help request :)