Отношение многие ко многим без третьей таблицы в PostgreSQL используя Elixir Ecto

image

Иногда использование третьей таблицы для связи многое ко многим не есть необходимым и добавляет в разработку проекта дополнительные сложности. Попытаемся уйти от использования третьей таблицы используя столбец типа массив добавленный в PostgreSQL 9.1

Давайте создадим небольшое приложение на Elixir Phoenix с названием Demo для демонстрации:

$ mix phoenix.new demo
$ cd demo

Проверим, что в порядке используя созданные тесты:

$ mix test

Теперь создадим модель Group и Post, которая будет принадлежать к Group:

$ mix phoenix.gen.model Group groups name:string
$ mix phoenix.gen.model Post posts name:string body:text group_id:references:groups

Теперь мы хотим создать модель пользователя (User), который может принадлежать нескольким группам. Так же пользователь будет иметь доступ только к записям Post из собственных групп. Вместо того, чтобы создавать третью таблицу для связи users и groups давайте добавим к таблице users колонку group_ids:

$ mix phoenix.gen.model User users name:string group_ids:array:integer

Вот как выглядит модель User:

# web/models/user.ex
defmodule Demo.User do
  use Demo.Web, :model

  schema "users" do
    field :name, :string
    field :group_ids, {:array, :integer}

    timestamps()
  end

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:name, :group_ids])
    |> validate_required([:name, :group_ids])
  end
end

Заметьте, что метод changeset позволяет изменять group_ids. Так что, если мы будем использовать этот метод для редактирования профайла пользователя самим пользователем, то пользователь сможет добавить себя в любую группу. Если такая логика Вам не подходит, то можно добавить дополнительную валидацию, удостоверяющую, что значение group_ids является подмножеством разрешенных для пользователя групп. Ну или же можно просто запретить пользователю изменять group_ids.


Мы также можем добавить индекс на group_ids:

CREATE INDEX users_group_ids_rdtree_index ON users USING GIST (group_ids gist__int_ops);

Можете создать дополнительную миграцию для этого.

Теперь спланируем метод Post.accessible_by/2, который будет возвращать все записи Post из доступных для пользователя групп. Для этого создадим тест:

# test/models/post_test.exs
defmodule Demo.PostTest do
  use Demo.ModelCase

  alias Demo.{Post, Group, User}

  # Опущены тесты метода changeset

  test "accessible for user" do
    g1 = %Group{} |> Repo.insert!
    g2 = %Group{} |> Repo.insert!
    g3 = %Group{} |> Repo.insert!

          %Post{group_id: g1.id} |> Repo.insert!
    p21 = %Post{group_id: g2.id} |> Repo.insert!
    p22 = %Post{group_id: g2.id} |> Repo.insert!
    p31 = %Post{group_id: g3.id} |> Repo.insert!

    user = %User{group_ids: [g2.id, g3.id]} |> Repo.insert!

    post_ids = Post
    |> Post.accessible_by(user)
    |> Ecto.Query.order_by(:id)
    |> Repo.all
    |> Enum.map(&(&1.id))

    assert post_ids == [p21.id, p22.id, p31.id]
  end
end

Реализация метода:


# web/models/post.ex
defmodule Demo.Post do
  use Demo.Web, :model

  schema "posts" do
    field :name, :string
    field :body, :string
    belongs_to :group, Demo.Group

    timestamps()
  end

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:name, :body])
    |> validate_required([:name, :body])
  end

  def accessible_by(query, user) do
    from p in query, where: p.group_id in ^user.group_ids
  end
end

Тут мы и получаем все записи Post из всех групп пользователя.

Мы можем пойти далее и разрешить записи Post принадлежать сразу нескольким группам. Для этого добавим колонку group_ids к таблице posts так же как и для таблицы users, а колонку group_id удалим. Теперь запись Post будет доступна для пользователя тогда и только тогда когда в массиве group_ids у записи Post и в массиве group_ids пользователя есть хотя бы один общий элемент.

Для этого мы можем использовать оператор перекрытия в PostgreSQL. Измененная модель Post:

