CODEWITHSUNDEEP
androidsqliteormlite
shift66
shift66

Reputation: 11958

ORMLite search for rows with most common column

I'm using ORMLite for working with local database and have an issue with one custom query.

I need to get one row which's some column's value is most common in table.

E.g. I have a column text and I have 3 rows with value text0, 4 rows with value text1 and 5 rows with value text2. I need to get one of the rows which have value text2 of text column.

Can I do this with ORMLite's query builder or I should run a raw query?

This is raw sql query which works fine and returns array of strings.

        Dao<Case, Long> caseDao = getHelper().getCaseDao();
        GenericRawResults<String[]> cases = caseDao.queryRaw("SELECT * ," +
                " COUNT('name') AS 'name_occurrence'" +
                " FROM 'MEDICAL_CASE'" +
                " GROUP BY 'name'" +
                " ORDER BY 'name_occurrence' DESC" +
                " LIMIT 1", new String[]{});

After getting strings I have to create an instance of my model, set values and do my jon with that object.

But it's a bit ugly that's why I'm asking if it's possible with methods of ormlite.

Upvotes: 3

Views: 921

Answers (1)

Gray
Gray

Reputation: 116888

Can I do this with ORMLite's query builder or I should run a raw query?

Any results that aren't an entity need to be done through ORMLite's dao.queryRaw(...) methods. If you can do it with SQL then you can do it through those methods.

See: http://ormlite.com/docs/raw-queries

You can't use the standard entity mapper because you are doing a SELECT *, COUNT('name'). ORMLite wouldn't know what to do with the COUNT(...) field.

Upvotes: 0

Related Questions