19.09.2016 18:44
shhavel 28 6300 Источник
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)


  1. Hacker13ua
    19.09.2016 19:13
    #9816108

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


    1. shhavel
      20.09.2016 13:52
      #9817410

      Отчет о производительности для 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. VitGo
    15.01.2017 18:11
    #9816168

    это не правильный путь…

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


  1. Virviil
    19.09.2016 20:24
    #9816220
    +1

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


    1. iqiaqqivik
      20.09.2016 10:11
      #9816896

      Иначе заметка получилась бы в пять раз короче и проще же :)


      1. shhavel
        20.09.2016 13:54
        #9817424

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

        Спасибо


        1. iqiaqqivik
          20.09.2016 17:49
          #9818152
          +1

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

          Веб здесь избыточный, вредный и неуместный артефакт.


    1. shhavel
      20.09.2016 13:53
      #9817420

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

      Спасибо


  1. VtD
    20.09.2016 06:29
    #9816716

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


    1. shhavel
      20.09.2016 13:55
      #9817426
      -1

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


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

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


      Спасибо


  1. mpakep
    20.09.2016 07:44
    #9816748
    +4

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


    1. shhavel
      20.09.2016 13:57
      #9817436
      -1

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


      Спасибо


  1. disa1985
    20.09.2016 13:58
    #9817438

    А как быть с целостностью данных?


    1. shhavel
      20.09.2016 13:58
      #9817440

      Пожалуйста, смотрите один из предыдущих ответов.
      Спасибо


  1. mr_tron
    20.09.2016 13:58
    #9817442

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


  1. YaMishar
    20.09.2016 13:59
    #9817444

    Интересно было бы посмотреть на механихм работы такого индекса. Не будет ли он просто аналогом той самой третьей таблицы?


    1. shhavel
      20.09.2016 14:00
      #9817448

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


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


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


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

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


      can :read, Post, group_id: user.group_ids

      Спасибо


      1. claygod
        22.09.2016 11:54
        #9822562

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


        1. shhavel
          22.09.2016 12:11
          #9822600

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


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


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


          1. claygod
            22.09.2016 18:17
            #9823652

            Удобней всего увидеть в виде таблице с сравнением вашего метода
            с обычно используемым решением в виде третьей таблицы.


  1. wankel
    20.09.2016 14:04
    #9817456

    Интересная реализация, но только на теории(ну или на курилке поболтать). На практике никто так делать не будет. Плюсов у данного подхода нет вовсе.


    1. shhavel
      20.09.2016 14:04
      #9817458
      +1

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


      Спасибо


      1. wankel
        20.09.2016 14:43
        #9817540

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


        1. shhavel
          20.09.2016 14:57
          #9817594
          -1

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


          А также:


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

          Спасибо


          1. mpakep
            20.09.2016 17:27
            #9818108

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


            1. shhavel
              20.09.2016 18:01
              #9818176
              -1

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


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


              SELECT id, name, email FROM users;


    1. burdin
      20.09.2016 15:21
      #9817668

      В PostgreSQL появились некоторые возможности баз данных NoSQL, плюс еще и с индексами.
      И иногда, это вполне может оказаться полезным.
      Например, если таблица, которая хранит сущность данных массива, небольшая и из нее редко удаляются данные, то почему нет? :)


  1. burdin
    20.09.2016 15:07
    #9817622

    Спасибо за статью! Думал, что никто и не пользуется таким :)
    Я в связке с Django использую поле-массив для тэгов. Пока вроде все нормально.