-
Notifications
You must be signed in to change notification settings - Fork 19
adding advanced filter display mode support for a CDB domain
We recently added a new "advanced filter" display mode feature that allows the user to filter the CDB list view for a domain by dimensions that are not direct attributes of that domain (e.g., the regular list view columns). For example, in the cable design domain, there is a requirement to filter the cable list by the names of the parents of the endpoint machine devices.
Advanced filter display mode allows a domain to specify one or more filters. Each filter includes one or more parameters. When the domain specifies filter information, a new "advanced filter" menu item is included in the "display mode" dialog menu. Selecting "advanced filter" from the menu displays a list of filters available for the domain. Selecting a specific filter displays a panel with input text fields for the filter parameters.
The cable design domain includes two filters, "Endpoint Ancestor Name" and "End1/2 Endpoint Ancestor Name". The first matches includes a single parameter "Ancestor Name Contains" whose value is matched against the names of parent machine design items of all endpoint machine design devices connected to either end of the cable. The second filter includes two parameters "End1 Ancestor Name Contains" and "End2 Ancestor Name Contains". It matches the parents of the devices connected to the specified cable end.
Here is an example of the display mode dialog for the cable design domain's "End1/2 Endpont Ancestor Name" filter: https://drive.google.com/file/d/1YHjtIX64hn7_Z81aTdWtgmQzYvph9xz2/view?usp=sharing
Clicking the dialog's "Done" button processes the filter displays the results in the normal list view. The list view's "Display Mode" button label is updated with the filter name and parameter values when using "Advanced Filter" mode, as shown in the screenshot below: https://drive.google.com/file/d/1YpzFrECYwGFPKAH-YK3JQx-1obmyVqAG/view?usp=sharing
Framework support is provided by the display mode dialog views, and java classes AdvancedFilter, AdvancedFilterParameter, ItemController, and ItemFacadeBase.
- contains the information for a domain-specific filter including name, description, and list of AdvancedFilterParameter objects
- includes reference to ItemController so that it can be notified when a parameter value changes
- contains the information about a filter parameter including name, description, and value
- includes reference to its parent filter object so that it can be notified when a parameter value changes
- initializeAdvancedFilterInfo(controller) - returns list of AdvancedFilter objects for domain, initialized with reference to ItemController object
- processAdvancedFilter(name, parameterValueMap) - processes the specified filter and returns list of ItemDomainEntity (e.g., the contents of the list view)
- getAdvancedFilters() - returns list of filters obtained by ItemFacadeBase.initializeAdvancedFilterInfo, saved in variable advancedFilters
- getAdvancedFilterName() - serves as model for filter name selected in display mode dialog's advanced filter menu
- getSelectedFilter() - returns the AdvancedFilter object corresponding to the name selected in the dialog's advanced filter menu
- advancedFilterChanged() - callback method for notification from an AdvancedFilter object that a parameter value changed
- createAdvancedFilterDataModel() - called by getScopedListDataModel(), creates ListDataModel by applying the filter with details captured in the display mode dialog; creates map of parameter name/value and calls createAdvancedFilterDataModel_()
- createAdvancedFilterDataModel_() - allows controller subclasses to customize the generation of the ListDataModel for the specified filter, default delegates to facade framework ItemFacadeBase.processAdvancedFilter()
- includes panel with menu for selecting filter name from domain's list of AdvancedFilter objects
- includes panel for capturing filter parameter values via input fields, using forEach loop over selected AdvancedFilter object's list of parameters
- controller.advancedFilterName is model for filter name menu
- value variable of AdvancedFilterParameter object for the selected AdvancedFilter is the value model for the corresponding inputText component
The requirements to enable advanced filter display mode support for a new domain are fairly simple:
- override ItemFacadeBase.initializeAdvancedFilterInfo() to return list of AdvancedFilter objects for domain
- override ItemFacadeBase.processAdvancedFilter() (or createAdvancedFilterDataModel_()) to process specified filter and return list of items to display in list view
This method simply returns a list of AdvancedFilter objects for the corresponding domain. Here is an example from ItemDomainCableDesignFacade:
@Override
public List<AdvancedFilter> initializeAdvancedFilterInfo(ItemController controller) {
List<AdvancedFilter> filters = new ArrayList<>();
AdvancedFilter filter = new AdvancedFilter(
FILTER_NAME_ANCESTOR_ANY,
"Machine design parents of cable endpoints",
controller);
filter.addParameter(
FILTER_PARAM_ANCESTOR_NAME,
"Substring to match in machine hierarchy for endpoint devices");
filters.add(filter);
filter = new AdvancedFilter(
FILTER_NAME_ANCESTOR_BY_END,
"Machine design parents of cable endpoints constrained to cable end",
controller);
filter.addParameter(
FILTER_PARAM_ANCESTOR_NAME_END1,
"Substring to match in machine hierarchy for endpoint devices on end 1");
filter.addParameter(
FILTER_PARAM_ANCESTOR_NAME_END2,
"Substring to match in machine hierarchy for endpoint devices on end 2");
filters.add(filter);
return filters;
}
The list includes two filters, each of which specifies the parameters it supports. Filter and parameter names are defined as class constants so that they can be shared with processAdvancedFilter(), which applies the specified filter.
The primary mechanism for applying the filter specified in the display mode dialog is via an override of ItemFacadeBase.processAdvancedFilter() in the domain's facade class. This approach assumes that an SQL query will be used to retrieve the filtered list of items. A secondary approach is to override ItemController.createAdvancedFilterDataModel_(), when some approach other than SQL will be used to retrieve the filtered list of items to display in the domain's list view.
Here is an example override of processAdvancedFilter() from ItemDomainCableDesginFacade:
public List<ItemDomainCableDesign> processAdvancedFilter(String name, Map<String, String> parameterValueMap) {
if (name == null || name.isEmpty()) {
return null;
}
List<ItemDomainCableDesign> result = null;
switch (name) {
case FILTER_NAME_ANCESTOR_ANY:
result = processAdvancedFilterAncestorAny(parameterValueMap.get(FILTER_PARAM_ANCESTOR_NAME));
break;
case FILTER_NAME_ANCESTOR_BY_END:
result = processAdvancedFilterAncestorByEnd(
parameterValueMap.get(FILTER_PARAM_ANCESTOR_NAME_END1),
parameterValueMap.get(FILTER_PARAM_ANCESTOR_NAME_END2));
break;
}
return result;
}
The method parameters include filter name and a map of parameter name/value pairs. In the example above, we simply use a switch statement based on the name of the filter to invoke a facade method that executes an SQL statement with the specified parameters. The facade methods and underlying SQL filter implementation are described in more detail below.
It is useful to think of each domain-specific filter as an SQL statement that returns a filtered list of domain items. In the ItemDomainCableDesign example above, processAdvancedFilter() maps each filter name to an SQL statement using the filter parameter values. For each filter, a facade method is defined that invokes a named SQL query on the ItemDomainCableDesign class. A MySQL stored function was created to execute a recursive query on the machine design hierarchy to find parents of cable endpoint machine design items whose name match the filter parameters. Each of these is described in more detail below.
I wanted to implement the machine design ancestor name filter in a way that could be used in other contexts by arbitrary SQL in addition to executing the query for the advanced filter mechanism. I created a stored SQL function cable_design_ancestor_filter() that executes the recursive SQL query over the machine design hierarchy as implemented in the item_element database table. This function is used by the named query and facade methods described below, but can also be used in arbitrary SQL e.g., "select * from item where domain_id = 9 and cable_design_ancestor_filter(id, 'DLMA', '1');". The full stored function is described in more detail here, and looks like this:
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//
The stored function above is exposed using named queries on the ItemDomainCableDesign class. Here is the named query annotation for the class:
@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')")
})
These named queries invoke the stored function shown above using a JPA function called "FUNCTION()" which accepts the stored function name and parameters as its parameters.
As described above, the facade override of processAdvancedFilter() maps a filter name and parameter values to a facade method that executes the filter query and returns the filtered list of items. The ItemDomainCableDesignFacade defines two such methods which invoke named queries on the ItemDomainCableDesign class. The methods look like this:
public List<ItemDomainCableDesign> processAdvancedFilterAncestorAny(String ancestorName) {
try {
return (List<ItemDomainCableDesign>) em.createNamedQuery("ItemDomainCableDesign.filterAncestorAny")
.setParameter("domainName", getDomainName())
.setParameter("nameFilterValue", ancestorName)
.getResultList();
} catch (NoResultException ex) {
}
return null;
}
public List<ItemDomainCableDesign> processAdvancedFilterAncestorByEnd(
String end1AncestorName, String end2AncestorName) {
try {
return (List<ItemDomainCableDesign>) em.createNamedQuery("ItemDomainCableDesign.filterAncestorByEnd")
.setParameter("domainName", getDomainName())
.setParameter("end1Value", end1AncestorName)
.setParameter("end2Value", end2AncestorName)
.getResultList();
} catch (NoResultException ex) {
}
return null;
}