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:
-- don't constrain cable end, matches any name against any device ancestorselect * from item where domain_id = 9 and cable_design_ancestor_filter(id, 'DLMA', '');-- constrain matches by cable end (1 or 2)select *from itemwhere domain_id = 9and 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')")
})
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 BOOLEANBEGINDECLARE row_count INT;DECLARE item_self_element_id INT;-- check that cable_item_id is specifiedIF ISNULL(cable_item_id)THENSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = 'cable item id must be specified';END IF;-- add % to both ends of filter valueIF ISNULL(name_filter_value) OR name_filter_value = ''THENRETURN 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 itemSELECT 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 endIF ISNULL(cable_end) OR cable_end = ''THEN-- cable end not specifiedWITH 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_idFROM item_element_relationship ierWHERE ier.relationship_type_id = 4 ANDsecond_item_element_id = item_self_element_id))UNIONSELECT ie.parent_item_idFROM item_element ie, ancestors AS aWHERE ie.contained_item_id1 = a.parent_item_id) SELECT count(i.name)INTO row_countFROM item i, ancestorsWHERE i.id = ancestors.parent_item_id and i.name like name_filter_value;ELSE-- cable end specifiedWITH 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_idFROM item_element_relationship ier,item_element_relationship_property ierp,property_value pvWHERE ier.relationship_type_id = 4 ANDsecond_item_element_id = item_self_element_id ANDierp.item_element_relationship_id = ier.id ANDpv.id = ierp.property_value_id ANDpv.value = cable_end))UNIONSELECT ie.parent_item_idFROM item_element ie, ancestors AS aWHERE ie.contained_item_id1 = a.parent_item_id) SELECT count(i.name)INTO row_countFROM item i, ancestorsWHERE i.id = ancestors.parent_item_id and i.name like name_filter_value;END IF;RETURN row_count > 0;END//
No comments:
Post a Comment