# web/models/post.ex
defmodule Demo.Post do
  use Demo.Web, :model

  schema "posts" do
    field :name, :string
    field :body, :string
    field :group_ids, {:array, :integer}

    timestamps()
  end

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:name, :body, :group_ids])
    |> validate_required([:name, :body, :group_ids])
  end

  def accessible_by(query, user) do
    from p in query, where: fragment("? && ?", p.group_ids, ^user.group_ids)
  end
end

В качестве упражнения, можете обновить также миграцию для создания таблицы posts и тесты модели Post. Не забудьте добавить индекс на колонку group_ids в таблице posts.

Надеюсь это будет хотя бы кому-то полезно. Спасибо.
Поделиться публикацией

Комментарии 28

    0
    Спасибо за интересное решение. А производительность не сравнивали с более традиционным подходом?
      0

      Отчет о производительности для 200 000 групп,
      4 000 000 записей Post,
      каждая запись Post находитсья в 40 группах — https://github.com/shhavel/demo_group_ids_performance_test
      Возмодно, 4 000 000 записей оказалось мало, так кhttps://github.com/shhavel/demo_group_ids_performance_testак, достаточно быстро работает и без индекса.
      Это для случая при котором Post принадлежит многим группам.


      Если же Post принадлежит только одной группе,
      то способ выбора всех записей Post с использованием массива group_ids немного проще
      в сравнении с использованием третьей таблицы.
      Запрос получения записей Post доступных пользователю:


      SELECT * FROM posts WHERE id IN (<user.group_ids>);

      где <user.group_ids> — список ID групп пользователя.
      При использованиии третьей таблицы этот список еще нужно получить,
      а при использовании колонки group_ids список заведома известен (при условии, что выполнен запрос на получение пользователя)


      Спасибо

      +1
      Производительность у колонок массивов в pg неплохая, если построить индексы. Правда с построением индексов по некоторым полям бывают проблемы, например индекс по UUID[] потребует дополнительных действий.
        +1

        Статья хорошая, спасибо, вот только больно смотреть, когда для объяснения работы с Ecto вдруг оказался нужен Phoenix.

          0
          Иначе заметка получилась бы в пять раз короче и проще же :)
            0
            Статья содержит только примеры кода моделей и тестов моделей
            (ее суть — как раз показать использование колонки типа Array в моделях),
            если убрать Phoenix статья короче не станет.

            Спасибо
              +1
              Сначала коммьюнити почти сделало из руби похапе-на-стероидах, потому что «ну с рельсами быстрее же». Теперь финикс (который, к счастью, гораздо меньше рассчитан на дебилов, чем рельсы) тащат в примеры использования Ecto.

              Веб здесь избыточный, вредный и неуместный артефакт.
            0
            «больно» — это проявление чувства, что может быть связано с индивидуальными особенностями Вашего организма.
            Но я так понимаю, Вы считаете использование Phoenix в данной статье неоправданным.
            Phoenix был использован для генерации моделей и тестов, и возможно, это уменьшает объем работы для воспроизведения описанного решения. Поэтому я считаю использование Phoenix уместным.

            Спасибо
            0
            Это всё, конечно, хорошо, но что будет если удалить группу? Для массивов нет поддержки foreign keys (2ndquadrant хотели добавить её в 9.3, но были проблемы с производительностью, потом, судя по всему, переключились на другое).
              –1

              Я думаю, поддержка foreign keys будет еще добавлена в PostgreSQL.
              На сегодняшний же день, можно просто удалять ID группы из всей записей posts
              при удалении самой группы:


              UPDATE posts SET group_ids = group_ids - 4 WHERE group_ids && ARRAY[4];

              Несомненно, это создает дополнительное неудобство, но не отбрасывает вариант использования колонки типа Array как альтернативу третьей таблицы.


              Спасибо

              +4
              Часто возникает необходимость добавить дополнительные свойства в третью таблицу. По мере роста базы данных это очень даже вероятно. Потом можно очень сильно пожалеть что не сделал старую добрую таблицу и решение будет больше похоже на костыль чем на удобный вариант. А ведь может потребоваться и связи делать с третьей страницев в таком случае совсем сложно представить как это будет выглядить. Обычная реляционная структура БД части которой реализованны в одном поле массивом.
                –1

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


                Спасибо

                0
                А как быть с целостностью данных?
                  0
                  Пожалуйста, смотрите один из предыдущих ответов.
                  Спасибо
                  0
                  Я пару раз так делал. Но оба раза закончилось тем, что понадобилось на эту связь m2m повесить дополнительные признаки и пришлось переделывать на класический m2m с третьей таблицей.
                  И не стоит забывать про проверку существования ключей. Например удаляете вы группу, а кто удалит её из списков у всех юзеров. Foregin key не даст просто так сделать не консистентную базу, а список интов — даст.
                    0
                    Интересно было бы посмотреть на механихм работы такого индекса. Не будет ли он просто аналогом той самой третьей таблицы?
                      0

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


                      Рассмотрим пример исользования в rails с исользованием cancan.
                      Пусть, Post принадлежит только одной группе
                      Третья таблица, для связи users и groupsmemberships


                      Определение выборки всех записей Post доступных пользователю с использованием третьей таблицы:


                      can :read, Post, group_id: user.memberships.pluck(:group_id)

                      Определение выборки всех записей Post доступных пользователю с использованием group_ids:


                      can :read, Post, group_id: user.group_ids

                      Спасибо

                        0
                        А конкретных бенчей нет?
                          0

                          Пока только это — https://github.com/shhavel/demo_group_ids_performance_test


                          200 000 групп
                          4 000 000 постов
                          каждый пост принадлежит 40-ка группам.


                          Выборка работает быстро и без индекса.
                          Постараюсь подготовить еще данные, чтобы было заметно разницу от использования индекса.

                            0
                            Удобней всего увидеть в виде таблице с сравнением вашего метода
                            с обычно используемым решением в виде третьей таблицы.
                      0
                      Интересная реализация, но только на теории(ну или на курилке поболтать). На практике никто так делать не будет. Плюсов у данного подхода нет вовсе.
                        +1

                        Это решение уже используется в production.
                        Я попытался описать его преимущества в некоторых ситуациях.


                        Спасибо

                          0
                          В статье все очень красиво. Но обратите внимание на свои ответы в комментариях, когда посыпались вопросы: «дополнительное неудобство»,
                          «подходит только для случая». Решение может и имеет право на жизнь, но при огромных лимитейшенах, а хорошего бенефита не видно. Видно только убийство масштабируемости.
                            –1

                            «На один запрос меньше» при каждом обращении к приложению — это приимущество.


                            А также:


                            • упрощение кода приложения при получении списка ID груп пользователя.
                            • очевидная связь массивва group_ids с HTML multi select для выбора групп
                              и только один UPDATE запрос при редактировании.

                            Спасибо

                              0
                              Плюс в том что на один запрос меньше, минус что если вам связки не нужны а нужны свойства вы просто вынуждены получать запись с информацией о всех связях. При большом количестве данных их может быть очень много. Система просто не знает что вы от нее хотите связи или свойства записи. И разделить их нет возможности. К отдельной таблице вы обращаетесь только в случае выборки связей а в записе получаете только свойства лишних данных нет. Данных гоняется больше. По мне так плюс и минус компенсируют себя, а при возрастании количества данных может стать серьезной проблемой быстродействия.
                                –1

                                Если в таблице есть вторичный ключ, то это тожt можно назвать хранением свойств (бизнесс данных) и связок (служебных данных).
                                Кроме того Вы не вынуждены получать запись с информацией о всех связях если эта информация вам не нужна,
                                можно же выбирать только те поля которые нужны перечислив их в SELECT.


                                Например если нужно выбрать только id, name, email:


                                SELECT id, name, email FROM users;
                          0
                          В PostgreSQL появились некоторые возможности баз данных NoSQL, плюс еще и с индексами.
                          И иногда, это вполне может оказаться полезным.
                          Например, если таблица, которая хранит сущность данных массива, небольшая и из нее редко удаляются данные, то почему нет? :)
                          0
                          Спасибо за статью! Думал, что никто и не пользуется таким :)
                          Я в связке с Django использую поле-массив для тэгов. Пока вроде все нормально.

                          Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                          Самое читаемое