Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Count(*) (asterisk) on Querydsl / MySQL?

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.

like image 817
masa Avatar asked Dec 26 '22 01:12

masa


1 Answers

The querydsl equivalent for COUNT(*) is Wildcard.count.

like image 149
Timo Westkämper Avatar answered Dec 28 '22 11:12

Timo Westkämper