cancel
Showing results for 
Search instead for 
Did you mean: 

How can I get PK from this query?

tpizarro93
Explorer
0 Kudos
337

I have this query:

SELECT {p.productId} FROM {PriceRow as p} GROUP BY {p:productId}, {p:store}, {p:price} HAVING count(*) > 1

But I don't need the productId, I need the PK. How can I get that? I tried to changed {p.productId} for {p.pk} but it doesn't works.

Accepted Solutions (1)

Accepted Solutions (1)

former_member634058
Active Participant

Hi,

If you are using productId instead of association of products in PriceRowModel, then you can try using the following query:

SELECT {prod.pk} from {product as prod} where {prod.code} IN ({{SELECT {p.productId} FROM {PriceRow as p} GROUP BY {p:productId}, {p:store}, {p:price} HAVING count(*) > 1}}

However, note that this will be a slow running query because of IN clause. Please use wisely.

Thanks.

tpizarro93
Explorer
0 Kudos

Thanks! It works

Answers (1)

Answers (1)

safin
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi,

If you want to query using hac, try this:

SELECT {p.pk} FROM {PriceRow as p} GROUP BY {p:pk}, {p:store}, {p:price} HAVING count(*) > 1

If you want to query in your code, try this:

String queryStr = "SELECT {pk} FROM {PriceRow} GROUP BY {pk}, {store}, {price} HAVING count(*) > 1";

FlexibleSearchQuery fsq = new FlexibleSearchQuery( queryStr );

Class[] resultTypesArray = { String.class };

fsq.setResultClassList( Arrays.asList( resultTypesArray ) );

SearchResult<String> result = getFlexibleSearchService().search( fsq );

List<String> pricePKList = result.getResult();

Hopefully it can help you a little