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)