23tony
Well-Known Member
I'm trying to see if there is a better way to do this than I'm doing. What I have works, but I have to make several DB queries in a loop to get what I want. I'm wondering if there is a better approach that can do it all in a single query. I've tried some things I found on SO and such, but while they worked for MySQL, they use a syntax SQLite doesn't appear to support.
I have a table "messages" with the following data:
id, incoming, contact_id, and seen are int, the rest are text
The goal is to get only the last sent message from each contact. For that, I am using the following two functions:
As I said, this works perfectly - does exactly what I want. I'm just trying to see if there may be a more efficient approach.
I have a table "messages" with the following data:
Code:
+----+----------+------------+-----------------+---------------------+---------------------+------+
| id | incoming | contact_id | contact_name | message | sent | seen |
+----+----------+------------+-----------------+---------------------+---------------------+------+
| 1 | 0 | 30 | Cap | Hi there, Cap | 2019-01-10 15:07:43 | 0 |
| 2 | 1 | 30 | Cap | Hey, what's up? | 2019-01-10 15:27:23 | 0 |
| 3 | 0 | 43 | SorcerorSupreme | Strange night, huh? | 2019-01-10 16:42:18 | 0 |
| 4 | 1 | 37 | IAMGROOT | I am Groot! | 2019-01-13 09:24:55 | 0 |
| 5 | 0 | 30 | Cap | Nothing | 2019-01-14 02:53:28 | 0 |
+----+----------+------------+-----------------+---------------------+---------------------+------+The goal is to get only the last sent message from each contact. For that, I am using the following two functions:
Code:
public ArrayList<Map<String,Object>> loadConversationList() {
ArrayList<Map<String,Object>> list = new ArrayList();
SQLiteDatabase db = getReadableDatabase();
Log.d(TAG, "Getting list of last messages");
String sql = "SELECT contact_id, max(sent) AS td FROM " + MESSAGE_TABLE + " GROUP BY contact_id ORDER BY td ASC";
Cursor res = db.rawQuery(sql, null);
Log.d(TAG, "Made call");
res.moveToFirst();
Log.d(TAG, "Set cursor");
while (!res.isAfterLast()) {
int contact_id = res.getInt(res.getColumnIndex("contact_id"));
String sent = res.getString(res.getColumnIndex("td"));
Log.d(TAG, "Getting info for "+contact_id+":"+sent);
Map<String, Object> message = getMessage(contact_id, sent);
list.add(message);
res.moveToNext();
}
Log.d(TAG, list.toString());
return list;
}
private Map<String,Object> getMessage(int contact_id, String sent) {
Map<String,Object> message = new HashMap();
SQLiteDatabase db = getReadableDatabase();
String sql = "SELECT * FROM " + MESSAGE_TABLE + " WHERE contact_id=" + contact_id + " AND sent='" + sent + "'";
Cursor res = db.rawQuery(sql, null);
res.moveToFirst();
while (!res.isAfterLast()) {
message.put("mid", res.getString(res.getColumnIndex("id")));
message.put("incoming", res.getString(res.getColumnIndex("incoming")));
message.put("contact_id", res.getString(res.getColumnIndex("contact_id")));
message.put("contact_name", res.getString(res.getColumnIndex("contact_name")));
message.put("message", res.getString(res.getColumnIndex("message")));
message.put("sent", res.getString(res.getColumnIndex("sent")));
res.moveToNext();
}
return message;
}As I said, this works perfectly - does exactly what I want. I'm just trying to see if there may be a more efficient approach.
