Blaze Join order

[shopera007]

This is my code and I'm not finding any way to force blaze to first join with the CTE before doing implicit joins from the entity view :

 final BlazeCriteriaBuilder blazeCriteriaBuilder = BlazeCriteria.get(this.criteriaBuilderFactory);
            final BlazeCriteriaQuery<Product> blazeCriteriaQuery = blazeCriteriaBuilder.createQuery(Product.class);

            final Root<Product> root = blazeCriteriaQuery.from(Product.class);

            final var criteriaBuilder = blazeCriteriaQuery.createCriteriaBuilder(this.entityManager);

            criteriaBuilder
                    .innerJoinOnSubquery(CoPurchasesCTE.class, "CoPurchasesCTE")
                    .from(CoPurchasingPair.class)
                    .bind(CoPurchasesCTE_.PRODUCT_ID).select("product2.id")
                    .bind(CoPurchasesCTE_.FREQUENCY).select("frequency")
                    .where("product1.id").eq(productId)
                    .orderByDesc("frequency")
                    .setMaxResults(limit)
                    .end()
                    .on("id").eqExpression("CoPurchasesCTE.productId")
                    .end();

            final var entityViewSetting = EntityViewSetting.create(ProductCardView.class);

            final var productCardViewCriteriaBuilder = this.entityViewManager.applySetting(entityViewSetting, criteriaBuilder);

            return productCardViewCriteriaBuilder
                    .getResultList();

Result query :

select
       *
    from
        product p1_0 
    left join
        product_tag t1_0 
            on p1_0.id=t1_0.product_id 
    left join
        tag t2_0 
            on t2_0.id=t1_0.tag_id 
    join
        (select
            cpp1_0.product2_id, cpp1_0.frequency 
        from
            co_purchasing_pair cpp1_0 
        where
            cpp1_0.product1_id=? 
        order by
            cpp1_0.frequency desc 
        fetch
            first 10 rows only) cpc1_0(product_id, frequency) 
            on (p1_0.id=cpc1_0.product_id) 

How can i please force Blaze to first join with CTE sub query then move on to the next joins. Thank you :)

[beikov]

SQL is declarative, so the order or joins in this particular case shouldn't matter. What problem are you trying to solve?

[shopera007]

Thank you for your response. In my query, I use both INNER JOIN and LEFT JOIN. If I perform the INNER JOIN between the product and my CTE first, there will be fewer rows for the subsequent joins, leading to better query optimization. For example, if we have three tables A, B, and C:

Performing A INNER JOIN B LEFT JOIN C will generally be more efficient than A LEFT JOIN B INNER JOIN C.

[beikov]

Can you try adding the following before calling this.entityViewManager.applySetting(...):

criteriaBuilder.leftJoinDefault("tags", "t")

I think that this might change the join order at least in the HQL that is produced, though Hibernate ORM could still re-order joins potentially.

[shopera007]

It worked as expected. I just need to arrange all the LEFT JOIN clauses in the desired order within my query.

Thank you so much for your help; you saved me a lot of time!

[beikov]

Glad it worked out for you!