html_extraordinaire Posts - Answer Overflow

html_extraordinaire

Explore posts from servers

JCHJava Community | Help. Code. Learn.DTDrizzle Team

•Created by html_extraordinaire on 10/5/2024 in #help

Lateral joins

Hi, from what I understand, lateral joins aren't supported by the query builder yet. I'm wondering what options I have to get type safe results from a query like this:

select f.id, f.family_name familyName, f.image_uuid imageUuid, json_agg(fm.data) members from family f left join lateral (select row_to_json(fm) data from (select * from member m where m.family_id = f.id) fm) fm on true group by f.id

6 replies

DTDrizzle Team

•Created by html_extraordinaire on 8/22/2024 in #help

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));
}

2 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/30/2024 in #help

Parsing Postgres array

Trying to parse a Postgres array coming back like this: {ditchrider,ditchrider,ditchrider}. This is my query:

db
.select({
  userRole: user.role,
  soureRole: sql`ARRAY_AGG(${sourceRole.sourceRole})`.as('sourceRole'),
})
.from(user)
.innerJoin(sourceRole, eq(user.id, sourceRole.userId))
.groupBy(user.id)
.where(eq(user.id, input.userId));

db
.select({
  userRole: user.role,
  soureRole: sql`ARRAY_AGG(${sourceRole.sourceRole})`.as('sourceRole'),
})
.from(user)
.innerJoin(sourceRole, eq(user.id, sourceRole.userId))
.groupBy(user.id)
.where(eq(user.id, input.userId));

.mapWith doesn't work.

1 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/25/2024 in #help

When using $dynamic(), are the where conditions merged or overwritten?

Title. Just wondering. The docs weren't super clear about that part in my opinion.

1 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/22/2024 in #help

Are dynamic selects with type inference possible?

Title. I'm trying to add select statements conditionally. I'd like the function to return a properly typed query based on what I've passed. Is that possible?

.select<SelectedFields<PgColumn, PgTable>>({
  ...(opts.groupBy && { irrigationClientId: shrink.irrigationClientId }),
  shrink: sum(shrink.amount).mapWith(Number).as('shrink'),
})

.select<SelectedFields<PgColumn, PgTable>>({
  ...(opts.groupBy && { irrigationClientId: shrink.irrigationClientId }),
  shrink: sum(shrink.amount).mapWith(Number).as('shrink'),
})

This prevents errors but isn't really what I want.

9 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/19/2024 in #help

Subquery in select statement?

Hi, I'm trying to grab a value from a subquery in my select statement for a computed column but I'm getting an error telling me the column from the subquery doesn't exist.

const issuancesSq = tx
  .select({
    totalAcreFeetPerShare: sum(issuance.acreFeetPerShare)
      .mapWith(Number)
      .as('totalAcreFeetPerShare'),
  })
  .from(issuance)
  .innerJoin(
    irrigationYear,
    eq(issuance.irrigationYearId, irrigationYear.id)
  )
  .where(eq(irrigationYear.id, input.irrigationYearId))
  .as('issuancesSq');

const issuancesSq = tx
  .select({
    totalAcreFeetPerShare: sum(issuance.acreFeetPerShare)
      .mapWith(Number)
      .as('totalAcreFeetPerShare'),
  })
  .from(issuance)
  .innerJoin(
    irrigationYear,
    eq(issuance.irrigationYearId, irrigationYear.id)
  )
  .where(eq(irrigationYear.id, input.irrigationYearId))
  .as('issuancesSq');

Attempting to use it inside of the select, here:

const irrigationClients = await tx
  .select({
    available:
      sql`((${irrigationYearShares.shares} * ${issuancesSq.totalAcreFeetPerShare}::numeric) + COALESCE(${auxiliaryIssuancesSq.available}, 0)) - COALESCE(${shrinkSq.shrink}, 0)`.mapWith(
        Number
      ),
    firstName: user.firstName,
    irrigationClientId: irrigationClient.id,
    lastName: user.lastName,
    shares: irrigationYearShares.shares,
    unit: irrigationsSq.unit,
    used: irrigationsSq.used,
  })

const irrigationClients = await tx
  .select({
    available:
      sql`((${irrigationYearShares.shares} * ${issuancesSq.totalAcreFeetPerShare}::numeric) + COALESCE(${auxiliaryIssuancesSq.available}, 0)) - COALESCE(${shrinkSq.shrink}, 0)`.mapWith(
        Number
      ),
    firstName: user.firstName,
    irrigationClientId: irrigationClient.id,
    lastName: user.lastName,
    shares: irrigationYearShares.shares,
    unit: irrigationsSq.unit,
    used: irrigationsSq.used,
  })

9 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/12/2024 in #help

Reset database

I'm using Vercel Postgres and once, a while ago, I reset the database using some command like drizzle-kit push, maybe? IIRC, there was some sort of conflict and it was like, "Sorry, gotta start fresh." Which was fine with me. Sometimes I want to just reset the database but there doesn't seem to be an easy way to do it. How can I force a reset?

4 replies

DTDrizzle Team

•Created by html_extraordinaire on 7/2/2024 in #help

Error: There are multiple relations between "__public__.shareTransferHistory" and "client".

This just recently became a problem and I can't figure out why. Here's my schema: https://gist.github.com/aaronfulkerson/a73f3745ce04d0a2b62ffd26c6457833 I'm using relationName to disambigurate the relations on shareTransferHistory but I'm completely lost. I feel like this was working until very recently but can't figure out why it's suddenly broken.

3 replies

Gaming

Programming