ValueCollectionDatasource and Lazy loaded attributes


I’ve got some trouble, using a ValueCollection as Datasource for my screen.
Let me describe the Scenario:

*Object A
** Image
** other attributes

*Object B
** Many_to_one ObjectA
** other attributes

The datasource query looks like:
select b.objectA, b.attr, count(b.attr2)... from ObjectB b where b.year = 2018 ... GROUP BY b.objectA

That’s working fine.
But as soon as I try to access ObjectA.image I get a lazy initialization error.
Caused by: org.eclipse.persistence.exceptions.ValidationException: Exception Description: An attempt was made to traverse a relationship using indirection that had a null Session. This often occurs when an entity with an uninstantiated LAZY relationship is serialized and that relationship is traversed after serialization. To avoid this issue, instantiate the LAZY relationship prior to serialization.

I tried to left join the ObjectA, works for the normal query, but access to ObjectA.image is not possible.
Since it’s a valueCollection, I tried to add ObjectA.image to the select statement, but it gives
java.sql.SQLSyntaxErrorException: expression not in aggregate or GROUP BY columns: T2.ID (where T2.ID is image.ID)

I thought of adding a view to the ValueCollectionDS. So I created a view for ObjectB, containing ALL fields. But… It’s not possible to add the view to the VCDS via cuba Studio UI; I added it manually to the XML definition, but it doesn’t affect the behaviour.

So, long story short.
Is it possible to add a view to a ValueCollectionDataSource?
Or is it possible to retrieve the image via join? How? I even tried joining objectA.image, results in an empty resultset.
Or any other ideas to achieve my goal? (objectb data in a table, grouped by referenced objectA, showing the image of objectA, having some calculations performed in the query (therefore the VCDS and not a normal CollectionDS)

Thank you very much,
Kind regards,

Hi Martin,

Value datasources do not support views.
You can ensure the reference attribute is loaded by specifying join fetch clause which is a standard thing in JPQL:

select b.objectA, b.attr, count(b.attr2)... 
from ObjectB b left join fetch b.objectA.image where ...

Or add b.objectA.image to the select clause, but then you have to add it also to group by:

select b.objectA, b.objectA.image, b.attr, count(b.attr2)... from ObjectB b ... 
GROUP BY b.objectA, b.objectA.image, b.attr
1 Like

Hi Konstantin,

thank you very much! I missed the FETCH, only did a join without fetch. with fetch it is working.
Sorry for this stupid issue, I’m not working in the Java or Web environment, this is just a side project so I’m missing many things that are basics for you and I might oversee simple solutions :slight_smile: .

Thank you,
Kind regards,