Drizzle Team•13mo ago

Help with query, how to use parent value in where condition ?

Hey everyone, is there any way to pass a value from the parent, which is widgets in this example to a where condition in a left join ?

await db.query.widgets.findFirst({
        where: eq(widgets.code, 'widget1'),
        with: {
            product: {
                with: {
                    productFeedbacks: {
                        where: eq(schema.productFeedbacks.groupBrandCode, *WIDGETS.brandCode*),
                    }
                }
            },
        }
    });

await db.query.widgets.findFirst({
        where: eq(widgets.code, 'widget1'),
        with: {
            product: {
                with: {
                    productFeedbacks: {
                        where: eq(schema.productFeedbacks.groupBrandCode, *WIDGETS.brandCode*),
                    }
                }
            },
        }
    });

2 Replies

Angelelz•13mo ago

I think you should be able to just do:

await db.query.widgets.findFirst({
        where: eq(widgets.code, 'widget1'),
        with: {
            product: {
                with: {
                    productFeedbacks: {
                        where: eq(schema.productFeedbacks.groupBrandCode, widgets.brandCode),
                    }
                }
            },
        }
    });

await db.query.widgets.findFirst({
        where: eq(widgets.code, 'widget1'),
        with: {
            product: {
                with: {
                    productFeedbacks: {
                        where: eq(schema.productFeedbacks.groupBrandCode, widgets.brandCode),
                    }
                }
            },
        }
    });

RodolpheGOP•13mo ago

The query returns all the productFeedbacks for this product, but doesn't filter out the ones from an other groupBrand. It's like the where condition is not there

{
  code: 'widget1',
  groupBrandCode: 'groupBrand1',
  product: {
    code: 'product1',
    productFeedbacks: [
      {
        groupBrandCode: 'groupBrand1',
        productCode: 'product1',
        order: 1,
        value: 'value',
        type: 'pro'
      },
      {
        groupBrandCode: 'groupBrand2',
        productCode: 'product1',
        order: 2,
        value: 'value2',
        type: 'pro'
      }
    ]
  }
}

{
  code: 'widget1',
  groupBrandCode: 'groupBrand1',
  product: {
    code: 'product1',
    productFeedbacks: [
      {
        groupBrandCode: 'groupBrand1',
        productCode: 'product1',
        order: 1,
        value: 'value',
        type: 'pro'
      },
      {
        groupBrandCode: 'groupBrand2',
        productCode: 'product1',
        order: 2,
        value: 'value2',
        type: 'pro'
      }
    ]
  }
}

widgets.groupBrandCode seems to be the schema of the entity not it's value

Gaming

Programming

Help with query, how to use parent value in where condition ?