recursive query in stored function for filtering cable designs

Dam requested a mechanism for filtering cables on dimensions that are not direct attributes of the cable design domain, in particular he wants to filter by the name of the machine design hierarchy parents of the devices that are cable endpoints. To do this, we decided to use a recursive query on the machine design items.

My goal was to implement this in a way that could be used in other contexts, in addition to this new "advanced filter display mode". To accomplish that, I created a stored function that could be used from the where clause of arbitrary cable design sql queries.

I haven't used recursive queries in mysql/mariadb, so I found the following link helpful:

https://mariadb.com/kb/en/recursive-common-table-expressions-overview/

The recursive part of the query looks like this:

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

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');

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; 

	-- add % to both ends of filter value 
	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//