Querying Over Self-References

Introduction

Sometimes, you want to create a more generic domain model to allow more flexibility in the type and structure of your data. In this case, you often turn to using inheritance or self references to allow for simple yet efficiently designed models. This makes building your microflows and application logic much easier, but it can become challenging to query the correct objects: especially when you are using a self-reference.

The Example

This example is for an implementation of folders on a computer, where one folder can contain several subfolders.

To implement this, a self-reference to Folder is used. The self-reference is an association called SubFolder_Folder. This allows you to build a folder structure with unlimited numbers and levels of folders.

If we create our folder functionality in a module called QueryOver, the association SubFolder_Folder is described in two ways in the domain model:

• Multiplicity: One ‘Folder’ object is associated with multiple ‘Folder’ objects

The Child is the Owner of the association - in other words, the association is always updated through the child.

There are six folders in the example above, and the database is structured and the attributes filled as shown below. In the SubFolder_Folder table, the ChildFolderID is shown on the left as it is the owner of the association.

For more information on how domain models are implemented in databases, see the Implementation section of Domain Model.

Retrieving the SubFolder (or SubFolders) (Children) from a Folder (Parent)

If you have the $ChosenFolder object available in your microflow you can easily retrieve the subfolder (or subfolders). Each association has a right side (parent in the association) and a left side (child or owner of the association). The platform reads each association and determines whether the parent is equal to the $ChosenFolder.

This is implemented using the following XPath constraint: [QueryOver.SubFolder_Folder=$ChosenFolder]. The XPath constraint is read from right to left, with the resulting Folder (or Folders) being the result. This is key to how you should interpret which direction you are following the association.

If the $ChosenFolder object has Code 202002141355334 and Name SubFolder2 we have chosen the folder with ID 3. The two folders in the left-hand table, highlighted in orange, will be returned. The platform applies the constraint by default on the right/parent side of the association and returns the relevant ChildFolder (or ChildFolders).

Retrieving the Parent Folder from a Folder

When you have the $ChosenFolder object available and you want to retrieve its ParentFolder (the folder next higher in the hierarchy, for example given SubFolder2 you want to retrieve MainFolder) from the database, it becomes more complicated.

Use the expression [reversed ()] to instruct Mendix to read the constraint in the reverse direction to that which it would normally use.

In our example, we want to find the folder which is the parent of $ChosenFolder. Now, the query becomes [QueryOver.SubFolder_Folder [reversed ()]=$ChosenFolder]. Instead of reading the association from right to left (Parent to Child), the association is read from left to right.

If the $ChosenFolder object has Code 202002141355334 and Name SubFolder2 we have chosen the folder with ID 3. The folder in the right-hand table, highlighted in orange, will be returned. The platform applies the constraint in reverse, on the left/child side of the association and returns the relevant ParentFolder.

Here is a video created by our community member Mike Kumpf explaining the use of reversed() in an expression.

Creating More Complex Queries

The previous example was a simple one. However the [reversed()] expression can be used in more complicated queries.

Say, for example, that each folder can contain multiple files, associated with the folder over the association File_Folder.

You want to retrieve all the files in the parent folder of the folder object $ChosenFolder.

Use the constraint [QueryOver.File_Folder/QueryOver.Folder/QueryOver.SubFolder_Folder [reversed ()]=$ChosenFolder] to return all the File objects associated with the Folder which is associated (as parent) with the Folder which is the same as $ChosenFolder.

If the $ChosenFolder object is SubFolder2, you will retrieve all the File objects associated with MainFolder over the association File_Folder.

Associations to Specializations

In the special case of self-reference when a one-to-many association is with a specialization of itself, you cannot retrieve by association.

Here is an example inheritance:

In this example, a list of Specializations cannot be retrieved when using a standard by-association retrieve in a microflow if the input is the specialization.

However, there is a workaround for this limitation: The list of Specializations can be retrieved with a Java action using the Java API. This Java action needs two parameters: the Specialization and a Boolean Reverse via this code snippet:

public class RetrieveAsAssociatedWithB extends CustomJavaAction<java.util.List<IMendixObject>>
{
	private IMendixObject __B;
	private main.proxies.Specialization B;
	private java.lang.Boolean Reverse;

	public RetrieveAsAssociatedWithB(IContext context, IMendixObject B, java.lang.Boolean Reverse)
	{
		super(context);
		this.__B = B;
		this.Reverse = Reverse;
	}

	@java.lang.Override
	public java.util.List<IMendixObject> executeAction() throws Exception
	{
		this.B = __B == null ? null : main.proxies.Specialization.initialize(getContext(), __B);
 
		// BEGIN USER CODE
		return Core.retrieveByPath(getContext(), __B, "Main.Generalization_Specialization", Reverse);
		// END USER CODE
	}
}

When setting the Reverse Boolean to true and using the Specialization object as the input, the returned list will contain all the generalizations associated to the specialization.