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
]