Problem with filters

Support
#1

hi i am using this filter to find records that has some asociated record:
image

Problem is that not all records are being shown.

if i make same select directly to mysql all records are shown:

SELECT * FROM `DOPRAVA_DOPRAVA`
LEFT JOIN `DOPRAVA_NOTIFIKACIE` `Doprava Notifikacie` ON `DOPRAVA_DOPRAVA`.`ID` = `Doprava Notifikacie`.`DOPRAVA_ID`
WHERE (`Doprava Notifikacie`.`ID`is not null)

Record has been created long time before and it is not visible in output.

#2

Hi.
The expression in the Join section should be started with “join” or “left join” statements.
Example of joining the Repair collection when selecting Car entities:
join {E}.repairs r

Regards,
Natalia

#3

Thank you, but this has no impact on results. Still the same problem.
Difference from direct mysql approach to cuba filters is 573 vs. 563 records.

here is log:
com.haulmont.cuba.core.app.RdbmsStore - loadList: metaClass=doprava_Doprava, view=com.company.doprava.entity.Doprava/doprava-view, query=Query{queryString='select e from doprava_Doprava e order by e.date_of_departure desc', condition=([join: {E}.notifikacie n, where: n.id is not null ]), sort=[date_of_departure: DESC], firstResult=0, maxResults=50}, max=50

Just to mention tables are in ONE_TO_MANY reliations and join is made to MANY records.

#4

@firstova any suggestions?

#5

Which version of MySql do you use? Do you have the same problem with another databases?

#6

Google cloud SQL Mysql 5.7.
I dont think it is problem of database since there are several other softwares performing same query against same database and only cuba gets wrong result.

Maybee there is some form of caching in play internally in cuba or some connection settings?

#7

Try to including distinct in JPQL queries, which ensures the absence of duplicates in the dataset returned from the database. Information about queries with distinct can be found here

#8

Also, try to enable debug eclipselink.sql logging and get the real SQL statement that the application is executed.

#9

@firstova where should i use distinct?
You mean directly in descriptor? Like this:
Because after that i am using just native filters in screen.

<loader id="dopravasDl">
                <query>
                    <![CDATA[
                    select distinct e from doprava_Doprava e order by e.date_of_departure desc
                    ]]>
                </query>
            </loader>