Problem with conditional data load

Support
#1

Hello there.

I am trying to work out how to get this condition to work, but I can’t seem to figure it out. I have a bit of JPQL that looks like this:

<loader id="publicHolidaysDl">
                <query>
                    <![CDATA[select ph from kipper_PublicHoliday ph,
                                kipper_Person p,
                                kipper_PersonPostalAddress ppa,
                                kipper_PostalAddress pa,
                                kipper_Country c]]>
                    <condition>
                        <c:jpql>
                            <c:where>
                                p = :container_personDc
                                and ppa.person = p
                                and ppa.defaultAddress = true
                                and ppa.postalAddress = pa
                                and pa.country.id = c.id
                                and c.id = ph.country.id
                            </c:where>
                        </c:jpql>
                    </condition>
                    <condition>
                        <c:jpql>
                            <c:where>
                                c.id = :country_id
                            </c:where>
                        </c:jpql>
                    </condition>
                </query>
            </loader>

I want the first condition to execute when the page loads (which seems to work okay), but when I change the value in a table, I want the second condition to execute, which it doesn’t do:

    @Subscribe(id = "personPostalAddressesDc", target = Target.DATA_CONTAINER)
    private fun onPersonPostalAddressesDcItemPropertyChange(event: InstanceContainer.ItemPropertyChangeEvent<PersonPostalAddress>) {

        if (event.property == "defaultAddress"
                && event.prevValue == false
                && event.value == true) {

            val country = event.item.postalAddress?.country

            if (country != null) {
                publicHolidaysDl.setParameter("country_id", country.id)
                publicHolidaysDl.load()
            }

        }

    }

But all I get is this error message:

DevelopmentException: Parameter 'country_id' is not used in the query

How do I get it to trigger the second condition?

#2

Hi, place conditions inside a single <condition> element, see the documentation.

#3 
<loader id="publicHolidaysDl">
                <query>
                    <![CDATA[select ph from kipper_PublicHoliday ph,
                                kipper_Person p,
                                kipper_PersonPostalAddress ppa,
                                kipper_PostalAddress pa,
                                kipper_Country c]]>
                    <condition>
                            <c:jpql>
                                <c:where>
                                    p = :container_personDc
                                    and ppa.person = p
                                    and ppa.defaultAddress = true
                                    and ppa.postalAddress = pa
                                    and pa.country.id = c.id
                                    and c.id = ph.country.id
                                </c:where>
                            </c:jpql>
                            <c:jpql>
                                <c:where>c.id = :country_id</c:where>
                            </c:jpql>
                    </condition>
                </query>
            </loader>

This gives me:

IllegalStateException: 'condition' element must have exactly one nested element
#4

Using and/or gives me the wrong result: either the second condition results in a blank table or I get both sets of results – which I think is correct behaviour, even if it’s not what I’m looking for.

When the page loads, I need the holidays populated based on the first criteria (unless there’s some way to get the country.id into the page when it first loads up)

When some other item is clicked, I need the table reloaded with data based on the new country.id.

#5

Hi Ray,

  1. If there are several conditions you have to use either <or> or <and>.
  2. As documentation states:

a condition is included in the query only when its parameters are set

Following to you explanation it’s not clear do you want to include both conditions or disable the first one. In the latter case you can try to achieve it by adding additional parameter which initially is set, and then remove it publicHolidaysDl.removeParameter().

#6

Hello, yes: I was looking to disable the first parameter. I also was missing a piece of the SQL in the second part of the statement:

<loader id="publicHolidaysDl">
                <query>
                    <![CDATA[select ph from kipper_PublicHoliday ph,
                                kipper_Person p,
                                kipper_PersonPostalAddress ppa,
                                kipper_PostalAddress pa,
                                kipper_Country c]]>
                    <condition>
                        <and>
                            <c:jpql>
                                <c:where>
                                    p = :container_personDc
                                    and ppa.person = p
                                    and ppa.defaultAddress = true
                                    and ppa.postalAddress = pa
                                    and pa.country.id = c.id
                                    and c.id = ph.country.id
                                </c:where>
                            </c:jpql>
                            <c:jpql>
                                <c:where>
                                    ph.country.id = c.id
                                    and c.id = :country_id
                                </c:where>
                            </c:jpql>
                        </and>
                    </condition>
                </query>
            </loader>

