def get_messages_stats(cls):
stats = db.session.query(MessageTableEntry.team_id,
MessageTableEntry.send_type,
func.count().label("messages")).group_by(
MessageTableEntry.send_type,
MessageTableEntry.team_id).all()
return MessageModelMapper.to_stats(stats)
def get_message_direct_receiver_by_ids(cls, user_id, team_id):
receiver = db.session.query(
UserTableEntry.user_id, UsersByTeamsTableEntry.team_id,
literal(True).label("is_user")).join(
UsersByTeamsTableEntry,
and_(UsersByTeamsTableEntry.user_id == UserTableEntry.user_id,
UsersByTeamsTableEntry.user_id == user_id,
UsersByTeamsTableEntry.team_id == team_id)).one_or_none()
return MessageModelMapper.to_message_receiver(receiver)
def get_mentions_by_message(cls, message_id):
mentions = db.session.query(
MentionsByMessagesTableEntry.client_id, UserTableEntry.username,
UserTableEntry.first_name, UserTableEntry.last_name,
ChannelTableEntry.name.label("channel_name"),
BotTableEntry.bot_name, UserTableEntry.user_id.label("is_user"),
ChannelTableEntry.channel_id.label("is_channel")).outerjoin(
UserTableEntry, UserTableEntry.user_id ==
MentionsByMessagesTableEntry.client_id).outerjoin(
ChannelTableEntry, ChannelTableEntry.channel_id ==
MentionsByMessagesTableEntry.client_id).outerjoin(
BotTableEntry, BotTableEntry.bot_id ==
MentionsByMessagesTableEntry.client_id).filter(
MentionsByMessagesTableEntry.message_id ==
message_id).all()
return MessageModelMapper.to_mentions(mentions)
def get_channel_messages_previews(cls, user_id, team_id):
chats = db.session.query(ChatTableEntry).filter(
ChatTableEntry.user_id == user_id).subquery("sq1")
last_messages = db.session.query(
MessageTableEntry.receiver_id.label("channel_id"),
func.max(
MessageTableEntry.timestamp).label("maxtimestamp")).filter(
and_(
MessageTableEntry.team_id == team_id,
MessageTableEntry.send_type ==
SendMessageType.CHANNEL.value)).group_by(
MessageTableEntry.receiver_id).subquery("sq2")
messages_previews = db.session.query(
MessageTableEntry.message_id,
MessageTableEntry.receiver_id.label("chat_id"),
ChannelTableEntry.name.label("chat_name"),
MessageTableEntry.sender_id.label("sender_id"),
UserTableEntry.username.label("sender_username"),
UserTableEntry.first_name.label("sender_first_name"),
UserTableEntry.last_name.label("sender_last_name"),
BotTableEntry.bot_name.label("sender_bot_name"),
MessageTableEntry.content.label("content"),
MessageTableEntry.message_type,
MessageTableEntry.timestamp.label("message_timestamp"),
chats.c.unseen.label("unseen_offset"),
UserTableEntry.user_id.label("is_user")
).join(
last_messages,
and_(MessageTableEntry.receiver_id == last_messages.c.channel_id,
MessageTableEntry.timestamp == last_messages.c.maxtimestamp)
).join(
chats,
and_(MessageTableEntry.team_id == chats.c.team_id,
MessageTableEntry.receiver_id == chats.c.chat_id)).outerjoin(
UserTableEntry,
UserTableEntry.user_id == MessageTableEntry.sender_id
).outerjoin(
BotTableEntry,
BotTableEntry.bot_id == MessageTableEntry.sender_id).join(
ChannelTableEntry, ChannelTableEntry.channel_id ==
MessageTableEntry.receiver_id).all()
return MessageModelMapper.to_channel_messages_previews(
messages_previews)
def get_channel_chat(cls, chat_id, team_id, offset, limit):
chat = db.session.query(
MessageTableEntry.message_id, MessageTableEntry.sender_id,
MessageTableEntry.receiver_id, MessageTableEntry.team_id,
MessageTableEntry.content, MessageTableEntry.message_type,
MessageTableEntry.timestamp, UserTableEntry.username,
UserTableEntry.first_name, UserTableEntry.last_name,
UserTableEntry.online, BotTableEntry.bot_name,
UserTableEntry.user_id.label("is_user")).outerjoin(
UserTableEntry,
MessageTableEntry.sender_id == UserTableEntry.user_id
).outerjoin(
BotTableEntry,
MessageTableEntry.sender_id == BotTableEntry.bot_id).filter(
and_(MessageTableEntry.team_id == team_id,
MessageTableEntry.receiver_id == chat_id)).offset(
offset).limit(limit).all()
return MessageModelMapper.to_messages_chat(chat)
def get_direct_messages_previews(cls, user_id, team_id):
chats = db.session.query(ChatTableEntry).filter(
ChatTableEntry.user_id == user_id).subquery("sq1")
last_messages_mixed = db.session.query(
func.least(MessageTableEntry.sender_id,
MessageTableEntry.receiver_id).label("user1"),
func.greatest(MessageTableEntry.sender_id,
MessageTableEntry.receiver_id).label("user2"),
func.max(
MessageTableEntry.timestamp).label("maxtimestamp")).filter(
and_(
or_(MessageTableEntry.receiver_id == user_id,
MessageTableEntry.sender_id == user_id),
MessageTableEntry.team_id == team_id,
MessageTableEntry.send_type ==
SendMessageType.DIRECT.value)).group_by(
func.least(MessageTableEntry.sender_id,
MessageTableEntry.receiver_id),
func.greatest(
MessageTableEntry.sender_id,
MessageTableEntry.receiver_id)).subquery("sq2")
last_messages = db.session.query(
MessageTableEntry.message_id, MessageTableEntry.sender_id,
MessageTableEntry.receiver_id, UserTableEntry.username,
UserTableEntry.first_name, UserTableEntry.last_name,
UserTableEntry.profile_pic, UserTableEntry.online,
BotTableEntry.bot_name, MessageTableEntry.content,
MessageTableEntry.message_type, MessageTableEntry.timestamp,
UserTableEntry.user_id.label("is_user"), chats.c.unseen
).join(
last_messages_mixed,
and_(
or_(
MessageTableEntry.sender_id == last_messages_mixed.c.user1,
MessageTableEntry.sender_id == last_messages_mixed.c.user2,
),
or_(
MessageTableEntry.receiver_id ==
last_messages_mixed.c.user1,
MessageTableEntry.receiver_id ==
last_messages_mixed.c.user2,
), MessageTableEntry.timestamp ==
last_messages_mixed.c.maxtimestamp,
MessageTableEntry.send_type == SendMessageType.DIRECT.value)
).join(
chats,
and_(
or_(
MessageTableEntry.sender_id == chats.c.chat_id,
MessageTableEntry.receiver_id == chats.c.chat_id,
),
or_(
MessageTableEntry.sender_id == chats.c.user_id,
MessageTableEntry.receiver_id == chats.c.user_id,
))
).outerjoin(
UserTableEntry,
or_(
and_(UserTableEntry.user_id == last_messages_mixed.c.user1,
UserTableEntry.user_id != user_id),
and_(UserTableEntry.user_id == last_messages_mixed.c.user2,
UserTableEntry.user_id != user_id))
).outerjoin(
BotTableEntry,
or_(
and_(BotTableEntry.bot_id == last_messages_mixed.c.user1,
BotTableEntry.bot_id != user_id),
and_(BotTableEntry.bot_id == last_messages_mixed.c.user2,
BotTableEntry.bot_id != user_id))).all()
return MessageModelMapper.to_direct_messages_previews(last_messages)
def get_message_channel_receiver_by_ids(cls, channel_id):
receiver = db.session.query(
ChannelTableEntry.team_id,
literal(False).label("is_user")).filter(
ChannelTableEntry.channel_id == channel_id).one_or_none()
return MessageModelMapper.to_message_receiver(receiver)
def get_chat_by_ids(cls, user_id, chat_id, team_id):
chat = db.session.query(ChatTableEntry).filter(
and_(ChatTableEntry.user_id == user_id,
ChatTableEntry.chat_id == chat_id,
ChatTableEntry.team_id == team_id)).one_or_none()
return MessageModelMapper.to_chat(chat)
def add_message(cls, message):
message_entry = MessageDatabaseMapper.to_message(message)
DatabaseClient.add(message_entry)
return MessageModelMapper.to_message(message_entry)