I have table product_spec_entry with following columns:
- product_spec_id
- commodity_spec_id
for one product_spec_id may be several commodity_spec_id, for example:
|product_spec_id | commodity_spec_id|
|----------------|------------------|
|1683 |1681 |
|1692 |1693 |
|1692 |1681 |
|1692 |1687 |
|1692 |1864 |
|1860 |1681 |
|1868 |1681 |
|1868 |1864 |
I want get all product_spec_id that have all commodity_spec_id are passed as parameter.
I wrote next query:
SELECT ps.product_spec_id, commodities
FROM (
SELECT
product_spec_id,
array_agg(commodity_spec_id) AS commodities
FROM system.product_spec_entry
GROUP BY product_spec_id) ps
WHERE Cast(ARRAY [1681, 1864] as BIGINT[]) <@ Cast(ps.commodities as BIGINT[]);
It's work fine, and return expected result:
product_spec_id = 1692, 1868
I try use this query for JPA native query:
String query = "SELECT ps.product_spec_id " +
"FROM ( " +
" SELECT " +
" product_spec_id, " +
" array_agg(commodity_spec_id) AS commodities " +
" FROM system.product_spec_entry " +
" GROUP BY product_spec_id) ps " +
"WHERE CAST(ARRAY[:commoditySpecIds] AS BIGINT[]) <@ CAST(ps.commodities AS BIGINT[])";
List<Long> commoditySpecsIds = commoditySpecs.stream().map(Spec::getId).collect(Collectors.toList());
List<BigInteger> productSpecIds = em.createNativeQuery(query).setParameter("commoditySpecIds", commoditySpecsIds)
.getResultList();
It does not work because I get array of record (ARRAY[(1692, 1868)]
) instead array of bigint (ARRAY[1692, 1868]
)
How I should bind array param to my query? May be I can use more simple query for it.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…