This one almost works. The page loads correctly, and when I click on the element, the table is updated to carry the new value, but it also leaves two empty rows above where the previous values were:

22
38

I think this is because I am effectively running this expression:

select ph from kipper_PublicHoliday ph,
                                kipper_Person p,
                                kipper_PersonPostalAddress ppa,
                                kipper_PostalAddress pa,
                                kipper_Country c
where 
                               ph.country.id = c.id
                               and c.id = :country_id

The extra entities defined in the select clause are causing a lot of extra rows to be returned because they’re not attached to anything. (I ran it through the JPQL console and this does appear to be the case) Unfortunately, without them, the first clause won’t work!

Phew! Okay, this is way too complicated for what I’m trying to achieve here, so I’ve gone back to something lot more basic.

            <loader id="publicHolidaysDl">
                <query>
                    <![CDATA[select ph from kipper_PublicHoliday ph,
                                kipper_Country country
                              where ph.country = country and country.id = :country_id
                    ]]>
                </query>
            </loader>

is a lot easier on the eye, and then just let the editor code handle the heavy lifting.


    private fun reloadPublicHolidays() {
        val country = personDc.item.postalAddresses?.first { it.defaultAddress == true }?.postalAddress?.country

        country?.let {
            publicHolidaysDl.setParameter("country_id", it.id)
            publicHolidaysDl.load()
        }
    }


…
…

    @Subscribe
    private fun onAfterShow(event: AfterShowEvent) {

 
        reloadPublicHolidays()

    }

…
…

    @Subscribe(id = "personPostalAddressesDc", target = Target.DATA_CONTAINER)
    private fun onPersonPostalAddressesDcItemPropertyChange(event: InstanceContainer.ItemPropertyChangeEvent<PersonPostalAddress>) {

        if (event.property == "defaultAddress"
                && event.prevValue == false
                && event.value == true) {

            reloadPublicHolidays()

        }

    }

Which seems to work and is a lot easier than wrestling with the Dataload coordinator.

#7

Thinking about it a little more, I wonder if this might be a deficiency with the dataload coordinator.

Unless I’m misunderstanding something, the select statement clause cannot always be shared by all the clauses. A set of where clauses might need a different set of entities defined for them.

In my case I probably need something hideous like this to make it work:

<loader id="publicHolidaysDl">
                <query>
                 <select clause="load">
                      select ph from kipper_PublicHoliday ph,
                                kipper_Person p,
                                kipper_PersonPostalAddress ppa,
                                kipper_PostalAddress pa,
                                kipper_Country c
                   </select>
                   <select clause="change">
                      select ph from kipper_PublicHoliday ph,
                                kipper_Country c
                   </select>
                    <condition>
                        <and>
                            <c:jpql>
                                <c:where clause="load">
                                    p = :container_personDc
                                    and ppa.person = p
                                    and ppa.defaultAddress = true
                                    and ppa.postalAddress = pa
                                    and pa.country.id = c.id
                                    and c.id = ph.country.id
                                </c:where>
                            </c:jpql>
                            <c:jpql>
                                <c:where clause="change">
                                    ph.country.id = c.id
                                    and c.id = :country_id
                                </c:where>
                            </c:jpql>
                        </and>
                    </condition>
                </query>
            </loader>
#8

Hi Ray,

I think declarative approach will be always limited to some typical use-cases. It’s hard to create universal mechanism especially for complex cases when some dynamic changes required.

Consider using DataLoader API to setup the query:

publicHolidaysDl.setQuery()
publicHolidaysDl.setCondition()
publicHolidaysDl.setParameters()
#9

Yes, I think you’re right. The connection between the two entities was not direct, so the declaration method was always going to struggle. Just writing it in code was the right solution.

Not to worry; I was just kicking the tyres to see what was possible. Aside from not being able to access the user session, the Dataload Coordinator is fine as it is.