/*SELECT contact.* t2.t1.history.*
FROM contact
INNER JOIN
(SELECT room_contact.contact t1.history.*
FROM
(SELECT room.Id, history.*
FROM room
INNER JOIN history
ON room.room_id = history.to_id
AND history.msg_key IN
(SELECT MAX(msg_key)
FROM history
GROUP BY to_id)) t1
INNER JOIN room_contact
ON t1.Id = room_contact.room) t2
ON contact.Id = t2.contact*/
public static List<Pair<List<Contact>, MessageHistory>> getSessionList() {
StringBuilder sqlBuilder0 = new StringBuilder();
sqlBuilder0.append(" SELECT MAX(msg_key) FROM history")
.append(" WHERE history.removed = 0 AND history.deleted = 0")
.append(" AND history.type <> 'READ_SECRET' AND history.type <> 'INFO'")
.append(" GROUP BY to_id");
Cursor cursor0 = SQLiteUtils.querySql(sqlBuilder0.toString());
SQLiteUtils.printCursor(cursor0);
// StringBuilder sqlBuilder1 = new StringBuilder();
// sqlBuilder1.append("SELECT room.Id, ")
// .append(SQLiteUtils.getColumNames4Select(MessageHistory.class))
// .append(" FROM room INNER JOIN history ON room.room_id = history.to_id")
// .append(" AND history.msg_key IN (SELECT MAX(msg_key) FROM history")
// .append(" WHERE history.removed = 0 AND history.deleted = 0")
// .append(" AND history.type <> 'READ_SECRET' AND history.type <> 'INFO'")
// .append(" GROUP BY to_id) GROUP BY history.to_id ")
// .append(" ORDER BY history.msg_key DESC, history.Id DESC");
// Cursor cursor1 = SQLiteUtils.querySql(sqlBuilder1.toString());
// SQLiteUtils.printCursor(cursor1);
StringBuilder sqlBuilder2 = new StringBuilder();
sqlBuilder2.append("SELECT room_contact.contact, ")
.append(SQLiteUtils.getColumNames4Select(MessageHistory.class, "t1"))
.append(" FROM (SELECT room.Id, ")
.append(SQLiteUtils.getColumNames4Select(MessageHistory.class))
.append(" FROM room INNER JOIN history ON room.room_id = history.to_id")
.append(" AND history.msg_key IN (SELECT MAX(msg_key) FROM history")
.append(" WHERE history.removed = 0 AND history.deleted = 0")
.append(" AND history.type <> 'READ_SECRET' AND history.type <> 'INFO'")
.append(" GROUP BY to_id) GROUP BY history.to_id ")
.append(" ORDER BY history.msg_key DESC, history.Id DESC)")
.append(" t1 LEFT JOIN room_contact")
.append(" ON t1.Id = room_contact.room");
Cursor cursor2 = SQLiteUtils.querySql(sqlBuilder2.toString());
SQLiteUtils.printCursor(cursor2);
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("SELECT ")
.append(SQLiteUtils.getColumNames4Select(Contact.class)).append(',')
.append(SQLiteUtils.getColumNames4Select(MessageHistory.class, "t2"))
.append(" FROM contact INNER JOIN (SELECT room_contact.contact, ")
.append(SQLiteUtils.getColumNames4Select(MessageHistory.class, "t1"))
.append(" FROM (SELECT room.Id, ")
.append(SQLiteUtils.getColumNames4Select(MessageHistory.class))
.append(" FROM room INNER JOIN history ON room.room_id = history.to_id")
.append(" AND history.msg_key IN (SELECT MAX(msg_key) FROM history")
.append(" WHERE history.removed = 0 AND history.deleted = 0")
.append(" AND history.type <> 'READ_SECRET' AND history.type <> 'INFO'")
.append(" GROUP BY to_id) GROUP BY history.to_id ")
.append(" ORDER BY history.msg_key DESC, history.Id DESC)")
.append(" t1 INNER JOIN room_contact")
.append(" ON t1.Id = room_contact.room) t2 ON contact.Id = t2.contact");
Cursor cursor = SQLiteUtils.querySql(sqlBuilder.toString());
SQLiteUtils.printCursor(cursor);
List<Contact> contacts = SQLiteUtils.processCursor(Contact.class, cursor, null);
List<MessageHistory> messages = SQLiteUtils.processCursor(MessageHistory.class, cursor, "t2");
List<Pair<List<Contact>, MessageHistory>> pairs = new ArrayList<Pair<List<Contact>, MessageHistory>>();
List<MessageHistory> mList = new ArrayList<MessageHistory>();
int count = contacts.size();
for (int i = 0; i < count; i++) {
Contact contact = contacts.get(i);
MessageHistory msgHistory = messages.get(i);
if (mList.contains(msgHistory)) {
int index = mList.indexOf(msgHistory);
Pair<List<Contact>, MessageHistory> pair = pairs.get(index);
List<Contact> cList = pair.first;
cList.add(contact);
} else {
List<Contact> cList = new ArrayList<Contact>();
cList.add(contact);
mList.add(msgHistory);
pairs.add(new Pair<List<Contact>, MessageHistory>(cList, msgHistory));
}
}
return pairs;
}