Many-to-Many where in far table

I'm trying to findMany() Pokemon(s) by a type name

pokemons         pokemonsToType          types
========         ===============         =======
- uuid     <---- - pokemon_uuid     ┌--> - uuid
- name           - type_uuid -------┘    - name

pokemons         pokemonsToType          types
========         ===============         =======
- uuid     <---- - pokemon_uuid     ┌--> - uuid
- name           - type_uuid -------┘    - name

querying like this...

await db.query.pokemons.findMany({
  with: {
    pokemonsToTypes: {
      with: {
        type: { // name of the relation is "type"
          where: (t, { eq }) => eq(t.name, "Water"),
        },
      },
    },
  },
})

await db.query.pokemons.findMany({
  with: {
    pokemonsToTypes: {
      with: {
        type: { // name of the relation is "type"
          where: (t, { eq }) => eq(t.name, "Water"),
        },
      },
    },
  },
})

If the where def. is on the far relation, the result-set is returning item(s) like this:

{
  uuid: '8766f8d9-d83b-4363-aab2-87431d46bb4e',
  name: 'Venusaur',
  pokemonsToTypes: [
    {
      pokemonUuid: '8766f8d9-d83b-4363-aab2-87431d46bb4e',
      typeUuid: 'ec7b093f-b3c3-4644-9e07-8e0f853ea891',
      type: null
    },
    {
      pokemonUuid: '8766f8d9-d83b-4363-aab2-87431d46bb4e',
      typeUuid: 'ed203404-004a-425f-8b83-20163d5fa54c',
      type: null
    }
  ]
}

{
  uuid: '8766f8d9-d83b-4363-aab2-87431d46bb4e',
  name: 'Venusaur',
  pokemonsToTypes: [
    {
      pokemonUuid: '8766f8d9-d83b-4363-aab2-87431d46bb4e',
      typeUuid: 'ec7b093f-b3c3-4644-9e07-8e0f853ea891',
      type: null
    },
    {
      pokemonUuid: '8766f8d9-d83b-4363-aab2-87431d46bb4e',
      typeUuid: 'ed203404-004a-425f-8b83-20163d5fa54c',
      type: null
    }
  ]
}

It returned totally wrong records from pokemons and the pivot table. No of the returned typeUuids are of type.name = 'Water'). Any ideas where I could go wrong? Are where expressions in relational M:N tables supported?

6 Replies

d1ge•15mo ago

did you figure out what was wrong? Just hit the same wall ish.

Luxaritas•15mo ago

What you’re filtering here is the type itself. That is “when retrieving the type, include it if this is true, otherwise return null”, rather than conditionally including pokemonToType. Unfortunately the relational query builder doesn’t support filtering a table on a related table currently - it’s planned, but for now you’d need to use the core query builder

d1ge•15mo ago

So docs is incorrect then, or is it only supported for one-to-many? https://orm.drizzle.team/docs/rqb#select-filters

Luxaritas•15mo ago

The docs are correct. In the example it gives there, it returns the post with id=1 and the subset of comments before some date, NOT the post id=1 IF it has some comments before some date

d1ge•15mo ago

So how would i do this using the query builder?

SELECT 
    p.id, 
    p.name, 
    p.clinic_name, 
    p.address, 
    p.zip_code, 
    p.city, 
    p.picture, 
    p.email, 
    p.website, 
    p.description, 
    p.phone, 
    p.tags, 
    p.subsidies, 
    p.municipality_id, 
    p.region_id, 
    p.active
FROM 
    psychologists p
JOIN 
    psychologists_to_audiences pta ON p.id = pta.psychologist_id
WHERE 
    pta.audience_id = 'YOUR_AUDIENCE_ID';

SELECT 
    p.id, 
    p.name, 
    p.clinic_name, 
    p.address, 
    p.zip_code, 
    p.city, 
    p.picture, 
    p.email, 
    p.website, 
    p.description, 
    p.phone, 
    p.tags, 
    p.subsidies, 
    p.municipality_id, 
    p.region_id, 
    p.active
FROM 
    psychologists p
JOIN 
    psychologists_to_audiences pta ON p.id = pta.psychologist_id
WHERE 
    pta.audience_id = 'YOUR_AUDIENCE_ID';

Something like this?:

      const items = await db.select()
      .from(psychologists)
      .innerJoin(psychologistsToSpecials, and(
        eq(psychologists.id, psychologistsToSpecials.psychologistId),
        input.specialId ? eq(psychologistsToSpecials.specialId, input.specialId) : undefined,
      ))
      .orderBy(asc(psychologists.id))
      .limit(input.limit + 1)
      .where(whereParams)

      const items = await db.select()
      .from(psychologists)
      .innerJoin(psychologistsToSpecials, and(
        eq(psychologists.id, psychologistsToSpecials.psychologistId),
        input.specialId ? eq(psychologistsToSpecials.specialId, input.specialId) : undefined,
      ))
      .orderBy(asc(psychologists.id))
      .limit(input.limit + 1)
      .where(whereParams)

Luxaritas•15mo ago

Looks reasonable on a quick glance

Gaming

Programming

Many-to-Many where in far table