Am I doing this right?

[epatey]

I'm just getting started with drizzle, and I'm really loving it so far. I've got a relatively simple query that I'm trying to execute with drizzle. The query returns statistics on how many messages have been received within each 10 minute period between some start time and end time.

select
  CONCAT(
    DATE_FORMAT(received, '%Y-%m-%d %H:'),
    LPAD(10 * (MINUTE(received) DIV 10), 2, '00'),
    ":00.000"
  ) AS intervalStart,
  count(*) as totalMessages,
from
  `Position`
where
  (`Position`.`received` > ? and `Position`.`received` < ?)
group by
  intervalStart
order by
  intervalStart;

Here's what I came up with after some struggling:

  await drizzleDb
    .select({
      intervalStart: sql`CONCAT(
          DATE_FORMAT(received, '%Y-%m-%d %H:'),
          LPAD(10 * (MINUTE(received) DIV 10), 2, '00'),
          ":00.000"
      ) AS intervalStart`.mapWith(position.received),
      totalPositions: sql<string>`count(*)`.mapWith(Number),
    })
    .from(position)
    .where(and(gt(position.received, startDate), lt(position.received, endDate)))
    .groupBy(sql`intervalStart`)
    .orderBy(sql`intervalStart`)
)

Am I on track? It took me some time to figure out the .mapWith stuff, but I think I'm doing that right. Also, is the way I'm doing .groupBy and .orderBy correct? Is there a simpler way for me to refer to intervalStart?

Any other pointers?

[Angelelz]

This query it's good! it will work properly. However, some of the selling points of using an ORM is type safety.
There are some hardcoded strings in your query that unless you are really careful, there is no help from your editor/LSP that you wrote it correctly. Unfortunately, you query is complex enough that there not a whole lot to improve, specially in the first column, but there are some stuff:

 await drizzleDb
    .select({
      intervalStart: sql`CONCAT(
          DATE_FORMAT(${position.received}, '%Y-%m-%d %H:'),
          LPAD(10 * (MINUTE(${position.received}) DIV 10), 2, '00'),
          ":00.000"
      )`.as("intervalStart").mapWith(position.received),
      totalPositions: sql<string>`count(*)`.mapWith(Number),
    })
    .from(position)
    .where(and(gt(position.received, startDate), lt(position.received, endDate)))
    .groupBy(({ intervalStart }) => intervalStart)
    .orderBy(({ intervalStart }) => intervalStart)

In order of appearing:

1. Ability to interpolate columns inside the sql operator
2. The .as() method for column alias
3. You can pass a function to orderBy/groupBy and others and that function will receive the columns that you've selected, then you can return the one you need.

[epatey]

Thanks so much for the response. That's exactly the sort of feedback I was looking for.

FWIW, I had to move the .as() to after the .mapWith().