McCBlog: February 2022

I recently had a requirement for an advanced search/filter mechanism for an entity in a JSF application outside the scope of the relevant database entity's regular attributes. It required a recursive query, as the database table represents a hierarchy of the items that it contains. Our database backend is mysql/mariadb 8.

I decided to use a mysql stored function for the recursive query because I wanted to use the query from arbitrary SQL in contexts than the original requirement. I have used recursive queries or stored functions in mysql before, so I just wanted to document my approach here for future reference.

I found the following links helpful:

The recursive part of the query looks like this:

WITH RECURSIVE ancestors as (

SELECT parent_item_id FROM item_element WHERE parent_item_id in (

SELECT parent_item_id FROM item_element WHERE id in (

SELECT first_item_element_id

FROM item_element_relationship ier

WHERE ier.relationship_type_id = 4 AND

second_item_element_id = item_self_element_id))

UNION

SELECT ie.parent_item_id

FROM item_element ie, ancestors AS a

WHERE ie.contained_item_id1 = a.parent_item_id

) SELECT count(i.name)

INTO row_count

FROM item i, ancestors

WHERE i.id = ancestors.parent_item_id and i.name like name_filter_value;

The first part of the query (before the union) is the "anchor". It is executed first and selects the elements that for the cable's direct endpoint machine design items. The "recursive part" (after the union) is executed repeatedly until it returns no new data.

The function accepts parameters for the name filter value, and an optional specification of cable end for constraining the result to one end of the cable or the other. Example queries look like this:

-- don't constrain cable end, matches any name against any device ancestor
select * from item where domain_id = 9 and cable_design_ancestor_filter(id, 'DLMA', '');

-- constrain matches by cable end (1 or 2)
select *
from item
where domain_id = 9
and cable_design_ancestor_filter(id, 'Rack 01-01', '1')
and cable_design_ancestor_filter(id, 'DLMA', '2');

Also worth documenting here is the mechanism for calling a stored function from JPA. This is done by using "FUNCTION()" in the SQL statement, as is shown in these two named query examples:

@NamedQueries({
@NamedQuery(name = "ItemDomainCableDesign.filterAncestorAny",
query = "SELECT i FROM Item i WHERE i.domain.name = :domainName AND FUNCTION('cable_design_ancestor_filter', i.id, :nameFilterValue, '')"),
@NamedQuery(name = "ItemDomainCableDesign.filterAncestorByEnd",
query = "SELECT i FROM Item i WHERE i.domain.name = :domainName AND FUNCTION('cable_design_ancestor_filter', i.id, :end1Value, '1') AND FUNCTION('cable_design_ancestor_filter', i.id, :end2Value, '2')")
})

Here is the full code of the stored function. One thing I tried to avoid was that there are two separate recursive queries for the conditional cases where cable end either is or is not specified. I was unable to find a solution using a stored procedure or by concatenating strings to form the query, but I'm guessing there must be a way...

DROP FUNCTION IF EXISTS cable_design_ancestor_filter//
CREATE FUNCTION cable_design_ancestor_filter
(cable_item_id INT,
name_filter_value VARCHAR(64),
cable_end VARCHAR(64))
RETURNS BOOLEAN
BEGIN
DECLARE row_count INT;
DECLARE item_self_element_id INT;

-- check that cable_item_id is specified
IF ISNULL(cable_item_id)
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'cable item id must be specified';
END IF;

-- add % to both ends of filter value
IF ISNULL(name_filter_value) OR name_filter_value = ''
THEN
RETURN true;
END IF;
SET name_filter_value = CONCAT('%', name_filter_value);
SET name_filter_value = CONCAT(name_filter_value, '%');

-- get self element id for cable design item
SELECT self_element_id INTO item_self_element_id FROM v_item_self_element WHERE item_id = cable_item_id;

-- check if any endpoint or its ancestors match the filter using recursive query
-- limit relationship elements to cable relationship type and specified cable end
IF ISNULL(cable_end) OR cable_end = ''
THEN
-- cable end not specified
WITH RECURSIVE ancestors as (
SELECT parent_item_id FROM item_element WHERE parent_item_id in (
SELECT parent_item_id FROM item_element WHERE id in (
SELECT first_item_element_id
FROM item_element_relationship ier
WHERE ier.relationship_type_id = 4 AND
second_item_element_id = item_self_element_id))

UNION

SELECT ie.parent_item_id
FROM item_element ie, ancestors AS a
WHERE ie.contained_item_id1 = a.parent_item_id

) SELECT count(i.name)
INTO row_count
FROM item i, ancestors
WHERE i.id = ancestors.parent_item_id and i.name like name_filter_value;
ELSE
-- cable end specified
WITH RECURSIVE ancestors as (
SELECT parent_item_id FROM item_element WHERE parent_item_id in (
SELECT parent_item_id FROM item_element WHERE id in (
SELECT first_item_element_id
FROM item_element_relationship ier,
item_element_relationship_property ierp,
property_value pv
WHERE ier.relationship_type_id = 4 AND
second_item_element_id = item_self_element_id AND
ierp.item_element_relationship_id = ier.id AND
pv.id = ierp.property_value_id AND
pv.value = cable_end))

UNION

SELECT ie.parent_item_id
FROM item_element ie, ancestors AS a
WHERE ie.contained_item_id1 = a.parent_item_id

) SELECT count(i.name)
INTO row_count
FROM item i, ancestors
WHERE i.id = ancestors.parent_item_id and i.name like name_filter_value;
END IF;

RETURN row_count > 0;
END//

McCBlog

Friday, February 25, 2022

mysql stored function with recursive query

About Me