Need help with Postgres Query

i’m filling charts and use this SQL:

select count(*) as data,to_char(created_at, 'YYYY-MM-DD HH24') as dates from products where to_char(created_at, 'YYYY') = '2019' group by dates order by dates;

to get this:

 "rows": [
            {
                "data": "1",
                "dates": "2019-02-12 00"
            },
            {
                "data": "1",
                "dates": "2019-02-12 03"
            },
.....

but i would like to select all years with their data at once so i get something like this:

"rows": [
       "2018": [
            {
                "data": "1",
                "dates": "2018-02-12 00"
            },
            {
                "data": "1",
                "dates": "2018-02-12 03"
            }
           ],
          "2019": [ 
            {
                "data": "1",
                "dates": "2019-02-12 00"
            },
            {
                "data": "1",
                "dates": "2019-02-12 03"
            }
        ]
    ]

any idea?

I typically would do a ‘group by year’…

select date_part('year', created_at) as crYear
... group by crYear

Or…

select extract(year from created_at)
...
group by 1

… and further transform (using map, a simple for) the resulting JSON if required.

1 Like

This is the RAW query that works fine:

   async getPostChartYearsRAW ({ response }) {
        // Set query
        const years = await Database.raw('SELECT to_char(created_at, \'YYYY\') AS years FROM posts GROUP BY years ORDER BY years')

        response.status(200).json({
            years
        })
    }

now i want to do this without us using RAW:


    async getPostChartYears ({ response }) {
        // Set query
        const query = Post.query()
        query.select('extract(year from created_at) AS years')
        query.groupBy('years')
        query.orderBy('years')
        // Run query
        const years = await query.fetch()

        response.status(200).json({
            years
        })
    }

but this gives me:

error
select "extract(year from created_at)" as "years" from "posts" group by "years" order by "years" asc - column "extract(year from created_at)" does not exist

any idea?

Hmm… I have always used raw for such requirements before. knex had a knex.fn for such things, but I am not aware of an equivalent method in controllers. Sorry.