Comments (12)
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.
from jinq.
I'll try to take a look at this over the weekend.
from jinq.
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.
from jinq.
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'
from jinq.
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'
from jinq.
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
from jinq.
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.
from jinq.
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
from jinq.
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.
from jinq.
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
from jinq.
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.
from jinq.
Wow, thank you! That was fast!
from jinq.
Related Issues (20)
- toList() commit transaction ignoring JPA-Transactional HOT 3
- Question... How does Jinq guesses the operators in Lambdas HOT 1
- Anonymous class as a custom tuple HOT 6
- Could not translate code to a query HOT 6
- A Stream that returns a list of UUIDs is not serializable HOT 10
- fail with registerCustomSqlFunction HOT 5
- How to get the total count after group by? HOT 2
- How to get the number of groups HOT 1
- How to get the number of groups by issuing a separate query after group by? HOT 3
- Support java v21 HOT 5
- can't leftOuterJoin support subquery? HOT 2
- [BUG]: The concatenation of strings was not as expected HOT 4
- [Feature] join supports on condition HOT 1
- [Feature] Add support for additional collection libraries HOT 3
- Where filtering after select with projection gives error HOT 2
- Move from javax.persistence to jakarta.persistence. HOT 6
- Java 13 supported? HOT 2
- Compare int, datetime, bool to String HOT 1
- Could not analyze lambda code HOT 1
- jdk11 support HOT 11
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from jinq.