DT
Drizzle Teamsrigi

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?
D
d1ge•245d ago
did you figure out what was wrong? Just hit the same wall ish.
L
Luxaritas•245d 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
D
d1ge•245d ago
So docs is incorrect then, or is it only supported for one-to-many? https://orm.drizzle.team/docs/rqb#select-filters
L
Luxaritas•245d 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
D
d1ge•245d 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)
L
Luxaritas•245d ago
Looks reasonable on a quick glance
Want results from more Discord servers?
Add your server
More Posts
Cannot call onConflictDoNothing() or on onConflctDoUpdate() on selectHello guys, this is probably right in front of me, but when I insert at the end of values I cannot fTop-level await is not available in the configured target environment ("chrome87", "edge88", "es2020Hey I just upgraded to the v^0.26.2 and I get the following error: ``` Top-level await is not avaican you Infer relations?im really loving how drizzle plays well with relations, but im wondering is there a way to get typesubRows.map is not a functionI am getting a subRows.map is not a function error when adding the "with" parameter to my relationalPostgres's Serial column type doesn't automatically have a defaultHello, when using postgres's `serial` types, and setting them as primary keys, there is an issue curHow to insert into a table without providing valuesI've got the following schema for users in sqlite, it only contains an id, which is an auto incremenDoent infer type in Callback syntax for queryIm querying the users table like this `db.query.user.findFirst({ where: (user, { eq }) => { Bug When Trying To Increment A Fieldthis set the field to 0 instead of increasing by one it used to work in other routes ``` await Maximum call stack exceeded with relation to selfI have the following table ``` export const category = pgTable('categories', { id: serial('id').importing into schema.ts fileHi, I am using turborepo and I have defined my schema in `packages/schema/user.ts` and in my main AWhat versions of MySQL are supported? I have JSON_ARRAYAGG issues with 5.7 and AWS Aurora ServerlessCan you confirm what versions of MySQL you intend on supporting? Relational queries with joins use J`$with` example in docs error "unsupported: with expression in select statement"I have a user with id of 42, just like the docs https://orm.drizzle.team/docs/crud#with-clause but Infer type for relational queryIs there anyway to infer the type of a relational queries result? For example if I have a user who in drizzle.config.ts: "Cannot use import statement outside a module"happens when I try to `push` ```ts import type { Config } from "drizzle-kit"; import { env } from "Error when using Drizzle (Non-abstract class 'PgSelect<TTableName, TSelection, TSelectMode, [...])I'm getting the following error when I try to use Drizzle: ```> graphql-server@1.0.0 start > npm runGenerate classes/interfaces from existing schemas?Messing around with drizzle, and I was wondering if there was any way to generate a class or interfa