Intersect in JpaRepository

0

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

share|improve this question

edited Nov 22 '18 at 7:52

Billy Frost

1,799198

asked Nov 21 '18 at 20:38

EgeEge

4161929

add a comment | 

0

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

share|improve this question

edited Nov 22 '18 at 7:52

Billy Frost

1,799198

asked Nov 21 '18 at 20:38

EgeEge

4161929

add a comment | 

0

0

0

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

share|improve this question

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

share|improve this question

edited Nov 22 '18 at 7:52

Billy Frost

1,799198

asked Nov 21 '18 at 20:38

EgeEge

4161929

share|improve this question

edited Nov 22 '18 at 7:52

Billy Frost

1,799198

asked Nov 21 '18 at 20:38

EgeEge

4161929

share|improve this question

share|improve this question

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

0

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

share|improve this answer

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
});


}
});

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.

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

0

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

share|improve this answer

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 | 

0

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

share|improve this answer

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 | 

0

0

0

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

share|improve this answer

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

share|improve this answer

answered Nov 21 '18 at 21:22

László TóthLászló Tóth

765

share|improve this answer

share|improve this answer

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.

draft saved

draft discarded

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.

draft saved

draft discarded

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

Name

Email

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

Name

Email

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

Name

Email

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

Name

Email

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

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here