33 lines
980 B
SQL
33 lines
980 B
SQL
DELIMITER ;;
|
|
|
|
DROP FUNCTION IF EXISTS count_matching_tags ;;
|
|
CREATE FUNCTION count_matching_tags (haystack TEXT, tags JSON) RETURNS INT DETERMINISTIC
|
|
BEGIN
|
|
DECLARE matches INT;
|
|
DECLARE idx INT;
|
|
SET matches = 0;
|
|
SET idx = 0;
|
|
WHILE idx < JSON_LENGTH(tags) DO
|
|
SET matches = IF (INSTR(haystack, JSON_UNQUOTE(JSON_EXTRACT(tags, CONCAT('$[', idx, ']')))), matches + 1, matches);
|
|
SET idx = idx + 1;
|
|
END WHILE;
|
|
RETURN matches;
|
|
END ;;
|
|
|
|
|
|
DROP FUNCTION IF EXISTS get_matching_league ;;
|
|
CREATE FUNCTION get_matching_league (haystack TEXT, id_sport INT) RETURNS INT DETERMINISTIC
|
|
BEGIN
|
|
DECLARE lid INT;
|
|
SELECT id
|
|
INTO lid
|
|
FROM leagues
|
|
WHERE (
|
|
leagues.id_sport = id_sport AND
|
|
JSON_TYPE(leagues.tags) = 'ARRAY' AND
|
|
count_matching_tags(haystack, leagues.tags) > 0
|
|
)
|
|
ORDER BY degree ASC
|
|
LIMIT 1;
|
|
RETURN lid;
|
|
END ;; |