DT
Drizzle Team•15mo ago
html_extraordinaire

Looking for suggestions

I feel like I had to jump through way too many hoops to do this and was wondering if anyone wants to point out any potential improvements. For starters, I kept getting errors about an ambiguous date column, which isn't that bad considering the fix was to just rename the column in date_series_query to date_series_date. Try changing it back to date and you'll see what I mean. But also look at how I'm having to use concat because Postgres otherwise complained about not being able to tell what type the parameters were. I feel like there's a lot that can be improved here and I'm just looking for suggestions. 
xport async function dailyTotals(input: DailyTotalsInput) {
  const year = db
    .select({ year: irrigationYear.year })
    .from(irrigationYear)
    .where(eq(irrigationYear.id, input.irrigationYearId));

  const dateSeries = db
    .select({
      date_series_date: sql`"date"`.as('date_series_date'),
    })
    .from(
      sql`generate_series(date_trunc('Month', concat(${input.month}::text,' 01, ', ${year})::date), (date_trunc('Month', concat(${input.month}::text, ' 01, ', ${year})::date) + '1 month - 1 day'::interval), '1 day'::interval) as date`
    )
    .as('date_series_query');

  const irrigations = db
    .select({
      date: irrigation.date,
      used: sum(getUsedColumn(irrigation.unit, irrigation.amount))
        .mapWith(Number)
        .as('used'),
    })
    .from(irrigationYear)
    .innerJoin(irrigation, eq(irrigationYear.id, irrigation.irrigationYearId))
    .where(
      and(
        eq(irrigationYear.id, input.irrigationYearId),
        sql`trim(to_char(date(${irrigation.date}), 'Month')) like ${input.month}`
      )
    )
    .groupBy(irrigation.date)
    .as('irrigations_query');

  return await db
    .select({ date: dateSeries.date_series_date, used: irrigations.used })
    .from(dateSeries)
    .leftJoin(irrigations, eq(dateSeries.date_series_date, irrigations.date));
}
xport async function dailyTotals(input: DailyTotalsInput) {
  const year = db
    .select({ year: irrigationYear.year })
    .from(irrigationYear)
    .where(eq(irrigationYear.id, input.irrigationYearId));

  const dateSeries = db
    .select({
      date_series_date: sql`"date"`.as('date_series_date'),
    })
    .from(
      sql`generate_series(date_trunc('Month', concat(${input.month}::text,' 01, ', ${year})::date), (date_trunc('Month', concat(${input.month}::text, ' 01, ', ${year})::date) + '1 month - 1 day'::interval), '1 day'::interval) as date`
    )
    .as('date_series_query');

  const irrigations = db
    .select({
      date: irrigation.date,
      used: sum(getUsedColumn(irrigation.unit, irrigation.amount))
        .mapWith(Number)
        .as('used'),
    })
    .from(irrigationYear)
    .innerJoin(irrigation, eq(irrigationYear.id, irrigation.irrigationYearId))
    .where(
      and(
        eq(irrigationYear.id, input.irrigationYearId),
        sql`trim(to_char(date(${irrigation.date}), 'Month')) like ${input.month}`
      )
    )
    .groupBy(irrigation.date)
    .as('irrigations_query');

  return await db
    .select({ date: dateSeries.date_series_date, used: irrigations.used })
    .from(dateSeries)
    .leftJoin(irrigations, eq(dateSeries.date_series_date, irrigations.date));
}
1 Reply
html_extraordinaire
html_extraordinaireOP•15mo ago
If you change date_series_date to date, that'll cause the ambiguous column error.

Did you find this page helpful?