Mixing
Intersect in JpaRepository
I have SQL query like below. I'd like to take the common product_id,authorization_ids for certain merchantIds.
In this example merchant 20000 has product1,authorization1 and product2, authorization1 whereas the other merchants only have product1, authorization1.
SELECT product_id,authorization_id FROM ACQ_MERCHANT_PRODUCT_AUTH WHERE merchant_Id = 20000
Intersect
SELECT product_id,authorization_id FROM ACQ_MERCHANT_PRODUCT_AUTH WHERE merchant_Id = 20001
Intersect
SELECT product_id,authorization_id FROM ACQ_MERCHANT_PRODUCT_AUTH WHERE merchant_Id = 20002
The query above gives the right result which is product1, authorization1.
I am trying to implement that in jpa:
@Repository
public interface MerchantProductAuthorizationRepository extends
JpaRepository<MerchantProductAuthorizationEntity, Long> {
@Query("SELECT new com.ykb.acq.application.sweep.util.ProductAuthMap(m.product.id, m.authorization.id) FROM MerchantProductAuthorizationEntity m WHERE m.merchantId IN (:merchants) GROUP BY m.product.id, m.authorization.id")
List<ProductAuthMap> findIntersactionOfProductAndAuthorizations(@Param("merchants") Set<Long> merchants);
}
But this gives me more lines than the intersect. ( product1, authorization1 and product2, authorization2)
How do I get the same result?
spring spring-data-jpa jpql
edited Nov 22 '18 at 7:52
Billy Frost
1,799198
asked Nov 21 '18 at 20:38
EgeEge
4161929
add a comment |
I have SQL query like below. I'd like to take the common product_id,authorization_ids for certain merchantIds.
In this example merchant 20000 has product1,authorization1 and product2, authorization1 whereas the other merchants only have product1, authorization1.
SELECT product_id,authorization_id FROM ACQ_MERCHANT_PRODUCT_AUTH WHERE merchant_Id = 20000
Intersect
SELECT product_id,authorization_id FROM ACQ_MERCHANT_PRODUCT_AUTH WHERE merchant_Id = 20001
Intersect
SELECT product_id,authorization_id FROM ACQ_MERCHANT_PRODUCT_AUTH WHERE merchant_Id = 20002
The query above gives the right result which is product1, authorization1.
I am trying to implement that in jpa:
@Repository
public interface MerchantProductAuthorizationRepository extends
JpaRepository<MerchantProductAuthorizationEntity, Long> {
@Query("SELECT new com.ykb.acq.application.sweep.util.ProductAuthMap(m.product.id, m.authorization.id) FROM MerchantProductAuthorizationEntity m WHERE m.merchantId IN (:merchants) GROUP BY m.product.id, m.authorization.id")
List<ProductAuthMap> findIntersactionOfProductAndAuthorizations(@Param("merchants") Set<Long> merchants);
}
But this gives me more lines than the intersect. ( product1, authorization1 and product2, authorization2)
How do I get the same result?
spring spring-data-jpa jpql
edited Nov 22 '18 at 7:52
Billy Frost
1,799198
asked Nov 21 '18 at 20:38
EgeEge
4161929
add a comment |
I have SQL query like below. I'd like to take the common product_id,authorization_ids for certain merchantIds.
In this example merchant 20000 has product1,authorization1 and product2, authorization1 whereas the other merchants only have product1, authorization1.
SELECT product_id,authorization_id FROM ACQ_MERCHANT_PRODUCT_AUTH WHERE merchant_Id = 20000
Intersect
SELECT product_id,authorization_id FROM ACQ_MERCHANT_PRODUCT_AUTH WHERE merchant_Id = 20001
Intersect
SELECT product_id,authorization_id FROM ACQ_MERCHANT_PRODUCT_AUTH WHERE merchant_Id = 20002
The query above gives the right result which is product1, authorization1.
I am trying to implement that in jpa:
@Repository
public interface MerchantProductAuthorizationRepository extends
JpaRepository<MerchantProductAuthorizationEntity, Long> {
@Query("SELECT new com.ykb.acq.application.sweep.util.ProductAuthMap(m.product.id, m.authorization.id) FROM MerchantProductAuthorizationEntity m WHERE m.merchantId IN (:merchants) GROUP BY m.product.id, m.authorization.id")
List<ProductAuthMap> findIntersactionOfProductAndAuthorizations(@Param("merchants") Set<Long> merchants);
}
But this gives me more lines than the intersect. ( product1, authorization1 and product2, authorization2)
How do I get the same result?
spring spring-data-jpa jpql
edited Nov 22 '18 at 7:52
Billy Frost
1,799198
asked Nov 21 '18 at 20:38
EgeEge
4161929
I have SQL query like below. I'd like to take the common product_id,authorization_ids for certain merchantIds.
In this example merchant 20000 has product1,authorization1 and product2, authorization1 whereas the other merchants only have product1, authorization1.
SELECT product_id,authorization_id FROM ACQ_MERCHANT_PRODUCT_AUTH WHERE merchant_Id = 20000
Intersect
SELECT product_id,authorization_id FROM ACQ_MERCHANT_PRODUCT_AUTH WHERE merchant_Id = 20001
Intersect
SELECT product_id,authorization_id FROM ACQ_MERCHANT_PRODUCT_AUTH WHERE merchant_Id = 20002
The query above gives the right result which is product1, authorization1.
I am trying to implement that in jpa:
@Repository
public interface MerchantProductAuthorizationRepository extends
JpaRepository<MerchantProductAuthorizationEntity, Long> {
@Query("SELECT new com.ykb.acq.application.sweep.util.ProductAuthMap(m.product.id, m.authorization.id) FROM MerchantProductAuthorizationEntity m WHERE m.merchantId IN (:merchants) GROUP BY m.product.id, m.authorization.id")
List<ProductAuthMap> findIntersactionOfProductAndAuthorizations(@Param("merchants") Set<Long> merchants);
}
But this gives me more lines than the intersect. ( product1, authorization1 and product2, authorization2)
How do I get the same result?
spring spring-data-jpa jpql
spring spring-data-jpa jpql
edited Nov 22 '18 at 7:52
Billy Frost
1,799198
asked Nov 21 '18 at 20:38
EgeEge
4161929
edited Nov 22 '18 at 7:52
Billy Frost
1,799198
asked Nov 21 '18 at 20:38
EgeEge
4161929
edited Nov 22 '18 at 7:52
Billy Frost
1,799198
edited Nov 22 '18 at 7:52
Billy Frost
1,799198
edited Nov 22 '18 at 7:52
Billy Frost
1,799198
1,799198
asked Nov 21 '18 at 20:38
EgeEge
4161929
asked Nov 21 '18 at 20:38
EgeEge
4161929
asked Nov 21 '18 at 20:38
EgeEge
4161929
4161929
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Your HQL not had intersect logic by merchantIds. In this, I count merchant_id groupped by (m.product.id, m.authorization.id) and if the count is equal the merchants count, then this pair(m.product.id, m.authorization.id) is in all merchant's Set.
SELECT new com.ykb.acq.application.sweep.util.ProductAuthMap(m.product.id, m.authorization.id) FROM MerchantProductAuthorizationEntity m WHERE m.merchantId IN (:merchants) GROUP BY m.product.id, m.authorization.id having count(distinct m.merchantId) =:merchantsCount
answered Nov 21 '18 at 21:22
László TóthLászló Tóth
765
This worked thanks :)
– Ege
Nov 22 '18 at 6:44
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420139%2fintersect-in-jparepository%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Your HQL not had intersect logic by merchantIds. In this, I count merchant_id groupped by (m.product.id, m.authorization.id) and if the count is equal the merchants count, then this pair(m.product.id, m.authorization.id) is in all merchant's Set.
SELECT new com.ykb.acq.application.sweep.util.ProductAuthMap(m.product.id, m.authorization.id) FROM MerchantProductAuthorizationEntity m WHERE m.merchantId IN (:merchants) GROUP BY m.product.id, m.authorization.id having count(distinct m.merchantId) =:merchantsCount
answered Nov 21 '18 at 21:22
László TóthLászló Tóth
765
This worked thanks :)
– Ege
Nov 22 '18 at 6:44
add a comment |
Your HQL not had intersect logic by merchantIds. In this, I count merchant_id groupped by (m.product.id, m.authorization.id) and if the count is equal the merchants count, then this pair(m.product.id, m.authorization.id) is in all merchant's Set.
SELECT new com.ykb.acq.application.sweep.util.ProductAuthMap(m.product.id, m.authorization.id) FROM MerchantProductAuthorizationEntity m WHERE m.merchantId IN (:merchants) GROUP BY m.product.id, m.authorization.id having count(distinct m.merchantId) =:merchantsCount
answered Nov 21 '18 at 21:22
László TóthLászló Tóth
765
This worked thanks :)
– Ege
Nov 22 '18 at 6:44
add a comment |
Your HQL not had intersect logic by merchantIds. In this, I count merchant_id groupped by (m.product.id, m.authorization.id) and if the count is equal the merchants count, then this pair(m.product.id, m.authorization.id) is in all merchant's Set.
SELECT new com.ykb.acq.application.sweep.util.ProductAuthMap(m.product.id, m.authorization.id) FROM MerchantProductAuthorizationEntity m WHERE m.merchantId IN (:merchants) GROUP BY m.product.id, m.authorization.id having count(distinct m.merchantId) =:merchantsCount
answered Nov 21 '18 at 21:22
László TóthLászló Tóth
765
Your HQL not had intersect logic by merchantIds. In this, I count merchant_id groupped by (m.product.id, m.authorization.id) and if the count is equal the merchants count, then this pair(m.product.id, m.authorization.id) is in all merchant's Set.
SELECT new com.ykb.acq.application.sweep.util.ProductAuthMap(m.product.id, m.authorization.id) FROM MerchantProductAuthorizationEntity m WHERE m.merchantId IN (:merchants) GROUP BY m.product.id, m.authorization.id having count(distinct m.merchantId) =:merchantsCount
answered Nov 21 '18 at 21:22
László TóthLászló Tóth
765
answered Nov 21 '18 at 21:22
László TóthLászló Tóth
765
answered Nov 21 '18 at 21:22
László TóthLászló Tóth
765
answered Nov 21 '18 at 21:22
László TóthLászló Tóth
765
765
This worked thanks :)
– Ege
Nov 22 '18 at 6:44
add a comment |
This worked thanks :)
– Ege
Nov 22 '18 at 6:44
This worked thanks :)
– Ege
Nov 22 '18 at 6:44
This worked thanks :)
– Ege
Nov 22 '18 at 6:44
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420139%2fintersect-in-jparepository%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
