Using joinRelated for multiple relation filtering

Question

I have a collection, a product and a collection_product join table. If I'm trying to filter products based on collection ids, I do the following:

Product.query()
  .modify((builder) => {
    if (collectionIds?.length) {   
      builder 
        .joinRelated({ collections: true })
        .whereIn('collections.id', collectionIds)
        .groupBy('product.id');  
    }
  }

Now we added child products into our product table that are the same as regular products, except they contain the parent id in the parent_product_id column.

These child products are not part of the collection_product table, so the above modifier will only return parent products. Is there a way to also include child products into the results if there's a match on the parent product?

I tried joining both collections and parentProduct.collections, but it didn't work. They only work separately. So by doing this:

.joinRelated({ parentProduct: { collections: true } })
.whereIn('parentProduct:collections.id', collectionIds)

I was able to get all the child products that have parents that belong to a collection, but not the parent products. How can I get both?

Answer 1

Maybe you should use .withGraphJoined({ parentProduct: { collections: true } })

https://vincit.github.io/objection.js/api/query-builder/eager-methods.html#withgraphjoined

instead of .joinRelated. It should also include joined relation to the results.