Left join fetch possible

[soda]

Does the QueryDsl classes allow user to do a "left join fetch" - i.e. eagerly fetch a collection even though the association has been defined as Lazy fetch type

if so could you please send an example thanks

[Timo Westkämper]

Hi.

Which Querydsl module are you talking about?

TImo

On Fri, Jun 1, 2012 at 12:16 PM, Querydsl on behalf of soda <quer...@googlegroups.com> wrote:

Does the QueryDsl classes allow user to do a "left join fetch" - i.e. eagerly fetch a collection even though the association has been defined as Lazy fetch type

if so could you please send an example thanks

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

[soda]

well, Im thinking about using the hibernate querydsl: http://blog.mysema.com/2010/07/querying-hibernate-with-querydsl.html , but only see normal joins in docs

[Timo Westkämper]

Hi.

Left joins are supported via leftJoin : http://www.querydsl.com/static/querydsl/2.6.0/apidocs/com/mysema/query/jpa/impl/JPAQuery.html

Timo

[soda]

yes but can I do the equivalent of a hibernate specific "left join fetch.."

[Timo Westkämper]

Yes.

leftJoin(...).fetch()

[Sathiyan Bakthavachalu]

Hi Tim,

I am trying to use join fetch as prescribed on this article to avoid ORM n+1  performance problem as mentioned in this article. http://en.wikibooks.org/wiki/Java_Persistence/Relationships#Example_of_Multiple_JPQL_Join_Fetch

I tried using leftJoin().fetch(). It does not seems to solve the problem. Here is my implementation. First the query with join is created and objects are populated. Then, JPA tries to get information from Customer Contac taddress one at a time based on PK. Then goes to CustomerContact profile , get the info based on PK. Then tries to put together the info.Can you please suggest a solution using query DSL? Thanks

public List<CustomerContact> findAll(Predicate predicate) {

QCustomerContact customerContact = QCustomerContact.customerContact;

JPAQuery query = new JPAQuery(entityManager());

query.from(customerContact)

.leftJoin(customerContact.contactAddress, QCustomerContactAddress.customerContactAddress).fetch()

.leftJoin(customerContact.contactProfile, QCustomerContactProfile.customerContactProfile).fetch()

.where(predicate)

.orderBy(customerContact.contactSequence.asc());

return query.list(QCustomerContact.customerContact);

}

[Timo Westkämper]

Hi.

Could you also try the leftJoin methods without the alias argument:

query.from(customerContact)

.leftJoin(customerContact.contactAddress).fetch()

.leftJoin(customerContact.contactProfile).fetch()

.where(predicate)

.orderBy(customerContact.contactSequence.asc());

Timo

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sathiyan Bakthavachalu]

Hi ,

I tried same issue. Just to let you know, Customer Contact has many to one relationship with Customer Contact address and one to one relationship with Customer Contact Profile.  Thanks

On Thursday, September 25, 2014 9:29:36 AM UTC-5, timowest wrote:

Hi.

Could you also try the leftJoin methods without the alias argument:

query.from(customerContact)

.leftJoin(customerContact.contactAddress).fetch()

.leftJoin(customerContact.contactProfile).fetch()

.where(predicate)

.orderBy(customerContact.contactSequence.asc());

Timo

[Sathiyan Bakthavachalu]

Also, we use query DSL version:2.8.1.

[Timo Westkämper]

Hi.

You verified by looking at the SQL that the joins are not used?

Timo

[Timo Westkämper]

Hi.

You should upgrade to a newer version, if possible.

Timo

[Sathiyan Bakthavachalu]

I upgraded to 3.4.3 , still the same issue. Here is the snippet from the log:

The query gets generated corectly. Then it loads 3800 rows of Customer Contact Profile and then move to loading Cusotmer Contact Address. Once it is doen, it tries to put to gether everything:

DEBUG - HQL: select customerContact

