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

AWS Q Developer image

Your AI Code Assistant

Automate your code reviews. Catch bugs before your coworkers. Fix security issues in your code. Built to handle large projects, Amazon Q Developer works alongside you from idea to production code.

Get started free in your IDE

Top comments (0)

Billboard image

Create up to 10 Postgres Databases on Neon's free plan.

If you're starting a new project, Neon has got your databases covered. No credit cards. No trials. No getting in your way.

Try Neon for Free →

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay