Query Help- too many joins resulting in cartesian product

hibernatorr · **Joined:** Fri Oct 12, 2007 11:52 am **Posts:** 5

Hi.
I have been playing around with hibernate where I work for some time now. I am getting good at it, and the hurdles I am facing now is in making HQL queries.

I can write HQL, i think its just plain inefficient.

My Object looks like this:

Code:

class TShirt       -has many ->   TShirtFeatures
class TShirtFeatures    - has one ->    TDesign - has one ->Designer
class TShirt        -has one ->   TShirtBatch
class ->TShirtBatch   -has many ->   TShirtBatchBusiness   -has one ->   Business

Each of these classes has a table in the database.

Now i need to find TShirts for a particular
Designer.name
and
TShirtBatchBusiness .Business.businessTypeEnum

---
This is where I get inefficient with my query:

Code:

Select t from TShirt t
LEFT JOIN t.tshirtFeatures tf
LEFT JOIN t.tshirtBatch.TShirtBatchBusiness tbb
where 
and tf.tDesign.designer=:designerParameter
and tbb.business.typeEnum=:businessTypeEnumStringParameter

well, first of all, it results in cartesian product by business.typeEnum
(as many rows as there are constants in the enum)

Second of all, i think there are too many joins

Is it better to get records first and then use java to iterate over them?
or should i just change my query?

My mapping loads all sets as lazy.
I thought of using sub-select fetching, but that is not possible in the current design

Any help would be very much appreciated

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Hi.

Your joins, in principle, look alright according to the object domain you describe.

Since you are still getting a cartesian product and all the business types, I would assume the problem is how the batch/batchBusiness/business trio is mapped, in the mapping files.
Can you provide the mapping files?

hibernatorr · **Joined:** Fri Oct 12, 2007 11:52 am **Posts:** 5

I am so sorry, while mapping from actual class names to hypothetical names for the hibernate forum, i made a mistake in the problem. I apologize. I am sorry.

My Object graph and SQL have no mistake.
The part that describes where the cartesian product is mistaken during translation

The correction is:

it results in cartesian product by tf.tDesign.designer
(as many rows as there are tDesigns)

---------------

The rest is correct.
---------------

Gonzao, thank you for your reply and patience.

My mapping looks like this:

Code:

class TShirt{
..
    @OneToMany(mappedBy = "tshirt", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @Cascade(org.hibernate.annotations.CascadeType.DELETE_ORPHAN)
    @org.hibernate.annotations.OrderBy(clause="(select date_info .defDate from date_info where date_info.dateInfoID= dateInfoID), timeStamp") 
    private Set<TShirtFeatures> tshirtFeatures;
..
}

class TShirtFeatures{
..
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "TSHRT_DESGN_ID")
    private TShirtDesign tDesign;
..
}

Mapping from TShirt to TShirtBatch looks like this:

Code:

class TShirt{
..
    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name = "TSHRT_BATCH_ID")
    private TShirtBatch tshirtBatch;
..
}

class TShirtBatch{
..
    @OneToMany(mappedBy = "tshirtBatch", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @Cascade(org.hibernate.annotations.CascadeType.DELETE_ORPHAN)
    @org.hibernate.annotations.OrderBy(clause="(select date_info .defDate from date_info where date_info.dateInfoID= dateInfoID), timeStamp") 
    private Set<TShirtBatchBusiness> tshirtBatchBusiness;
..
}

hibernatorr · **Joined:** Fri Oct 12, 2007 11:52 am **Posts:** 5

Could anyone suggest something

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

You describe in your domain model that the relationship between Design and Designer is one-to-one, however, you get multiple Designers per Design, is that correct?

If so, please post the generated mapping file for Designer, as well as describe the tables behind both Designer and Design. Since there is no indication in your HQL query that Designer needs any parameter to establish its 1-to-1 character, the problem must be in how these 2 tables end up being mapped.