Drizzle Team•14mo ago

Using Query Syntax to filter by FK's field

hey there, I have a schema of users and their pets name and type. a user can only own one pet, and a pet could have several owners. how would I use query syntax to get all the users that own a dog (type === 'dog')?

export const users = pgTable('users', {
    id: serial('id').primaryKey(),
    petId: text('petId'),
        name: text('name'),
});
export const pets = pgTable('pets', {
    id: serial('id').primaryKey(),
    name: text('name'),
        type: text('type'),
});

export const usersRelations = relations(users, ({ one }) => ({
    pet: one(pets, { fields: [user.petId], references: [pet.id] }),
        
}));

export const users = pgTable('users', {
    id: serial('id').primaryKey(),
    petId: text('petId'),
        name: text('name'),
});
export const pets = pgTable('pets', {
    id: serial('id').primaryKey(),
    name: text('name'),
        type: text('type'),
});

export const usersRelations = relations(users, ({ one }) => ({
    pet: one(pets, { fields: [user.petId], references: [pet.id] }),
        
}));

5 Replies

Angelelz•14mo ago

filtering by nested relations is not allowed anymore. But you could start from the pet, filter by type and get the users from there. I think that you're missing the petsRelations here

BaNuniOP•14mo ago

thanks for your answer - does this mean I have to use the sql-like select syntax? also, i'm curious, why is this not allowed?

Angelelz•14mo ago

You could do this:

db.query.pets.findMany({
  where: eq(pets.type, "dog"),
  with: { users: true }
})

db.query.pets.findMany({
  where: eq(pets.type, "dog"),
  with: { users: true }
})

But then you'll have to aggregate all the users from all the different pets. You can read about it on the release nots for v0.28 They might add it back later

BaNuniOP•14mo ago

Cool, thanks for your help! regarding revrsing the query - my actual case is more complex than that, and requires filters on both tables (also, it actually nests another table in it 🙂 ) I wish the system I'm actually working on were about pets and dogs I'll go back to select

Angelelz•14mo ago

This case seems simple enough that a select with a left join should give you what you want

Gaming

Programming

Using Query Syntax to filter by FK's field