Maastonakki Comments - Answer Overflow

Maastonakki

Explore posts from servers

•Created by Maastonakki on 10/12/2024 in #help

Calculating average of joined table column

The interesting part is, that if I try to use ${jobs.rating} and ${jobs.contractorId} in COALESCE, those get resolved for table contractors

3 replies

DTDrizzle Team

•Created by Maastonakki on 10/12/2024 in #help

Calculating average of joined table column

Actually got this working with this:

const anotherContractor = await db.query.contractors.findMany({
  limit: 1,
  where: eq(contractors.id, 364),
  extras: {
    rating: sql<number>`
COALESCE((
  SELECT ROUND(AVG("job_job"."rating"), 2) 
  FROM ${jobs} 
  WHERE "job_job"."contractor_id" = ${contractors.id}
), 0)`.as("rating"),
  },
  with: {
    user: {
      columns: {
        id: true,
        email: true,
        username: true,
        slug: true,
      },
    },
    languages: {
      with: {
        language: true,
      },
    },
    jobs: {
      columns: { id: true },
    },
  },
});

const anotherContractor = await db.query.contractors.findMany({
  limit: 1,
  where: eq(contractors.id, 364),
  extras: {
    rating: sql<number>`
COALESCE((
  SELECT ROUND(AVG("job_job"."rating"), 2) 
  FROM ${jobs} 
  WHERE "job_job"."contractor_id" = ${contractors.id}
), 0)`.as("rating"),
  },
  with: {
    user: {
      columns: {
        id: true,
        email: true,
        username: true,
        slug: true,
      },
    },
    languages: {
      with: {
        language: true,
      },
    },
    jobs: {
      columns: { id: true },
    },
  },
});

3 replies

DTDrizzle Team

•Created by Nacho Elias on 6/18/2024 in #help

Many to many flatten response

I was just about to write same question, funny enough having also languages relation which I would like to get flattened.

2 replies

Gaming

Programming