Wednesday, August 1, 2018

Demystifying Liferay categories: leftCategoryId / rightCategoryId

Liferay allows site administrators to define a "vocabulary" containing categories such as:

  • Cheese
  • Bread
  • Butter

Web content can then be classified using these categories, for instance a news article about canapé would be assigned the categories "Cheese" and "Bread".

Create a Liferay page, put a "Category Navigation" application and an "Asset Publisher" on it, link them and voilà! You have a nice page where relevant articles appear depending on the category you clicked.

Categories can have sub-categories:




Left and right


When you click on a category, you will notice this parameter in the URL: categoryId=21434

You can inspect this category by putting it in the "parentCategoryId" field of this Liferay API page:
https://yourserver/api/jsonws?signature=%2Fassetcategory%2Fget-child-categories-1-parentCategoryId#serviceResults

You will notice that each category has a "leftCategoryId" and a "rightCategoryId" field. What are those!?

They help Liferay easily display items in the category navigation widget. Here is how they are stored in database:

mysql> SELECT leftCategoryId, rightCategoryId, name FROM AssetCategory WHERE vocabularyId=21431 ORDER BY leftCategoryId;
+----------------+-----------------+-----------+
| leftCategoryId | rightCategoryId | name      |
+----------------+-----------------+-----------+
|              2 |               7 | Cheese    |
|              3 |               4 | Camembert |
|              5 |               6 | Roquefort |
|              8 |               9 | Bread     |
|             34 |              35 | Butter    |
+----------------+-----------------+-----------+
5 rows in set (0.00 sec)

When you click on a category, for instance "Cheese", Liferay will use the category identifier to get the Cheese row, and get the left and right, here "2 and "7". Liferay will then use these identifiers in the following SQL request:

SELECT categoryId, name FROM AssetCategory WHERE (groupId = ___) AND (leftCategoryId BETWEEN ___ AND ___);

... where the first blank is the Liferay site identifier, and the second and third are the first and last categories of the vocabulary, in our example 2 and 7. Let's run it:

mysql> SELECT categoryId, name FROM AssetCategory WHERE (groupId = 20181) AND (leftCategoryId BETWEEN 2 AND 7);
+------------+-----------+
| categoryId | name      |
+------------+-----------+
|      21433 | Cheese    |
|      21703 | Camembert |
|      21704 | Roquefort |
+------------+-----------+
3 rows in set (0.01 sec)

Voilà! You get all of the categories whose articles should appear on the page when you click "Cheese".

You might ask: Why not just use parentId to get all categories whose parent is the Cheese categories, and then recursively their own sub-categories using the same technique?
Answer: It would not scale. For vocabularies with n levels of sub-categories, the recursion would trigger O(n!) SQL requests, whereas the left/right strategy uses O(1) SQL requests. This technique is called nested set model. Its drawback is that left/right information must be rebuilt every time an administrator moves or deletes a category.



Applicable to Liferay 6.2 and Liferay 7.0
Nicolas Raoul