Установка значений пустых строк JSONB в Null при сортировке с помощью Ransack

Эта статья была перепечатана из блога orbit.love. Оригинальную версию вы можете найти здесь.


Использование гема Ransack — это отличный способ добавить функциональность поиска в ваше приложение без необходимости использования дополнительных библиотек или зависимостей.

Одна из проблем, с которой вы можете столкнуться, возникает при сортировке данных по атрибуту, вложенному в колонку JSON. Если значение атрибута является пустой строкой, он будет включен в отсортированный список (даже если мы объявили :nulls_last в конфигурации Ransack).

В этой статье я расскажу о том, как обеспечить, чтобы значения JSONB с пустой строкой рассматривались как нулевые значения и помещались в конец любого отсортированного списка при поиске и сортировке с помощью Ransack.

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

Примечание: для Member #3 и Member #4 пустая строка для местоположения.

Наша цель — отсортировать таблицу Members по местоположению, и убедиться, что все значения Location с пустой строкой будут рассматриваться как нулевые значения в отсортированном списке.

Более конкретно, нам нужно, чтобы результирующий SQL-запрос, сгенерированный Ransack, выглядел следующим образом:

SELECT DISTINCT "members".*, NULLIF("members"."data" ->> 'location', ")
FROM "members"
ORDER BY NULLIF("members"."data" ->> 'location', '') ASC NULLS LAST
Войти в полноэкранный режим Выйти из полноэкранного режима

Сортировка по вложенным атрибутам JSONB с помощью Ransack

ransackers: специальные методы, предоставляемые Ransack для создания дополнительных функций поиска через Arel

Используя преимущества ransackers, мы можем сортировать нашу таблицу по атрибутам JSONB расположения членов.

Давайте добавим следующее в нашу модель Member:

class Member < ApplicationRecord
    # ...

    ransacker :location do |parent|
          Arel::Nodes::InfixOperation.new(
             '->>', 
             parent.table[:data], 
             Arel::Nodes::Quoted.new(‘location’)
          )
        end

     # ...
end
Войти в полноэкранный режим Выйти из полноэкранного режима

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

Наш результат Ransack определяется как:

def search_result

     members = Member.arel_table
     data_column = members[:data]

     workspace
         .members
         .select(members[Arel.star], jsonb_attr(data_column, 'location'))
         .ransack(ransack_filter)
         .result(distinct: true)
 end

 def jsonb_attr(column, attr)
     Arel::Nodes::InfixOperation.new(
         '->>', 
         column, 
         Arel::Nodes::Quoted.new(attr.to_s)
 end
Войти в полноэкранный режим Выйти из полноэкранного режима

С помощью приведенного выше кода мы делаем атрибут Location JSONB доступным в операторе SELECT нашего SQL-запроса:

SELECT DISTINCT "members".*, "members"."data" ->> 'location'
FROM "members"
Войти в полноэкранный режим Выйти из полноэкранного режима

Когда мы сортируем нашу таблицу по местоположению, это работает. Отчасти.

Проблема в том, что значения Location с пустой строкой были помещены в начало отсортированного списка:

Для одной записи это может быть не очень важно.

Но подумайте о десятках записей, в которых атрибут Location пользователя является пустой строкой. В этом случае пользователю придется просеять несколько пустых значений Location, чтобы добраться до начала алфавитного списка.

Установка NULLS LAST не учитывает значения пустой строки

Согласно документации Ransack, мы можем использовать :nulls_first или :nulls_last, чтобы решить, хотим ли мы, чтобы нулевые значения появлялись до или после ненулевых значений при сортировке.

Мы также можем заставить нули всегда быть первыми или последними, используя :nulls_always_first или :nulls_always_last.

Хотя это и полезно, но не применимо к значениям пустых строк, которые могут присутствовать в колонке JSONB.

Что же делать? Нам нужно установить все значения пустых строк в столбце Location как нулевые значения, чтобы они сортировались после непустых значений.

Установка значений пустых строк JSONB в нулевые значения
Метод ниже позволит нам взять любое значение пустой строки и вернуть его как NULL, когда Ransack построит наш запрос #search_results:

def null_if(column, value)
    Arel::Nodes::NamedFunction.new "NULLIF", [column, value]
end
Войти в полноэкранный режим Выйти из полноэкранного режима

В нашем коде мы можем поместить этот метод ниже метода jsonb_attr:

def jsonb_attr(column, attr)
    Arel::Nodes::InfixOperation.new(
        '->>', 
        column, 
        Arel::Nodes::Quoted.new(attr.to_s)
     )
end

def null_if(column, value)
    Arel::Nodes::NamedFunction.new "NULLIF", [column, value]
end
Войти в полноэкранный режим Выйти из полноэкранного режима

Далее нам нужно обновить наш метод jsonb_attr, обернув код метода в помощник null_if:

def jsonb_attr(column, attr)
    null_if(
            Arel::Nodes::InfixOperation.new('->>', column, Arel::Nodes::Quoted.new(attr.to_s)),
            Arel::Nodes::Quoted.new('')
        )
end
Войти в полноэкранный режим Выйти из полноэкранного режима

Для аргументов null_if выше, столбец — это объект InfixOperation, а значение — пустая строка, построенная классом Arel::Nodes::Quoted.

Обновление сортировщика местоположений для возвращения условия NULLIF ORDER BY

Теперь, если я попытаюсь отсортировать по Location в таблице, я получу ошибку Postgres:

ActiveRecord::StatementInvalid (PG::InvalidColumnReference: ERROR:  
for SELECT DISTINCT, ORDER BY expressions must appear in select list)
Enter fullscreen mode Выход из полноэкранного режима

Мы видим эту ошибку, потому что пытаемся выполнить ORDER BY следующим образом:

ORDER BY "members"."data" ->> 'location'

Войти в полноэкранный режим Выйти из полноэкранного режима

Когда в операторе SELECT указано следующее:

SELECT DISTINCT … NULLIF("members"."data" ->> 'location', '')
Enter fullscreen mode Выйти из полноэкранного режима

Поскольку выражения ORDER BY должны быть теми же выражениями, которые заданы в списке SELECT, нам нужно убедиться, что оператор NULLIF присутствует в предложении ORDER BY.

Для этого давайте обновим наш Location ransacker на модели Member:

class Member < ApplicationRecord
    # ...
    ransacker :location do |parent|
            Arel::Nodes::NamedFunction.new 'NULLIF',
                                   [
                                     Arel::Nodes::InfixOperation.new(
                                       '->>',
                                       parent.table[:data],
                                       Arel::Nodes::Quoted.new('location')
                                     ),
                                     Arel::Nodes::Quoted.new('')
                                   ]
        end

    #...
end
Войти в полноэкранный режим Выход из полноэкранного режима

Здесь мы берем код из метода null_if и используем его в явном виде в нашем рансакере в модели Member.

Метод null_if принимает два аргумента: column (объект InfixOperation) и value (объект Quoted).

Добавление обертки null_if к рансакеру Location позволяет нам упорядочивать по тому же аргументу NULLIF(…), который мы указали в операторе SELECT (через наш запрос Ransack).

Вот как выглядит полученный запрос:

SELECT DISTINCT "members".*, NULLIF("members"."data" ->> 'location', ")
FROM "members"
ORDER BY NULLIF("members"."data" ->> 'location', '') ASC NULLS LAST
Войти в полноэкранный режим Выйти из полноэкранного режима

И вот оно! В результате сортировки по местоположению значения пустой строки помещаются в конец списка, независимо от направления сортировки.

Сортировка по местоположению — по алфавиту

Сортировка по местоположению — обратно-алфавитная

Заключение

В этой статье мы рассмотрели, как установить значения пустых строк в столбце JSONB как нулевые значения при использовании гема Ransack для поиска и сортировки данных.

Надеюсь, это поможет вам во всех ваших начинаниях по сортировке таблиц с использованием Ransack, вложенных атрибутов JSONB и нулевых значений!


Спасибо @hadees на GitHub за полезный гист Arel, который предоставил метод null_if, использованный нами в этой заметке.

Оцените статью
devanswers.ru
Добавить комментарий