evitaDB - Fast e-commerce database
logo
page-background

Ordering references / by reference attribute

You can sort entities by attributes on references, and you can also sort fetched referenced entities by their attributes or by attributes of references that point to them. Although these are fundamentally different scenarios, both are described in this section.

Reference property

argument:string!

a mandatory name of the reference whose attribute is to be used for the ordering

constraint:(traverseByEntityProperty|pickFirstByEntityProperty)?
optional constraint that specifies the way entities are sorted, see pickFirstByEntityProperty and traverseByEntityProperty constraint documentation for more details
constraint:orderingConstraint+
one or more ordering constraints that specify the ordering by the reference attribute
The referenceProperty is implicit in requirement referenceContent
In the orderBy clause within the referenceContent requirement, the referenceProperty constraint is implicit and must not be repeated. All attribute order constraints in referenceContent automatically refer to the reference attributes, unless the entityProperty container is used there.

Sorting by reference attribute is not as common as sorting by entity attributes, but it allows you to sort entities that are in a particular category or have a particular group specifically by the priority/order for that particular relationship.

To sort products related to a "sale" group by the orderInGroup attribute set on the reference, you need to issue the following query:
The example is based on a simple one-to-zero-or-one reference (a product can have at most one reference to a group entity). The response will only return the products that have a reference to the "sale" group, all of which contain the orderInGroup attribute (since it's marked as a non-nullable attribute). Because the example is so simple, the returned result can be anticipated:
Ordering of 1:N references

Things can get more complicated when the reference is one-to-many. What should you expect when you run a query that involves sorting by a property on a reference attribute? Relational databases allow this, but you need to deal with the problem of row multiplication. With evitaDB, you work with an entity model, so you don't have to worry about the multiplication problem. It's possible, but there are some specifics because evitaDB supports hierarchical entities.

Let's break it down into two cases:

Non-hierarchical entity
If the referenced entity is non-hierarchical and the returned entity references multiple entities, only the reference with the lowest primary key of the referenced entity, while also having the order property set, will be used for ordering. This is the same as if you had used the pickFirstByEntityProperty constraint in your referenceProperty container:
Hierarchical entity
If the referenced entity is hierarchical and the returned entity references multiple entities, the reference used for ordering is the one that contains the order property and is the closest hierarchy node to the root of the filtered hierarchy node.
It sounds complicated, but it's really quite simple. Imagine you're listing products from a category and also sorting them by a property called orderInCategory on the category reference. The first products you get are the ones directly related to the category, in order of orderInCategory. Then you get the products from the first child category, and so on, keeping the category tree's order. This is the same as using the traverseByEntityProperty constraint in your referenceProperty container:
Note:
You can control the behaviour by using the pickFirstByEntityProperty or traverseByEntityProperty constraints in your referenceProperty container. You can also use the traverseByEntityProperty for non-hierarchical entities and for 1:1 references. It changes the order so that the entities are first sorted by the property of the thing they're referring to, and then by the reference property itself. For more information, check out the examples and the detailed documentation of the traverseByEntityProperty constraint.

Pick first by entity property

constraint:orderingConstraint+
one or more ordering constraints that specify the ordering of the references to pick the first one from the list of references to the same entity to be used for ordering by referenceProperty
The pickFirstByEntityProperty ordering constraint can only be used within the referenceProperty ordering constraint. It makes sense only in case the cardinality of the reference is 1:N (although this isn't actively checked by the query engine). This constraint lets you specify the order of the references to pick the first one from the list of references to the same entity to be used for ordering by referenceProperty.

Let's expand our previous example to include products that refer to both the "sale" and "new" groups:

The result will contain the first products from the "new" group, which has the lowest primary key. Then it will contain the products from the "sale" group. The order of products within each group will be determined by the orderInGroup attribute. This is the usual way things are done for references that target non-hierarchical entities.
If we want to change the order of the groups, we can use the pickFirstByEntityProperty ordering constraint to explicitly specify the order of the groups. For example, if we want to list products in the "sale" group first, we can use the following query:

If a product is in both groups, the "sale" group takes priority and is used for ordering. You can use all sorts of ordering constraints and adjust the ordering to suit your needs.

Traverse by entity property

argument:enum(DEPTH_FIRST|BREADTH_FIRST)?
optional argument that specifies the mode of the reference traversal, the default value is DEPTH_FIRST
constraint:orderingConstraint+
one or more ordering constraints that change the traversal order of the references of the ordered entity before the referenceProperty ordering constraint is applied
The traverseByEntityProperty ordering constraint can only be used with the referenceProperty ordering constraint. This means that entities should be sorted first by the referenced entity property. If the entity is hierarchical, you can specify whether the hierarchy should be traversed in depth-first or breadth-first order.

Once the referenced entities are sorted, the order of the referenced entities is applied to the references to the referenced entities. If there are multiple references, only the first one that can be evaluated is used for ordering.

This behaviour is best illustrated by the following example. Let's list products in the 'Accessories' category in order of the orderInCategory attribute on the category reference:
The result will first contain products in the Accessories category, ordered by orderInCategory from most to least, then products in the Christmas electronics category (which is the first child of the Accessories category with the least primary key), then products in the Smart wearable category (which has no directly related products), then products in the Bands category (which is the first child of the Smart wearable category), and so on. The order follows the order of the categories in the following image:
dynamic-tree.png
If a product falls into both the Christmas electronics and Smart wearable categories, it will only be listed once. This is because, in this query, the category's primary key is used to traverse the hierarchy.
Here's another example: we want to list products in the Accessories category in a certain order. This order is based on the orderInCategory attribute on the reference to the category. But we want to go through the hierarchy using breadth first manner, where each hierarchy level should be sorted by category order attribute first:

As you can see, you can arrange the order of the entities and the references within the hierarchy however you want.

Entity property

constraint:orderingConstraint+
one or more ordering constraints that specify the ordering by the referenced entity attributes
The entityProperty ordering constraint can only be used within the referenceContent requirement. It allows to change the context of the reference ordering from attributes of the reference itself to attributes of the entity the reference points to.
In other words, if the Product entity has multiple references to ParameterValue entities, you can sort those references by, for example, the order or name attribute of the ParameterValue entity. Let's see an example:

Entity group property

constraint:orderingConstraint+
one or more ordering constraints that specify the ordering by the referenced entity group attributes
The entityGroupProperty ordering constraint can only be used within the referenceContent requirement. It allows the context of the reference ordering to be changed from attributes of the reference itself to attributes of the group entity within which the reference is aggregated.
In other words, if the Product entity has multiple references to ParameterValue entities that are grouped by their assignment to the Parameter entity, you can sort those references primarily by the name attribute of the grouping entity, and secondarily by the name attribute of the referenced entity. Let's look at an example:

Author: Ing. Jan Novotný

Date updated: 25.6.2023

Documentation Source