from com.XXX.customer.entity.CustomerContact customerContact

  left join fetch customerContact.contactAddress

  left join fetch customerContact.contactProfile

where customerContact.companyNumber = ?1 and customerContact.customerNumber = ?2

order by customerContact.contactSequence asc

DEBUG - --- HQL AST ---

 \-[QUERY] Node: 'query'

    +-[SELECT_FROM] Node: 'SELECT_FROM'

    |  +-[FROM] Node: 'from'

    |  |  +-[RANGE] Node: 'RANGE'

    |  |  |  +-[DOT] Node: '.'

    |  |  |  |  +-[DOT] Node: '.'

    |  |  |  |  |  +-[DOT] Node: '.'

    |  |  |  |  |  |  +-[DOT] Node: '.'

    |  |  |  |  |  |  |  +-[IDENT] Node: 'com'

    |  |  |  |  |  |  |  \-[IDENT] Node: 'XXX'

    |  |  |  |  |  |  \-[IDENT] Node: 'customer'

    |  |  |  |  |  \-[IDENT] Node: 'entity'

    |  |  |  |  \-[IDENT] Node: 'CustomerContact'

    |  |  |  \-[ALIAS] Node: 'customerContact'

    |  |  +-[JOIN] Node: 'join'

    |  |  |  +-[LEFT] Node: 'left'

    |  |  |  +-[FETCH] Node: 'fetch'

    |  |  |  \-[DOT] Node: '.'

    |  |  |     +-[IDENT] Node: 'customerContact'

    |  |  |     \-[IDENT] Node: 'contactAddress'

    |  |  \-[JOIN] Node: 'join'

    |  |     +-[LEFT] Node: 'left'

    |  |     +-[FETCH] Node: 'fetch'

    |  |     \-[DOT] Node: '.'

    |  |        +-[IDENT] Node: 'customerContact'

    |  |        \-[IDENT] Node: 'contactProfile'

    |  \-[SELECT] Node: 'select'

    |     \-[IDENT] Node: 'customerContact'

    +-[WHERE] Node: 'where'

    |  \-[AND] Node: 'and'

    |     +-[EQ] Node: '='

    |     |  +-[DOT] Node: '.'

    |     |  |  +-[IDENT] Node: 'customerContact'

    |     |  |  \-[IDENT] Node: 'companyNumber'

    |     |  \-[PARAM] Node: '?'

    |     |     \-[NUM_INT] Node: '1'

    |     \-[EQ] Node: '='

    |        +-[DOT] Node: '.'

    |        |  +-[IDENT] Node: 'customerContact'

    |        |  \-[IDENT] Node: 'customerNumber'

    |        \-[PARAM] Node: '?'

    |           \-[NUM_INT] Node: '2'

    \-[ORDER] Node: 'order'

       +-[DOT] Node: '.'

       |  +-[IDENT] Node: 'customerContact'

       |  \-[IDENT] Node: 'contactSequence'

       \-[ASCENDING] Node: 'asc'

  

DEBUG - SQL: select customerco0_.M0QMNB as M1_5_0_, customerco1_.MYSEQN as MYSEQN6_1_, customerco1_.MYADDC as MYADDC6_1_, customerco1_.MYCONO as MYCONO6_1_, customerco2_.M1P9CD as M1_7_2_, customerco2_.M1QMNB as M2_7_2_, customerco2_.M1CONO as M3_7_2_, customerco0_.M0P9CD as M2_5_0_, customerco0_.M0CONO as M3_5_0_, customerco0_.M0ADSQ as M4_5_0_, customerco0_.M0CNTI as M5_5_0_, customerco0_.M0JATX as M6_5_0_, customerco0_.M0I9TX as M7_5_0_, customerco0_.M0MDIN as M8_5_0_, customerco1_.MYCITY as MYCITY6_1_, customerco1_.MYNQTX as MYNQTX6_1_, customerco1_.MYADD1 as MYADD6_6_1_, customerco1_.MYADD2 as MYADD7_6_1_, customerco1_.MYH2CD as MYH8_6_1_, customerco1_.MYZIPC as MYZIPC6_1_, customerco2_.M1EMAD as M4_7_2_, customerco2_.M1EMMF as M5_7_2_ from MKP30 customerco0_ left outer join MKP70 customerco1_ on customerco0_.M0ADSQ=customerco1_.MYSEQN and customerco0_.M0P9CD=customerco1_.MYADDC and customerco0_.M0CONO=customerco1_.MYCONO left outer join MKP35 customerco2_ on customerco0_.M0P9CD=customerco2_.M1P9CD and customerco0_.M0QMNB=customerco2_.M1QMNB and customerco0_.M0CONO=customerco2_.M1CONO where customerco0_.M0CONO=? and customerco0_.M0P9CD=? order by customerco0_.M0QMNB asc

