async def load_messages(account_id, type, text=None, offset=0, limit=None): if text is None: text_filter = '' else: text = '%{}%'.format(text) text_filter = 'AND m.body ILIKE %(text)s' limit_filter = '' if limit is None else 'LIMIT %(limit)s' if type.is_SENDER: type_filter = 'AND m.sender=%(account_id)s' else: type_filter = 'AND m.sender<>%(account_id)s' sql_count = '''SELECT count(*) as total FROM visibilities AS v JOIN messages AS m ON m.id = v.message WHERE v.account=%(account_id)s AND v.visible {type_filter} {text_filter}'''.format(type_filter=type_filter, text_filter=text_filter) sql = '''SELECT m.id AS id, m.sender AS sender, m.recipients AS recipients, m.body AS body, m.created_at AS created_at FROM visibilities AS v JOIN messages AS m ON m.id = v.message WHERE v.account=%(account_id)s AND v.visible {type_filter} {text_filter} ORDER BY m.created_at DESC {limit_filter} OFFSET %(offset)s'''.format(type_filter=type_filter, text_filter=text_filter, limit_filter=limit_filter) arguments = { 'account_id': account_id, 'type': type.value, 'offset': offset, 'limit': limit, 'text': text } tasks = [db.sql(sql_count, arguments), db.sql(sql, arguments)] count_results, messages_result = await asyncio.gather(*tasks) return count_results[0]['total'], [ message_from_row(row) for row in messages_result ]
async def load_conversation(account_id, partner_id, text=None, offset=0, limit=None): if text is None: text_filter = '' else: text = '%{}%'.format(text) text_filter = 'AND m.body ILIKE %(text)s' limit_filter = '' if limit is None else 'LIMIT %(limit)s' account_1_id, account_2_id = min(account_id, partner_id), max(account_id, partner_id) sql_count = '''SELECT count(*) as total FROM messages AS m JOIN visibilities AS v ON m.id = v.message JOIN conversations AS c ON m.id = c.message WHERE v.visible AND v.account=%(account_id)s AND c.account_1=%(account_1_id)s AND c.account_2=%(account_2_id)s {text_filter}'''.format(text_filter=text_filter) sql = '''SELECT m.id AS id, m.sender AS sender, m.recipients AS recipients, m.body AS body, m.created_at AS created_at FROM messages AS m JOIN visibilities AS v ON m.id = v.message JOIN conversations AS c ON m.id = c.message WHERE v.visible AND v.account=%(account_id)s AND c.account_1=%(account_1_id)s AND c.account_2=%(account_2_id)s {text_filter} ORDER BY m.created_at DESC {limit_filter} OFFSET %(offset)s'''.format(text_filter=text_filter, limit_filter=limit_filter) arguments = {'account_1_id': account_1_id, 'account_2_id': account_2_id, 'account_id': account_id, 'text': text, 'offset': offset, 'limit': limit} tasks = [db.sql(sql_count, arguments), db.sql(sql, arguments)] count_results, messages_result = await asyncio.gather(*tasks) return count_results[0]['total'], [message_from_row(row) for row in messages_result]
async def load_messages(account_id, type, text=None, offset=0, limit=None): if text is None: text_filter = '' else: text = '%{}%'.format(text) text_filter = 'AND m.body ILIKE %(text)s' limit_filter = '' if limit is None else 'LIMIT %(limit)s' if type.is_SENDER: type_filter = 'AND m.sender=%(account_id)s' else: type_filter = 'AND m.sender<>%(account_id)s' sql_count = '''SELECT count(*) as total FROM visibilities AS v JOIN messages AS m ON m.id = v.message WHERE v.account=%(account_id)s AND v.visible {type_filter} {text_filter}'''.format(type_filter=type_filter, text_filter=text_filter) sql = '''SELECT m.id AS id, m.sender AS sender, m.recipients AS recipients, m.body AS body, m.created_at AS created_at FROM visibilities AS v JOIN messages AS m ON m.id = v.message WHERE v.account=%(account_id)s AND v.visible {type_filter} {text_filter} ORDER BY m.created_at DESC {limit_filter} OFFSET %(offset)s'''.format(type_filter=type_filter, text_filter=text_filter, limit_filter=limit_filter) arguments = {'account_id': account_id, 'type': type.value, 'offset': offset, 'limit': limit, 'text': text} tasks = [db.sql(sql_count, arguments), db.sql(sql, arguments)] count_results, messages_result = await asyncio.gather(*tasks) return count_results[0]['total'], [message_from_row(row) for row in messages_result]
async def load_conversation(account_id, partner_id, text=None, offset=0, limit=None): if text is None: text_filter = '' else: text = '%{}%'.format(text) text_filter = 'AND m.body ILIKE %(text)s' limit_filter = '' if limit is None else 'LIMIT %(limit)s' account_1_id, account_2_id = min(account_id, partner_id), max(account_id, partner_id) sql_count = '''SELECT count(*) as total FROM messages AS m JOIN visibilities AS v ON m.id = v.message JOIN conversations AS c ON m.id = c.message WHERE v.visible AND v.account=%(account_id)s AND c.account_1=%(account_1_id)s AND c.account_2=%(account_2_id)s {text_filter}'''.format(text_filter=text_filter) sql = '''SELECT m.id AS id, m.sender AS sender, m.recipients AS recipients, m.body AS body, m.created_at AS created_at, v.visible as visible FROM messages AS m JOIN visibilities AS v ON m.id = v.message JOIN conversations AS c ON m.id = c.message WHERE v.visible AND v.account=%(account_id)s AND c.account_1=%(account_1_id)s AND c.account_2=%(account_2_id)s {text_filter} ORDER BY m.created_at DESC {limit_filter} OFFSET %(offset)s'''.format(text_filter=text_filter, limit_filter=limit_filter) arguments = { 'account_1_id': account_1_id, 'account_2_id': account_2_id, 'account_id': account_id, 'text': text, 'offset': offset, 'limit': limit } tasks = [db.sql(sql_count, arguments), db.sql(sql, arguments)] count_results, messages_result = await asyncio.gather(*tasks) return count_results[0]['total'], [ message_from_row(row) for row in messages_result ]