Complex query with nested joins

[glass flame]

Hi guys, im looking for help in creating complex query for me.
So i have two tables
Product:

• id
  ...
• categories (joined with Category table)
  Discount:
• id
  ...
• productId (as fk)
• categoryId (as fk also)

and there is a product (id: 'someproductid', categories: ['somecategory']),
an category (id: 'somecategory')
and a discount (id: 'somediscountid', productId: null, categoryId: 'somecategory')

What I'm trying to do is to provide only product.id and find those discounts which has product's category. Query starts in discount service and it's look like that for now (i'll skip some code blocks ofc)

let query = this.discountRepository.createQueryBuilder('discount');
query
  .leftJoinAndSelect('discount.product', 'product')
  .leftJoinAndSelect(
        'product.categories',
        'productCategories',
        'productCategories.audit_rd IS NULL AND productCategories.audit_ru IS NULL',
      )
  .leftJoinAndSelect('discount.users', 'users')
  .leftJoinAndSelect('discount.category', 'category');
query.select([
      'discount',
      'product.id',
      'product.name',
      'productCategories.name',
    ]);
if (dto.productId) {
  query.andWhere(
    new Brackets(qb => {
      qb
        .orWhere('product.id = :productId', { productId: dto.productId})
        .orWhere('category.name = productCategories.name) 
        // Also tried with `IN productCategories.name` but it throws error
    })  
  )
}

Plz help

Complex query with nested joins

[wary epoch]

So, to get this straight, there is a junction table, productCategories, that holds the product to categories relations, correct? And each discount is a one to one with category? So, not sure about using TypeORM or what it would be, but I would approach the SQL something like

SELECT
  p.id
  , p.name
  , c.name
  , d.* -- with fancy SQL work, this could be made an array with the use of something like ARRAY_AGG() in Postgres. Not sure what your database is here
FROM
  product AS p
LEFT JOIN
  productCategories pc ON pc.productId = p.id
LEFT JOIN
  categories c ON c.id  = pc.categoryId
LEFT JOIN
  discounts d ON d.categoryId = c.id
WHERE p.id = providedId

You should also be able to tack on whatever other restrictions you need per join, this could just be the base form of it

[glass flame]

So, to get this straight, there is a junction table, productCategories, that holds the product to categories relations, correct? - Yeah, nothing fancy here, junction table between
And each discount is a one to one with category? - It's a one-to-many relation, discount can (optional) have category and category can have multiple discounts
I'm using postgres.
Thanks for you'r answer, I'll try to adapt my TypeORM query

[urban mesa]

  async getProductWithDiscounts(productId: number) {
    const product = await this.em.findOne(Product, 
      { id: productId },
      {
        populate: [
          'categories.category',  // product -> categories -> category
          'categories.category.discounts'  // category -> discounts
        ],
        fields: [
          'id', 
          'name', 
          'categories.category.name',
          'categories.category.discounts'
        ]
      }
    );
    
    return product;
  }
}