Блог

Главная - Вопросы по программированию - Найдите объект в указанных категориях. HQL / MYSQL

Найдите объект в указанных категориях. HQL / MYSQL

#mysql #spring-boot #hibernate #jpa #hql

Вопрос:

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

Моя цель-выбрать топ-5 наиболее подходящих товаров для данных категорий.

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

 @Query("SELECT p FROM Product p JOIN p.categories c WHERE c.name IN (:categories) ORDER BY SIZE(p.categories) DESC")
    List<Product> findByCategories_NameInOrderByCount(@Param("categories") Collection<String> categories);

Как я могу улучшить свой запрос, чтобы он первым возвращал товары с наибольшим количеством категорий, соответствующих названию?

Ответ №1:

Ваш запрос неверен, вы должны использовать что-то подобное этому:

 @Query("SELECT p FROM Product p WHERE (SELECT COUNT(*) FROM p.categories c WHERE c.name IN (:categories)) > 0 ORDER BY (SELECT COUNT(*) FROM p.categories c WHERE c.name IN (:categories)) DESC")
List<Product> findByCategories_NameInOrderByCount(@Param("categories") 
Collection<String> categories);

В любом случае, это, вероятно, также не будет работать очень хорошо, потому что базе данных придется объединить и отсортировать три таблицы. Чтобы избежать этого, обычно используется инвертированный или многозначный индекс, как его называет MySQL (https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued). Удалите таблицы product_has_category и category и вместо этого введите столбец JSON «сведения» в таблицу продуктов.

Затем вы можете создать индекс:

 CREAT INDEX categories ON product( (CAST(JSON_EXTRACT(categories, '$[*]') AS VARCHAR(80) ARRAY)) )

И запросите его вот так:

 @Query("SELECT p FROM Product p WHERE FUNCTION('JSON_OVERLAPS', FUNCTION('JSON_EXTRACT', p.categories, '$[*]'), :categories) = 1 ORDER BY FUNCTION('COUNT_MATCHES', p.categories, :categories) DESC")
List<Product> findByCategories_NameInOrderByCount(@Param("categories") 
String categories);

Но вам нужно добавить функцию в свою базу данных:

 CREATE FUNCTION COUNT_MATCHES (categories JSON, searchCategories JSON)
    RETURNS INT DETERMINISTIC
   RETURN (
     SELECT COUNT(*) 
     FROM JSON_TABLE(categories, '$[*]' COLUMNS(
       name VARCHAR(80) PATH '

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

1. Большое вам спасибо, я долго страдал.

ERROR ON ERROR)
) c
JOIN JSON_TABLE(searchCategories, '$[*]' COLUMNS(
name VARCHAR(80) PATH '

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

1. Большое вам спасибо, я долго страдал.

ERROR ON ERROR )
) s ON c.name = s.name
);

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

1. Большое вам спасибо, я долго страдал.

Метки: