Sqlite function

[Alexey Shumankov]

Hi!

How to write query contains sqlite function like SUM or MAX with greenDAO?

[Alexey Shumankov]

It seems that there is no API support for this. One solution that I found is to write native query with session.getDatabase().query()

среда, 12 сентября 2012 г., 17:24:06 UTC+6 пользователь Alexey Shumankov написал:

[Alexey Shumankov]

Another problem is than i can't use LazyList because it's constuctor is not visible :(

среда, 12 сентября 2012 г., 17:24:06 UTC+6 пользователь Alexey Shumankov написал:

Hi!

[Markus Junginger]

You are right that there's currently no such thing in the API. What's your use case? Do you want to select a SUM/MAX? Or do you want to use it in the WHERE condition?

And yes, the constructor of LazyList is protected. This is made intentionally because it takes Cursor as parameter, which is has to conform to internal rules.

Markus

[Alexey Shumankov]

My use case is conversations - show list with only last messages from people. And I want to use MAX in select clause and group by user id.

So, what about making ListLazy constructor public? It would be very useful to have power of LazyList if there is no API support.

среда, 12 сентября 2012 г., 18:22:16 UTC+6 пользователь Markus Junginger написал:

[Markus Junginger]

Actually, you should check out WhereCondition.StringCondition. Using this you can pass any SQL fragments for the WHERE clause to the query builder.

[Alexey Shumankov]

How do i use it?

Example query:
SELECT MAX(id), userID FROM messages GROUP BY userID

среда, 12 сентября 2012 г., 20:59:50 UTC+6 пользователь Markus Junginger написал:

[Markus Junginger]

Sorry, I misread your question. What I wrote referred to the WHERE clause, of course.

There is no way around SQL for queries like yours: 

SELECT MAX(id), userID FROM messages GROUP BY userID

 

The rule is simple: use greenDAO for queries that return entities, and SQL for anything else.

Markus

[yiğit boyar]

if you are trying to fetch latest messages,

you can write like this:

SELECT * FROM messages WHERE id IN (SELECT MAX(id) FROM messages GROUP BY userid)

to use it with greenDao, get a query builder from MessagesDao and add "id IN (SELECT MAX(id) FROM messages GROUP BY userid)" as a where condition.

yigit

[Alexey Shumankov]

Great solution! (how could i forget about IN operator?) Thanks!

четверг, 13 сентября 2012 г., 14:48:07 UTC+6 пользователь yiğit boyar написал:

[Daniel D]

This is how I did it. Assume there is the Company entity and it has the numOfEmployees field: 

Cursor cursor = daoSession.getDatabase().rawQuery("SELECT SUM(" + Company Dao.Properties.NumOfEmployees.columnName + ") FROM " + Company Dao.TABLENAME, new String []{});
cursor.moveToFirst();
long result = cursor.getLong(0);

Note that you could hardcode the column and table names above, but this is way cleaner.


Regards,


Dan.