Records are not being filtered in the associated entity

Support
#1

Hello

I am facing the following issue that associated table is not being filtered when used in join in a hierarchical data source.

Could you please advice me what I am doing incorrectly or maybe there is a bug in CUBA platform. Tried this on 6.5.3 and the snapshot version.

I have attached a simplified sample project to demonstrate this.

What I want to achieve is to build localized side tree menu with search capabilities.

So I have two entities:

  • Navigation - this is the hierarchy
  • NavigationTranslation - this is translations for multiple languages for each node in the Navigation table.

Navigation has ONE-TO-MANY relationship to NavigationTranslation by translations association field. NavigationTranslation has MANY-TO-ONE relationship to Navigation by navigation column.

I also created a view for Navigation which includes also the parent and also the translations
I use it in my test screen.

Also, I extended the main window to store my current language in a session variable “language”
Then I created a Access Group constraint to limit Nabvigationtranslation entity by the language which has both in database and in memory values defined.

my datasource in the screen is as follows:

select e from dataproblem$Navigation e INNER JOIN e.translations t WHERE t.language = :session$language

There are for records in NavigationTranslation table - 2 for English language and two for Russian.

When I look at translations table via Entity Inspector it show properly two records filtered for the active language.

But in the test screen language filter is ignored, and is not applied nor from query in the screen datasource, nor through access group constraint. For each node cuba tries to concatenate two ranslations, but shows internal representation, but not a translations.caption field, as it is set in the tree properties. I even can not get the entity name property working.

public://attachments/faa6dff78bfb92f58543476dcb0050b4.png

If I look at the query log which is sent to the database, I see, that the first query is correct (there is a filter by language)

SELECT LIMIT ? ? t1.ID AS a1, t1.DELETE_TS AS a2, t1.DELETED_BY AS a3, t1.NAME AS a4, t1.VERSION AS a5, t1.PARENT_ID AS a6 FROM DATAPROBLEM_NAVIGATION_TRANSLATION t0, DATAPROBLEM_NAVIGATION t1 WHERE (((<b>t0.LANGUAGE_ = ?</b>) AND (t1.DELETE_TS IS NULL)) AND (((t0.NAVIGATION_ID = t1.ID) AND (t0.DELETE_TS IS NULL)) AND (t0.DELETE_TS IS NULL)))

But this is followed by the two additional queries and the one for translations does not have the language ID filter

SELECT ID, CAPTION, DELETE_TS, DELETED_BY, LANGUAGE_, VERSION, NAVIGATION_ID FROM DATAPROBLEM_NAVIGATION_TRANSLATION WHERE ((NAVIGATION_ID IN (?,?)) AND (DELETE_TS IS NULL))
SELECT ID, DELETE_TS, DELETED_BY, NAME, VERSION FROM DATAPROBLEM_NAVIGATION WHERE ((ID IN (?)) AND (0=0))

Also don’t know, why the translations.caption property does not work, while I don’t receive any error messages
Please see the demo project attached (including logs and database).

DataProblem.zip (343.0K)

faa6dff78bfb92f58543476dcb0050b4

#2

Hi Darius,

You are trying to display a property of the nested collection - it doesn’t work. You should flatten the structure somehow, for example using a ValueDatasource with the query returning separate attributes:

select e.id, e.parent, t.caption from dataproblem$Navigation e 
INNER JOIN e.translations t 
WHERE t.language = :session$language

See the fixed project attached.

DataProblem.zip (40.4K)

#3

Hi Konstantin

This worked fine. Thank you very much.

I also would like to implement a search functionality for this tree view. And because the tree now uses KeyValueDataSource, SearchPickerField won’t accept such fields for the property value.
Ideally, I would like some suggestions to popup if possible, when the user starts typing.

What would you suggest for the implementation? Can I do this with a textbox and a button?

Thanks.