ormlite select count(*) as typeCount group by type

Question

I want to do something like this in OrmLite

SELECT *, COUNT(title) as titleCount from table1 group by title;

Is there any way to do this via QueryBuilder without the need for queryRaw?

Answer 1

The documentation states that the use of COUNT() and the like necessitates the use of selectRaw(). I hoped for a way around this - not having to write my SQL as strings is the main reason I chose to use ORMLite.

http://ormlite.com/docs/query-builder

selectRaw(String... columns):
Add raw columns or aggregate functions (COUNT, MAX, ...) to the query. This will turn the query into something only suitable for using as a raw query. This can be called multiple times to add more columns to select. See section Issuing Raw Queries.

Further information on the use of selectRaw() as I was attempting much the same thing:

Documentation states that if you use selectRaw() it will "turn the query into" one that is supposed to be called by queryRaw().

What it does not explain is that normally while multiple calls to selectColumns() or selectRaw() are valid (if you exclusively use one or the other), use of selectRaw() after selectColumns() has a 'hidden' side-effect of wiping out any selectColumns() you called previously.

I believe that the ORMLite documentation for selectRaw() would be improved by a note that its use is not intended to be mixed with selectColumns().

QueryBuilder<EmailMessage, String> qb = emailDao.queryBuilder();
qb.selectColumns("emailAddress"); // This column is not selected due to later use of selectRaw()!
qb.selectRaw("COUNT (emailAddress)");

ORMLite examples are not as plentiful as I'd like, so here is a complete example of something that works:

QueryBuilder<EmailMessage, String> qb = emailDao.queryBuilder();        
qb.selectRaw("emailAddress"); // This can also be done with a single call to selectRaw()
qb.selectRaw("COUNT (emailAddress)");
qb.groupBy("emailAddress");
GenericRawResults<String[]> rawResults = qb.queryRaw(); // Returns results with two columns

Answer 2

Is there any way to do this via QueryBuilder without the need for queryRaw(...)?

The short answer is no because ORMLite wouldn't know what to do with the extra count value. If you had a Table1 entity with a DAO definition, what field would the COUNT(title) go into? Raw queries give you the power to select various fields but then you need to process the results.

With the code right now (v5.1), you can define a custom RawRowMapper and then use the dao.getRawRowMapper() method to process the results for Table1 and tack on the titleCount field by hand.

I've got an idea how to accomplish this in a better way in ORMLite. I'll look into it.