I am working on a project on AdonisJs and Lucid ORM where i need to show a chart with user registration count grouped by every month (created_at -> month name) for the last year.
I did this query in Laravel eloquent but didn't find a solution for how to do this query in Lucid.
Please note: I'm using PostgreSQL and CockroachDB
User table columns
id | username | email | created_at | updated_at
PostgreSQL query
SELECT
to_char(date_trunc('month', created_at), 'YYYY, Month') AS created_month,
COUNT(id) AS total_registrations,
FROM users
GROUP BY date_trunc('month', created_at)
ORDER BY date_trunc('month', created_at)
I really need to know how I can do the same using Lucid ORM.
I'll appreciate any help from you. Best regards!
