The original functional MySQL query that lists all and only the providers that have all the listed tags:
SELECT * FROM provider
INNER JOIN provider_tag
ON provider_tag.provider_id = provider.id AND provider_tag.tag_id in (1, 2)
GROUP BY (provider.id)
HAVING COUNT(*) = 2
Translating to MySQLQuery in Querydsl is straightforward...
MySQLQuery query = new MySQLQuery(conn, dialect);
List<Integer> tagIds = ...;
query.from(provider)
.innerJoin(provider_tag)
.on(providerTag.providerId.eq(provider.id), providerTag.tagId.in(tagIds))
.groupBy(provider.id)
.having(???);
... except for the condition in having
.
How do I add COUNT(*)
to the query?
EDIT after Timo's 1st correction proposal:
So, the query looks like this:
SearchResults<Tuple> result = query.from(provider)
.innerJoin(providerTag)
.on(providerTag.providerId.eq(provider.id), providerTag.tagId.in(tagIds))
.groupBy(provider.id)
.having(Wildcard.count.eq((long) tagIds.size()))
.listResults(
provider.id,
provider.name);
However, this causes an SQLException Illegal operation on empty result set
, if the result set is empty.
My other queries that return an empty result set do not cause exceptions, so I guess I should not need to catch the exception, but there is a problem that should be fixed?
The generated MySQL works perfectly (returns 0 rows), so the problem is not there.
EDIT 2:
The problem was in groupBy()
. This seems to work, if one applies the correction shown in the issue.
The querydsl equivalent for COUNT(*)
is Wildcard.count
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With