leftOuterJoin JoinWithSource support

[b0c1]

Hi! Possible to create leftOuterJoin with JoinWithSource implementation? I have 3 table. I18NMaster <-> I18N <-> Language

I want to select all I18NMaster field in all Language, and where exists, I want the I18N localized text...

b0c1

[my2iu]

I'll try to take a look at this over the weekend.

[my2iu]

I'm not sure if I completely understand your request or not.

The reason for having a leftOuterJoin with JoinWithSource implementation is if you want to perform a left outer join between two entities that don't have any associations between them. (If there was a proper association between the two entities, then you could simply use the regular leftOuterJoin).

All Jinq queries are translated to JPQL to be executed. Unfortunately, I don't think normal JPQL supports letting you perform a LEFT OUTER JOIN between two entities that aren't related. So Jinq wouldn't be able to translate a leftOuterJoin with JoinWithSource into anything useful.

You could add an association between the I18N localized text and the Language, and then you could perform a normal left outer join. But without an association, I don't think there's anything Jinq can do due to the limitations of the underlying JPQL query language.

[b0c1]

Ummm... I think JPA 2.1 support ON clause and will support to join entities. (I not tested I just check the spec), but in this case I can't do the following thing with Jinq/JPQL just with native query?

        JPAJinqStream<Tuple3<I18NMaster, I18N, I18N>> query = streams.streamAll(em, I18NMaster.class)
                .leftOuterJoin(m -> JinqStream.from(m.getI18NList()).where(i -> i.getLanguageCode().equals(from)))
                .leftOuterJoin(mp -> JinqStream.from(mp.getOne().getI18NList()).where(i -> i.getLanguageCode().equals(to)))
                .select(x -> new Tuple3<>(x.getOne().getOne(), x.getOne().getTwo(), x.getTwo()));

OR with native sql:

SELECT
    a.CODE,
    a.DESCRIPTION,
    a.MAX_LENGTH,
    a.MOBILE,
    a.DESKTOP,
    a.ADMIN,
    a.TEMPLATE,
    b.LANGUAGE_CODE,
    b.LOCAL_TEXT,
    b.LOCAL_TEMPLATE,
    c.LANGUAGE_CODE,
    c.LOCAL_TEXT,
    c.LOCAL_TEMPLATE
FROM
    I18N_MASTER a
    LEFT JOIN I18N b
    ON b.MASTER_CODE=a.CODE AND b.LANGUAGE_CODE='en'
    LEFT JOIN I18N c
    ON c.MASTER_CODE=a.CODE AND c.LANGUAGE_CODE='hu'

[my2iu]

I'll look at the spec to double-check, but I initially couldn't find any mention of support for that feature in Hibernate, so I didn't think it actually is available.

Looking at your code, I think you actually want something like this:

JPAJinqStream<Tuple3<I18NMaster, I18N, I18N>> query = 
   streams.streamAll(em, I18NMaster.class)
      .leftOuterJoinList(m -> m.getI18NList())
      .where(mp -> mp.getTwo().getLanguageCode().equals(from)))
      .leftOuterJoinList(mp -> mp.getOne().getI18NList())
      .where(x -> x.getOne().getTwo().getLanguageCode().equals(to))
      .select(x -> new Tuple3<>(x.getOne().getOne(), x.getOne().getTwo(), x.getTwo()));

or the equivalent with SQL

SELECT
    a.CODE,
    a.DESCRIPTION,
    a.MAX_LENGTH,
    a.MOBILE,
    a.DESKTOP,
    a.ADMIN,
    a.TEMPLATE,
    b.LANGUAGE_CODE,
    b.LOCAL_TEXT,
    b.LOCAL_TEMPLATE,
    c.LANGUAGE_CODE,
    c.LOCAL_TEXT,
    c.LOCAL_TEMPLATE
FROM
    I18N_MASTER a
    LEFT JOIN I18N b ON b.MASTER_CODE=a.CODE 
    LEFT JOIN I18N c ON c.MASTER_CODE=a.CODE 
WHERE
   b.LANGUAGE_CODE='en'
   AND c.LANGUAGE_CODE='hu'

[b0c1]

Your result is wrong... my result will return the lines where the i18n fields are empty (for example doesn 't have 'hu' localization) your only return where all localized content is exists

[my2iu]

Can you just test for NULL in the WHERE clause too?

JPAJinqStream<Tuple3<I18NMaster, I18N, I18N>> query = 
   streams.streamAll(em, I18NMaster.class)
      .leftOuterJoinList(m -> m.getI18NList())
      .where(mp -> mp.getTwo().getLanguageCode().equals(from)) || mp.getTwo() == null)
      .leftOuterJoinList(mp -> mp.getOne().getI18NList())
      .where(x -> x.getOne().getTwo().getLanguageCode().equals(to) || x.getOne().getTwo() == null)
      .select(x -> new Tuple3<>(x.getOne().getOne(), x.getOne().getTwo(), x.getTwo()));