DEBUG - Result set row: 0

DEBUG - Result row: EntityKey[com.XXX.customer.entity.CustomerContact#component[contactSequence]{contactSequence=1}], null, EntityKey[com.XXX.customer.entity.CustomerContactProfile#component[customerNumber,contactSequence,companyNumber]{contactSequence=1, companyNumber=01, customerNumber=  103048}]

DEBUG - Result set row: 1

DEBUG - Result row: EntityKey[com.XXX.customer.entity.CustomerContact#component[contactSequence]{contactSequence=2}], null, EntityKey[com.XXX.customer.entity.CustomerContactProfile#component[customerNumber,contactSequence,companyNumber]{contactSequence=2, companyNumber=01, customerNumber=  103048}]

DEBUG - Result set row: 2

DEBUG - Result row: EntityKey[com.XXX.customer.entity.CustomerContact#component[contactSequence]{contactSequence=3}], null, EntityKey[com.XXX.customer.entity.CustomerContactProfile#component[customerNumber,contactSequence,companyNumber]{contactSequence=3, companyNumber=01, customerNumber=  103048}]

DEBUG - Result set row: 3

.

.

.

DEBUG - Result set row: 3812

DEBUG - Result row: EntityKey[com.XXX.customer.entity.CustomerContact#component[contactSequence]{contactSequence=3813}], null, EntityKey[com.XXX.customer.entity.CustomerContactProfile#component[customerNumber,contactSequence,companyNumber]{contactSequence=3813, companyNumber=01, customerNumber=  103048}]

DEBUG - Resolving associations for [com.XXX.customer.entity.CustomerContact#component[contactSequence]{contactSequence=1}]

DEBUG - Loading entity: [com.XXX.customer.entity.CustomerContactAddress#component[sequence,customerNumber,companyNumber]{sequence=0, companyNumber=01, customerNumber=  103048}]

DEBUG - select customerco0_.MYSEQN as MYSEQN6_0_, customerco0_.MYADDC as MYADDC6_0_, customerco0_.MYCONO as MYCONO6_0_, customerco0_.MYCITY as MYCITY6_0_, customerco0_.MYNQTX as MYNQTX6_0_, customerco0_.MYADD1 as MYADD6_6_0_, customerco0_.MYADD2 as MYADD7_6_0_, customerco0_.MYH2CD as MYH8_6_0_, customerco0_.MYZIPC as MYZIPC6_0_ from MKP70 customerco0_ where customerco0_.MYSEQN=? and customerco0_.MYADDC=? and customerco0_.MYCONO=?

DEBUG - Done entity load

On Thursday, September 25, 2014 9:48:26 AM UTC-5, timowest wrote:

Hi.

You should upgrade to a newer version, if possible.

Timo

On Thu, Sep 25, 2014 at 5:47 PM, Timo Westkämper <timo.we...@gmail.com> wrote:

Hi.

You verified by looking at the SQL that the joins are not used?

Timo

[timowest]

Hi.

According to the Hibernate documentation this should work http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-joins

So this might be a Hibernate bug.

Br,

Timo