Drizzle Team•13mo ago

How to filter query based on children table?

Hi friends, first time using Drizzle so be gentle, this question may be dumb. I designed a model where I have a country table and a country_language table, as you can see in the attachment. Let's say that I want to return the country which has the slug (from the country_language table)="portugal-en". How can I write that expression? Here's my try:

const country = await db.query.countries.findFirst({
  with: {
    countryLanguages: {
      where: eq(countryLanguages.slug, countrySlug),
    }
  },
});

const country = await db.query.countries.findFirst({
  with: {
    countryLanguages: {
      where: eq(countryLanguages.slug, countrySlug),
    }
  },
});

However, this is returning the first country and then just filtering the country_languages table, which is not what I want. I tried to move the where clause to the country level, but didn't worked, since that column (slug) does not exist on the country table). Thank you!

3 Replies

Angelelz•13mo ago

They removed the ability to select base on nested relations in v0.28 But this can be done with a subquery or an inner join Take a look at this example for something similar: https://discord.com/channels/1043890932593987624/1166190702984708126/1166200301133504522

nelsonprsousaOP•13mo ago

Do you know why they removed it?

Angelelz•13mo ago

Yes, it's on the release notes

Gaming

Programming

How to filter query based on children table?