gistfile1.txt

String sql =
                "SELECT " +
                    "chats._id, chats.title, " +
                    "display_names_view.first_name, display_names_view.last_name, friends.avatar_thumb, " +
                    "chats.last_create_operation_time, " +
                    "(SELECT chat_messages.message FROM chat_messages WHERE chat_messages.chat_local_id = chats._id ORDER BY chat_messages._id DESC LIMIT 1) AS last_message, " +
                    "(SELECT COUNT(1) " +
                        "FROM chat_messages " +
                        "WHERE chat_messages.chat_local_id = chats._id AND chat_messages.creator_id <> ? AND chat_messages.status = ?" +
                    ") AS count_unread," +
                    "(SELECT group_concat(friends.private_user_id, '\n') " +
                        "FROM friends " +
                        "WHERE friends.private_user_id IN " +
                            "(SELECT chat_participants.private_user_id FROM chat_participants WHERE chat_participants.chat_local_id = chats._id) " +
                    ") AS participants_ids, " +
                    "(SELECT group_concat(friends.avatar_thumb, '\n') " +
                        "FROM friends " +
                        "WHERE friends.private_user_id IN " +
                            "(SELECT chat_participants.private_user_id FROM chat_participants WHERE chat_participants.chat_local_id = chats._id) " +
                    ") AS participants_avatars," +
                    "(SELECT group_concat(CASE WHEN display_names_view.first_name = '' THEN ' ' ELSE display_names_view.first_name END, '\n') " +
                        "FROM display_names_view " +
                        "WHERE display_names_view.private_user_id IN " +
                            "(SELECT chat_participants.private_user_id FROM chat_participants WHERE chat_participants.chat_local_id = chats._id AND chat_participants.private_user_id <> ?) " +
                    ") AS participants_first_names, " +
                    "(SELECT group_concat(CASE WHEN display_names_view.last_name = '' THEN ' ' ELSE display_names_view.last_name END, '\n') " +
                        "FROM display_names_view " +
                        "WHERE display_names_view.private_user_id IN " +
                            "(SELECT chat_participants.private_user_id FROM chat_participants WHERE chat_participants.chat_local_id = chats._id AND chat_participants.private_user_id <> ?) " +
                    ") AS participants_last_names " +
                "FROM chats " +
                "LEFT OUTER JOIN friends ON friends.private_user_id = chats.destination_id " +
                "LEFT OUTER JOIN display_names_view ON display_names_view.private_user_id = friends.private_user_id " +
                "WHERE chats.is_deleted = 0 " +
                "ORDER BY chats.last_create_operation_time DESC";