Блог

Я пишу REST api, который работает с SQL, и постоянно нахожусь в ситуациях, похожих на эту, где мне нужно возвращать списки объектов с вложенными списками внутри каждого объекта, запрашивая объединения таблиц.

Допустим, у меня есть отношения «многие ко многим» между пользователями и группами. У меня есть таблица пользователей и таблица групп и таблица соединений UserGroup между ними. Теперь я хочу написать конечную точку REST, которая возвращает список пользователей и для каждого пользователя группы, в которые они зарегистрированы. Я хочу вернуть json в таком формате:

 [
    {
        "username": "test_user1",
        <other attributes ...>
        "groups": [
            {
                "group_id": 2,
                <other attributes ...>
            },
            {
                "group_id": 3,
                <other attributes ...>
            }
        ]
    },
    {
        "username": "test_user2",
        <other attributes ...>
        "groups": [
            {
                "group_id": 1,
                <other attributes ...>
            },
            {
                "group_id": 2,
                <other attributes ...>
            }
        ]
    },
    etc ...
  

Есть два или три способа запросить SQL для этого, о которых я могу думать:

1. Выдавать переменное количество SQL-запросов: запрашивать список пользователей, затем перебирать каждого пользователя для запроса по соединительной связи, чтобы заполнить список групп для каждого пользователя. Количество SQL-запросов линейно увеличивается с увеличением числа возвращенных пользователей.

пример (с использованием python flask_sqlalchemy / flask_restx):

 users = db.session.query(User).filter( ... )
for u in users:
    groups = db.session.query(Group).join(UserGroup, UserGroup.group_id == Group.id) 
        .filter(UserGroup.user.id == u.id)
retobj = api.marshal([{**u.__dict__, 'groups': groups} for u in users], my_model)
# Total number of queries: 1   number of users in result
  

2. Выдавать постоянное количество SQL-запросов: это можно сделать, выполнив один монолитный SQL-запрос, выполняющий все объединения с потенциально большим количеством избыточных данных в столбцах пользователя, или, что часто более предпочтительно, несколько отдельных SQL-запросов. Например, запросите список пользователей, затем запросите объединение таблицы групп для GroupUsers, затем вручную сгруппируйте группы в коде сервера.

пример кода:

 from collections import defaultdict
users = db.session.query(User).filter( ... )
uids = [u.id for u in users]
groups = db.session.query(User.user_id, Group).join(UserGroup, UserGroup.group_id == Group.id) 
        .filter(UserGroup.user_id._in(uids))
aggregate = defaultdict(list)
for g in groups:
    aggregate[g.user_id].append(g[1].__dict__)
retobj = api.marshal([{**u.__dict__, 'groups': aggregate[u.id]} for u in users], my_model)
# Total number of queries: 2
  

3. Третий подход, имеющий ограниченную полезность, заключается в использовании string_agg или аналогичного подхода, чтобы заставить SQL объединить группировку в один строковый столбец, а затем распаковать строку в список на стороне сервера, например, если все, что мне нужно, это номер группы, я мог бы использовать string_agg и group_by, чтобы получить обратно «1,2» в одном запросе к пользовательской таблице. Но это полезно только в том случае, если вам не нужны сложные объекты.

Меня привлекает второй подход, потому что я чувствую, что он более эффективен и масштабируем, потому что количество SQL-запросов (которое, как я предполагаю, является основным узким местом без особой причины) постоянно, но на стороне сервера требуется еще немного работы, чтобы отфильтровать все группы для каждого пользователя. Но я подумал, что часть смысла использования SQL заключается в том, чтобы воспользоваться его эффективной сортировкой / фильтрацией, чтобы вам не приходилось делать это самостоятельно.

Итак, мой вопрос в том, прав ли я, полагая, что хорошей идеей будет сделать количество SQL-запросов постоянным за счет увеличения времени обработки на стороне сервера и разработки? Это пустая трата времени, чтобы попытаться сократить количество ненужных SQL-запросов? Буду ли я сожалеть об этом, если не сделаю этого, когда API будет протестирован в масштабе? Есть ли лучший способ решить эту проблему, о котором я не знаю?

Используя joinedload option, вы можете загрузить все данные всего одним запросом:

 q = (
    session.query(User)
    .options(db.joinedload(User.groups))
    .order_by(User.id)
)
users = q.all()
for user in users:
    print(user.name)
    for ug in user.groups:
        print("  ", ug.name)
  

При выполнении приведенного выше запроса все группы были бы уже загружены из базы данных с помощью запроса, аналогичного приведенному ниже:

 SELECT "user".id,
       "user".name,
       group_1.id,
       group_1.name
FROM   "user"
LEFT OUTER JOIN (user_group AS user_group_1
                 JOIN "group" AS group_1 ON group_1.id = user_group_1.group_id)
            ON  "user".id = user_group_1.user_id
  

И теперь вам нужно только сериализовать результат с помощью правильной схемы.