DT
Drizzle Team•10mo ago
piscopancer

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
Sillvva•10mo 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));
piscopancer
piscopancerOP•10mo 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
Sillvva•10mo 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
piscopancer
piscopancerOP•10mo 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
Sillvva•10mo 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.
piscopancer
piscopancerOP•10mo 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
Sillvva•10mo 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
piscopancer
piscopancerOP•10mo 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
Sillvva•10mo 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
    }
  })
}
piscopancer
piscopancerOP•10mo 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
Sillvva•10mo 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, …
piscopancer
piscopancerOP•10mo ago
@Sillvva no new solution is batch it's literally a Promise.all but for sql queries forget about union i spoke of above
Want results from more Discord servers?
Add your server