async def get_chat_id_by_user_id_list(users_id: list) -> list:
async with config['db'].acquire() as conn:
query = text("""
SELECT chat_id
from (SELECT
cp.chat_id,
count(cp.chat_id) as found_members,
(select count(cps.id) as all_members
from chats_permission cps
where cp.chat_id = cps.chat_id
) as all_members
FROM chats
left join chats_permission cp on chats.id = cp.chat_id
where cp.user_id IN :users_id
group by cp.chat_id) as list
where list.found_members = :users_count
and list.all_members = :users_count;
""")
users_participated = list(
map(
lambda x: as_dict(dict(x)), await
conn.execute(query,
users_id=tuple(users_id),
users_count=len(users_id))))
return users_participated[0]['chat_id'] if len(
users_participated) == 1 else None
async def get_participated_by_user_id(user_id: int) -> list:
async with config['db'].acquire() as conn:
message_id = sa.select([sa_message.c.id]) \
.select_from(sa_message) \
.where(sa_chat.c.id == sa_message.c.chat_id) \
.order_by(desc(sa_message.c.id)) \
.limit(1) \
.as_scalar()
query = sa.select([sa_chat_permission.c.id.label('chat_permission_id'),
sa_chat_permission.c.permission,
sa_chat_permission.c.chat_name,
sa_chat_permission.c.chat_image,
sa_chat.c.id.label('chat_id'),
sa_chat.c.created_at,
message_id.label('message_id')
]) \
.select_from(
sa_chat
.join(sa_chat_permission, sa_chat_permission.c.chat_id == sa_chat.c.id, isouter=True)
) \
.where(or_(sa_chat_permission.c.user_id == user_id, sa_chat_permission.c.user_id == None)) \
.order_by(asc('message_id'))
return list(
map(lambda x: as_dict(dict(x)), await conn.execute(query)))
async def new_messages_by_chat_id(message: str, chat_id: int,
user_id: int) -> dict:
try:
query = sa_message.insert(inline=True)
query = query.values([{
'chat_id': chat_id,
'text': message,
'user_id': user_id
}]).returning(literal_column('*'))
async with config['db'].acquire() as conn:
new_message = [
as_dict(dict(message)) for message in (await (
await conn.execute(query)).fetchall())
]
if not new_message:
raise CustomHTTPException(irc['INTERNAL_SERVER_ERROR'],
500)
query = sa.select([sa_message.c.id,
sa_message.c.user_id,
sa_message.c.text,
label('message_text', sa_message.c.text),
label('message_image', sa_message.c.image),
sa_message.c.created_at,
label('user_name', (sa_user.c.name)),
sa_user.c.email,
label('user_image', sa_user.c.image)
]) \
.select_from(
sa_message
.join(sa_chat, sa_message.c.chat_id == sa_chat.c.id)
.join(sa_user, sa_message.c.user_id == sa_user.c.id)
) \
.where(sa_message.c.id == new_message[0]['id'])
new_message_with_params = list(
map(lambda x: as_dict(dict(x)), await conn.execute(query)))
if len(new_message_with_params) != 0:
return new_message_with_params[0]
except Exception as e:
raise await raise_db_exception(e)
async def create_new_chat_by_name(connect: SAConnection) -> dict:
try:
query = sa_chat.insert(inline=True)
query = query.values([]).returning(literal_column('*'))
new_chat = as_dict(dict((await (await connect.execute(query)).fetchall())[0]))
if not new_chat:
raise CustomHTTPException(irc['INTERNAL_SERVER_ERROR'], 500)
return new_chat
except Exception as e:
raise await raise_db_exception(e)
async def create_chat_permission_bulk(
chat_permissions: list, connect: SAConnection) -> dict or None:
try:
query = sa_chat_permission.insert(inline=True)
query = query.values(chat_permissions).returning(
literal_column('*'))
new_permissions = [
as_dict(dict(chat_permission)) for chat_permission in (await (
await connect.execute(query)).fetchall())
]
if not new_permissions:
raise CustomHTTPException(irc['INTERNAL_SERVER_ERROR'], 500)
return new_permissions
except Exception as e:
raise await raise_db_exception(e)
async def get_messages_by_chat_id(chat_id: int) -> list:
async with config['db'].acquire() as conn:
query = sa.select([sa_message.c.id,
sa_message.c.user_id,
sa_message.c.text,
label('message_text', sa_message.c.text),
label('message_image', sa_message.c.image),
sa_message.c.created_at,
label('user_name', (sa_user.c.name)),
sa_user.c.email,
label('user_image', sa_user.c.image)
]) \
.select_from(
sa_message
.join(sa_chat, sa_message.c.chat_id == sa_chat.c.id, isouter=True)
.join(sa_user, sa_message.c.user_id == sa_user.c.id, isouter=True)
) \
.where(sa_chat.c.id == chat_id) \
.order_by(asc(sa_message.c.id))
return list(
map(lambda x: as_dict(dict(x)), await conn.execute(query)))
async def get_last_participated_by_user_id(user_id: int) -> dict or None:
async with config['db'].acquire() as conn:
query = sa.select([sa_chat_permission.c.permission,
label('chat_id', sa_chat.c.id),
sa_chat.c.created_at
]) \
.select_from(
sa_chat_permission
.join(sa_chat, sa_chat_permission.c.chat_id == sa_chat.c.id, isouter=True)
.join(sa_message, sa_chat.c.id == sa_message.c.chat_id, isouter=True)
) \
.where(sa_chat_permission.c.user_id == user_id) \
.order_by(desc(sa_message.c.id)) \
.limit(1)
result = list(
map(lambda x: as_dict(dict(x)), await conn.execute(query)))
if len(result) != 0:
return result[0]
return None
async def get_participated_by_user_id_and_chat_id(chat_id: int,
user_id: int) -> list:
async with config['db'].acquire() as conn:
query = sa.select([sa_chat_permission.c.id.label('chat_permission_id'),
sa_chat_permission.c.permission,
sa_chat_permission.c.chat_image,
sa_chat_permission.c.chat_name,
sa_chat.c.id.label('chat_id'),
sa_chat.c.created_at,
]) \
.select_from(
sa_chat
.join(sa_chat_permission, sa_chat_permission.c.chat_id == sa_chat.c.id, isouter=True)
) \
.where(
and_(
or_(sa_chat_permission.c.user_id == user_id, sa_chat_permission.c.user_id == None),
sa_chat_permission.c.chat_id == chat_id
)
)
return list(
map(lambda x: as_dict(dict(x)), await conn.execute(query)))