def get_tags(user_id=None): if user_id: sql = ("SELECT tags.name FROM `tags` INNER JOIN `user_tag` " "ON tags.id = user_tag.tag_id WHERE user_tag.user_id = %s") tags = db.get_all_rows(sql, values=(user_id, )) if not tags: return () return [tag_name for tag_name, in tags] else: sql = "SELECT ut.user_id, tags.name FROM tags INNER JOIN user_tag AS ut ON tags.id = ut.tag_id ORDER BY ut.user_id" tags_groups = db.get_all_rows(sql) if not tags_groups: return None tags_groups = itertools.groupby(tags_groups, lambda pair: pair[0]) return { user: list(tag for _, tag in group) for user, group in tags_groups }
def get_all_users( cls, user_match, filters=None, sort_by=None ): # аргументы(ссылка на текущий класс, ссылка на текущего юзера, установленные фильтры, ) sql = ( "SELECT id FROM `users` " "WHERE NOT (biography is NULL OR age IS NULL OR city IS NULL OR gender IS NULL " "OR preferences IS NULL)") if user_match: sql, values = cls.filter_by_preferences( sql, user_match ) # возвращает дополненный sql запрос и массив предпочтений (пол, предпочтение) users = db.get_all_rows( sql, values=values ) # возвращает массив подходящих юзеров по предпочтениям else: users = db.get_all_rows( sql ) # возвращает всех юзеров, у которых заполнены основные данные users = [Account(user, extended=False) for user in users ] # создает сущности юзеров посредством конструктора аккаунт fame_rates = cls.get_fame_rating( ) # возвращает список с рейтингом пользователей tags_groups = cls.get_tags() # возвращает список с тэгами for user in users: user.fame = fame_rates.get( user.id, 0 ) if fame_rates else 0 # назначает каждому польхователю его рейтинг user.tags = tags_groups.get(user.id, ()) if tags_groups else ( ) # назначает каждому польхователю его теги if filters is not None: users = [user for user in users if user.filter_fit(filters) ] # применяет фильтры с главной страницы (строго) sort_func = cls.create_sort_func(user_match, sort_by) # создает функцию обработки if sort_func is not None: users = sorted( users, key=sort_func ) # сортирует юзеров по возвращаемому значению функции обработки return users #
def get_messages(self): sql = "SELECT * FROM `messages` WHERE chat_id = %s ORDER BY timestamp" messages = db.get_all_rows(sql, values=(self.id, ), cur_class=DictCursor) for message in messages: local_dt = message['timestamp'].replace( tzinfo=pytz.utc).astimezone(self.tz) self.tz.normalize(local_dt) message['timestamp'] = local_dt.strftime(self.timestamp_format) return messages
def get_all_users(cls, user_match, filters=None, sort_by=None): sql = ( "SELECT id FROM `users` " "WHERE NOT (biography is NULL OR age IS NULL OR city IS NULL OR gender IS NULL " "OR preferences IS NULL)") if user_match: sql, values = cls.filter_by_preferences(sql, user_match) users = db.get_all_rows(sql, values=values) else: users = db.get_all_rows(sql) users = [Account(user, extended=False) for user in users] fame_rates = cls.get_fame_rating() tags_groups = cls.get_tags() for user in users: user.fame = fame_rates.get(user.id, 0) if fame_rates else 0 user.tags = tags_groups.get(user.id, ()) if tags_groups else () if filters is not None: users = [user for user in users if user.filter_fit(filters)] sort_func = cls.create_sort_func(user_match, sort_by) if sort_func is not None: users = sorted(users, key=sort_func) return users
def update_user_tags(self, new_tags): if self.tags == new_tags: return None all_tags = db.get_all_rows("SELECT * FROM `tags`") all_tags = set(tag_name for _, tag_name in all_tags) sql = ', '.join('(%s)' for tag_name in new_tags if tag_name not in all_tags) new_tags = set(new_tags) nonexistent_tags = new_tags - all_tags if nonexistent_tags: sql = f"INSERT INTO `tags` (name) VALUES {sql}" db.query(sql, values=nonexistent_tags, commit=True) sql = "DELETE FROM `user_tag` WHERE user_id = %s" db.query(sql, values=(self.id, ), commit=True) all_tags = db.get_all_rows("SELECT * FROM `tags`") sql = ', '.join("(%s, %s)" for _ in range(len(new_tags))) user_tag_ids = itertools.chain.from_iterable( (self.id, tag_id) for tag_id, tag_name in all_tags if tag_name in new_tags) if len(sql): sql = f"INSERT INTO `user_tag` (user_id, tag_id) VALUES {sql}" db.query(sql, values=user_tag_ids, commit=True)
def get_fame_rating(user_id=None): sql = "SELECT liked_id AS user, COUNT(liked_id) AS like_num FROM `likes` GROUP BY user" search_in = db.get_all_rows(sql) if not search_in: return 0 max_likes = max(like_num for _, like_num in search_in) if user_id: user_likes = [ like_num for user, like_num in search_in if user == user_id ] if not user_likes: return 0 return round(user_likes[0] / max_likes * 100) return { user_id: round(like_num / max_likes * 100) for user_id, like_num in search_in }
def get_chats(cls, user_id): sql = ( "SELECT chat_id, sender_id, recipient_id, text, timestamp FROM messages " "INNER JOIN chats ON messages.chat_id = chats.id " "WHERE sender_id = %s OR recipient_id = %s ORDER BY timestamp DESC" ) columns = ('chat_id', 'sender_id', 'recipient_id', 'message', 'timestamp') chats = db.get_all_rows(sql, values=(user_id, user_id)) if not chats: return None chats = itertools.groupby(chats, lambda row: row[0]) chats = list(dict(zip(columns, message)) for _, (message, *_) in chats) for chat in chats: second_user = chat['recipient_id'] if chat['sender_id'] == session[ 'user'] else chat['sender_id'] chat['second_user'] = Account(second_user, extended=False) local_dt = chat['timestamp'].replace(tzinfo=pytz.utc).astimezone( cls.tz) cls.tz.normalize(local_dt) chat['timestamp'] = local_dt.strftime(cls.timestamp_format) return chats
def get_tag_list(): res = db.get_all_rows("SELECT name as id, name AS text FROM tags", cur_class=MySQLdb.cursors.DictCursor) return jsonify({'success': True, 'tags': res})
def get_notifications(cls, user_id): sql = "SELECT id FROM notifications WHERE user_id = %s ORDER BY date_created DESC" notifications = db.get_all_rows(sql, values=(user_id, )) return [ cls(notification_id).to_json() for notification_id in notifications ]
def visited(self): sql = "SELECT visited_id FROM `visits` WHERE user_id = %s" response = db.get_all_rows(sql, values=(self.id, )) return [visited for visited, in response]
def reported(self): sql = "SELECT reported_id FROM `reports` WHERE user_id = %s" response = db.get_all_rows(sql, values=(self.id, )) return [reported_user for reported_user, in response]
def blocked(self): sql = "SELECT blocked_id FROM `blocked` WHERE user_id = %s" response = db.get_all_rows(sql, values=(self.id, )) return [blocked_user for blocked_user, in response]
def liked(self): sql = "SELECT liked_id FROM `likes` WHERE user_id = %s" response = db.get_all_rows(sql, values=(self.id, )) return [liked_id for liked_id, in response]