mysql stored function with recursive query

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:

• https://mariadb.com/kb/en/recursive-common-table-expressions-overview/
• https://www.geeksforgeeks.org/mysql-creating-stored-function/

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//