DEV Community

Cover image for KnexJS: how to convert a SQL join with aliases and ordered by values from another table
Jakub
Jakub

Posted on

1 1

KnexJS: how to convert a SQL join with aliases and ordered by values from another table

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.

The script

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
Enter fullscreen mode Exit fullscreen mode

The script joins 2 tables message and conversation.
The result is all conversations ordered by the last created_at message in message table

example of an output result:

id topic origin_type origin_id created_at updated_at
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

The updated_at is deducted from the message table where the last created message as created_at gives which is the newest updated_at conversation.

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 :)

Heroku

This site is built on Heroku

Join the ranks of developers at Salesforce, Airbase, DEV, and more who deploy their mission critical applications on Heroku. Sign up today and launch your first app!

Get Started

Top comments (0)