Right joins in JQPL

Support
#1

I have a model that has Material → Material Properties, where Material and MaterialProperty are JPA entites.

My problem is that I want to display all the materials and all of their properties in a table, but in a way that the material is printed 10 times, on 10 different rows, if it has 10 properties (but with a different property on each row). Also, if there are any Materials without any properties, I still want the Material printed

For sake of simplicity:
Material 1 - property 1.1
Material 1 - property 1.2
Material 1 - property 1.3
Material 2 - property 2.1
Material 2 - property 2.2
Material 3 - -

I found a way to sort of do this, by starting the query from the property side, and joining to the materials, but it makes it very difficult to print the Materials that don’t have any properties. Ideally I would be able to start the query by selecting the material and joining it to the properties, using a right join!

Is this possible in JPQL?

#3

Hello Jamie!

Right join is not supported, but you can achieve such representation by using key-value container and left join, e.g.:

<!-- ... -->
    <data>
        <keyValueCollection id="materialsDc">
            <loader id="materialsDl">
                <query>
                    <![CDATA[select e.name, p.name, p.value from Material e left join e.properties p]]>
                </query>
            </loader>
            <properties>
                <property name="materialName" datatype="string"/>
                <property name="propertyName" datatype="string"/>
                <property name="propertyValue" datatype="string"/>
            </properties>
        </keyValueCollection>
    </data>
<!-- ... -->
        <dataGrid id="materialsDataGrid"
                  width="100%"
                  minHeight="20em"
                  dataContainer="materialsDc"
                  columnReorderingAllowed="true">
            <actions/>
            <columns resizable="true">
                <column property="materialName" header="Material Name"/>
                <column property="propertyName" header="Property Name"/>
                <column property="propertyValue" header="Property Value"/>
            </columns>
        </dataGrid>

Please, see the next quick example: d4960.zip (108.1 KB)

Regards,
Dmitry

#4

Thank you, that worked perfectly!

However, is it possible to use a fetch plan, or to force associated entities to be loaded?

I have a method that takes in a Material object, and then internally loads up various other linked entities (that I didn’t mention in my original post).

However, doing material.getMaterialKit() gives a “Cannot get unfetched attribute [materialKit] from detached object at.magenta.tau.taup.custommaterials.entity.Material”

Even if I manually do material.setMaterialKit(keyValue.getValue(“materialKit”)), then I get a similar error, as the lombok setter throws an error.

Is there a way around this?

#5

Hello Jamie!

FetchPlan cannot be specified for Key-Value entity, but you can reload entity manually to fetch it according to your needs. E.g. for property <property name="material" class="com.company.d4960.entity.Material"/> of keyValueCollection it will look like:

Material reloaded = dataManager.load(Id.of((Material) keyValueEntity.getValue("material")))
        .fetchPlan(builder -> {
            builder.add("materialKit", FetchPlan.BASE)
                   .add("anotherRequiredAttribute", FetchPlan.INSTANCE_NAME);
        })
        .one();

As for unfetched attribute exception, thank you for noticing the problem, I’ve created the issue to fix it. Until that, please reload manually entities returned as values of KeyValueEntity.

P.S. Just in case, it is not recommended to use Lombok with Jpa entities.

Regards,
Dmitry