Drizzle Team•9mo ago

count() for join tables in query API

Usage I have a user page where you can see how many of their answers were considered correct (a person who asked a question accepted them, like on github). 3 tables are used: * users * questions * answers each answer has a column authorId which refers user.id, each question has acceptedAnswerId which refers answer.id

export function queryUser({ userId }: { userId: number }) {
  return db.query.usersTable.findFirst({
    where: (t, { eq }) => eq(t.id, userId),
    with: {
      questions: {
        with: {
          answers: {
            columns: {
              id: true,
              authorId: true,
            },
          },
          usersLikes: {
            columns: {
              questionId: true,
              userId: true,
            },
          },
          usersViews: {
            columns: {
              questionId: true,
            },
          },
        },
      },
    },
    extras: (t) => ({
      // a number of "answers" (table, see above) whose "authorId" === t.id 
      // should return an integer 0/1/2/3/4/5/...
      acceptedAnswers: count(t.id).as('accepted_answers'),
    }),
  })
}

export function queryUser({ userId }: { userId: number }) {
  return db.query.usersTable.findFirst({
    where: (t, { eq }) => eq(t.id, userId),
    with: {
      questions: {
        with: {
          answers: {
            columns: {
              id: true,
              authorId: true,
            },
          },
          usersLikes: {
            columns: {
              questionId: true,
              userId: true,
            },
          },
          usersViews: {
            columns: {
              questionId: true,
            },
          },
        },
      },
    },
    extras: (t) => ({
      // a number of "answers" (table, see above) whose "authorId" === t.id 
      // should return an integer 0/1/2/3/4/5/...
      acceptedAnswers: count(t.id).as('accepted_answers'),
    }),
  })
}

Draw your attention to the extras, what should be written there? I am not quite following

12 Replies

Sillvva•9mo ago

Could do something like this

const acceptedAnswers = db
    .select()
    .from(answers)
    .innerJoin(questions, eq(answers.id, questions.acceptedAnswerId))
    .where(eq(answers.authorId, userId));

const acceptedAnswers = db
    .select()
    .from(answers)
    .innerJoin(questions, eq(answers.id, questions.acceptedAnswerId))
    .where(eq(answers.authorId, userId));

piscopancerOP•9mo ago

ok and i assume i can also integrate this function right inside my extras, right? because i remember in drizzle you can compose Select api queries (provided by you) and Query api queries (my code)

Sillvva•9mo ago

Never tried it. The docs don't show that possibility and explicitly state that aggregations are not possible in extras But you could get the user and accepted answers in separate queries

piscopancerOP•9mo ago

yeah but you know for sure it's bad and sql is designed to query whatever data you want, no matter its complexity at one call. that's my goal here this errs right now

extras: (t) => ({
  // a number of "answers" (table, see above) whose "authorId" === t.id
  acceptedAnswers: acceptedAnswers(userId).as('accepted_answers'),
}),

extras: (t) => ({
  // a number of "answers" (table, see above) whose "authorId" === t.id
  acceptedAnswers: acceptedAnswers(userId).as('accepted_answers'),
}),

but i will try things

Sillvva•9mo ago

Depending on how your relations are setup, it might be possible to get the full list of accepted answers rather than an aggregated count in the initial query.

piscopancerOP•9mo ago

yes ofc it's possible, but this is too much data. my use case for this query is a user page with their stats and i need to display how many (literally a number) of questions they answered correctly, according to the questions creators

Sillvva•9mo ago

You may need to do a core query (db.select()) instead then as aggregations do not look to be possible with the query builder. At least not yet

piscopancerOP•9mo ago

oh, do you mean i cannot query more than one table at once with query api atm? and i need to, let's say used Select api to query from more than 1 table

Sillvva•9mo ago

I'm saying things like count() and getting back a number aren't possible. When using the query builder, aggregations need to be done in the code. Something like this:

export function queryUser({ userId }: { userId: number }) {
  return db.query.usersTable.findFirst({
    where: (t, { eq }) => eq(t.id, userId),
    with: {
      questions: {
        with: {
          answers: {
            columns: {
              id: true,
              authorId: true,
            },
          },
          usersLikes: {
            columns: {
              questionId: true,
              userId: true,
            },
          },
          usersViews: {
            columns: {
              questionId: true,
            },
          },
        },
      },
      answers: {
        columns: {
          id: true
        },
        with: {
          questions: {
            columns: {
              acceptedAnswerId: true,
            }
          }
        }
      }
    },
  }).then(user => {
    const acceptedAnswers = user.answers.filter(answer => answer.questions.acceptedAnswerId === answer.id)
    return {
      ...user,
      acceptedAnswers: acceptedAnswers.length
    }
  })
}

export function queryUser({ userId }: { userId: number }) {
  return db.query.usersTable.findFirst({
    where: (t, { eq }) => eq(t.id, userId),
    with: {
      questions: {
        with: {
          answers: {
            columns: {
              id: true,
              authorId: true,
            },
          },
          usersLikes: {
            columns: {
              questionId: true,
              userId: true,
            },
          },
          usersViews: {
            columns: {
              questionId: true,
            },
          },
        },
      },
      answers: {
        columns: {
          id: true
        },
        with: {
          questions: {
            columns: {
              acceptedAnswerId: true,
            }
          }
        }
      }
    },
  }).then(user => {
    const acceptedAnswers = user.answers.filter(answer => answer.questions.acceptedAnswerId === answer.id)
    return {
      ...user,
      acceptedAnswers: acceptedAnswers.length
    }
  })
}

piscopancerOP•9mo ago

@Sillvva oh my god. I have read some docs in drizzle and found out that you can combine absolutely unrelated queries using unions, and that is exactly what I need for two separate tables

import { union } from 'drizzle-orm/sqlite-core'
import { users, customers } from './schema'

const allNamesForUserQuery = db.select({ name: users.name }).from(users);

const result = await union(
  allNamesForUserQuery,
  db.select({ name: customers.name }).from(customers)
).limit(10);

import { union } from 'drizzle-orm/sqlite-core'
import { users, customers } from './schema'

const allNamesForUserQuery = db.select({ name: users.name }).from(users);

const result = await union(
  allNamesForUserQuery,
  db.select({ name: customers.name }).from(customers)
).limit(10);

you can find this example on their docs. damn I am so stupid, I would have already known that if I learned SQL in the first place instead of jumping straight into ORM totally missing any SQL knowledge

Sillvva•9mo ago

Yeah. Some things to keep in mind with Unions, though. https://www.postgresql.org/docs/16/queries-union.html - The number and the order of the columns must be the same in all queries - The data types must be compatible, meaning that they can be converted to a common type implicitly - The column names in the result set are usually equal to the column names in the first query

PostgreSQL Documentation

7.4. Combining Queries (UNION, INTERSECT, EXCEPT)

7.4. Combining Queries (UNION, INTERSECT, EXCEPT) # The results of two queries can be combined using the set operations union, intersection, …

piscopancerOP•9mo ago

@Sillvva no new solution is batch it's literally a Promise.all but for sql queries forget about union i spoke of above

Gaming

Programming

count() for join tables in query API