I don't have your database schema, so I can't really tell if this will return the results you want or not. Obviously, if you can get the native SQL to work, then you can always just drop down to native SQL using your JPA provider.

[oberien]

Coming back to the originial question I currently have kind of the same problem. My problem is, that during development of a Plugin I have no possibility to influence Entities. Going with this example, I have the following relations: I18nMaster ← I18n → Language. So I18n has a property @ManyToOne master and long languageId (without a direct relation). As I don't have a relation from I18nMaster to I18n, I cannot use leftOuterJoin(Join), as I need leftOuterJoin(JoinWithSource) to be able to start streaming I18n.

The Query would then look like:

JinqStream<Pair<I18nMaster, I18n>> stream =
streams.streamAll(I18nMaster.class)
.leftOuterJoin((m, em) -> em.stream(I18n.class).where(i -> m.getId() == i.getMaster().getId())

For a 1:1 or M:1 association between I18nMaster and I18n, this could also be done in a subquery in select(SelectWithSource), but for 1:N or M:N associations, this is only possible with leftOuterJoin.

Even if I use a subquery in select, if i want to use different columns of I18n later, I need to create a new subquery each time, instead of just selecting from the first one.

A possible workaround would be to be able to return JinqStream<Pair<I18nMaster, JinqStream<I18n>>>:

JinqStream<Pair<I18nMaster, I18n>> stream =
streams.streamAll(I18nMaster.class)
.select((e, em) -> new Pair(e, em.stream(I18n.class).where(i -> i.master.getId() == e.getId())

But currently this results in

org.jinq.jpa.transform.QueryTransformException: ch.epfl.labos.iu.orm.queryll2.symbolic.TypedValueVisitorException: Unhandled symbolic execution operation: @arg1.stream(I18n.class)

From my understanding, JPQL ON should allow leftOuterJoin(JoinWithSource) on different entities, without them being mapped on the entity-level (but rather on the database-level). leftOuterJoin(JoinWithSource) could return an OuterJoinJinqStream<Pair<T, U>> extends JinqStream<Pair<T, U>> supporting the method JinqStream<Pair<T, U>> OuterJoinJinqStream::on(Select<T, U> select), which could then be translated into the check for the ON part.

The following query

JinqStream<Pair<I18nMaster, I18n>> stream =
streams.streamAll(I18nMaster.class)
.leftOuterJoin((m, em) -> em.stream(I18n.class))
.on(i -> m.getId() == i.getMaster().getId())

could be translated into

SELECT A, B
FROM I18nMaster A
LEFT JOIN I18n B ON A.Id == B.MasterId

[my2iu]

Sorry for the delay. I've been busy with trying to get another project off the ground, so this issue fell off my radar.

After playing with the LEFT JOIN...ON stuff, I found that it doesn't work properly in the 4.3 series of Hibernate that I was developing on, but it does seem to work in the current 5.1 version of Hibernate. LEFT JOIN...ON works fine on EclipseLink. I'm busy tomorrow, and deploying anything to Maven takes a day, so I'll try to get this feature in for the weekend.

[oberien]

Thank you for the fast response.

Just for interest, how would the following query be translated:

JinqStream<Pair<I18nMaster, I18n>> stream =
streams.streamAll(I18nMaster.class)
.leftOuterJoin((m, em) -> em.stream(I18n.class).where(m.getId() == i.getMaster().getId()))

Would it be

SELECT A, B
FROM I18nMaster A
LEFT JOIN (SELECT * FROM I18n C WHERE A.Id == C.MasterId) as B

[my2iu]

Well, the leftOuterJoin() method doesn't support using a source, so you can't do

.leftOuterJoin((m, em) -> ...

at the moment. Even then, I'm not sure if the code would work because I found that most JPA providers didn't handle subqueries in the FROM section very well. I think support was so bad that I might have even disabled it. That's why they have the LEFT OUTER JOIN ... ON ... syntax, so that JPA wouldn't need to handle subqueries inside the FROM.

I'll probably have a syntax like this:

JinqStream<Pair<I18nMaster, I18n>> stream =
streams.streamAll(I18nMaster.class)
.leftOuterJoin((m, em) -> em.stream(I18n.class), (a, b) -> a.getId() == b.getId())

or something like that. I'll have to see when I code it up tomorrow.

[my2iu]

Version 1.8.11 with support for leftOuterJoin() using a source to generate LEFT OUTER JOIN...ON queries is now available in Maven Central. Later on Saturday, I will write up some documentation and upload that version to the Jinq website as well.

[oberien]

Wow, thank you! That was fast!