ListGrid sorting on foreignKey field

[vostapenko]

In two words:

INFO  HibernateDataSource - [builtinApplication.users_fetch] Query string: select _User, department1 from ru.eurotechnologygroup.etgcrm.server.model.User _User left outer join _User.department department1 order by _User.department.name

transforms into incorrect query by HibernateDataSource, which doesn't return records with foreignKey == null (what for is the second join?)

DEBUG SQL - select top 75 user0_.USERID as USERID0_0_, department1_.DID as DID2_1_, user0_.USERBLOCKED as USERBLOC2_0_0_, user0_.USERDATEBIRTH as USERDATE3_0_0_, user0_.DID as DID0_0_, user0_.USEREMAIL as USEREMAIL0_0_, user0_.USERFIRSNAME as USERFIRS5_0_0_, user0_.USERLASTNAME as USERLAST6_0_0_, user0_.USERLOGIN as USERLOGIN0_0_, user0_.USERMASKIP as USERMASKIP0_0_, user0_.USERPASSWORD as USERPASS9_0_0_, user0_.USERPHONE as USERPHONE0_0_, user0_.USERPATRONYMIC as USERPAT11_0_0_, department1_.DNAME as DNAME2_1_ from suser user0_ left outer join department department1_ on user0_.DID=department1_.DID cross join department department2_ where user0_.DID=department2_.DID order by department2_.DNAME

In Detail:
I have a ListGrid with a list of users. There's a department FK field there. When i try to sort data by this field things go bad, listgrid shows records with foreignKey != null and starts to infinitely make tries to reload recordset.


users.ds.xml

<DataSource ID="users" serverType="hibernate" beanClassName="ru.eurotechnologygroup.etgcrm.server.model.User"
            schemaBean="ru.eurotechnologygroup.etgcrm.server.model.User">
    <fields>
      ...
        <field name="departmentName" type="text" hidden="true" valueXPath="department/name"/>
        <field name="department" title="Подразделение" displayField="departmentName"/>
      ...
    </fields>
</DataSource>

User.java

@Entity
@Table(name = "suser")
public class User implements Serializable
{
...
private Department department;
...
@OneToOne(targetEntity = Department.class, fetch = FetchType.EAGER)
    @JoinColumn(name = "DID")
    public Department getDepartment()
    {
        return department;
    }

    public void setDepartment(Department department)
    {
        this.department = department;
    }
}

nothing special about listgrid

        DataSource ds = DataSource.get("users");
        ListGrid listGrid = new ListGrid();
        listGrid.setCanEdit(false);
        listGrid.setDataSource(ds);
        listGrid.setAutoFetchData(true);

Also i have department.ds.xml and Department hibernate bean. Nothing special about them, just id and name fields. And i also tried declaring foreignKey="departments.id" (despite the fact that it should be autoloaded from bean defintition).

Am i missing something? Please help. All Your examples doesn't provide the case where FK can be null.

I can provide full logs on demand.

TY.

[vostapenko]

I once again want to mention, that everything works perfect while there's no records with null foreignkey (User.did). Fetching, sorting - all works.

[alius]

Hi,

First to clarify:

This is a HQL query created by HibernateDataSource. This query looks good to me. Do you agree?

INFO  HibernateDataSource - [builtinApplication.users_fetch] Query string: select _User, department1 from ru.eurotechnologygroup.etgcrm.server.model.User _User left outer join _User.department department1 order by _User.department.name

This query is SQL query and it is created by Hibernate based on HQL query.

DEBUG SQL - select top 75 user0_.USERID as USERID0_0_, department1_.DID as DID2_1_, user0_.USERBLOCKED as USERBLOC2_0_0_, user0_.USERDATEBIRTH as USERDATE3_0_0_, user0_.DID as DID0_0_, user0_.USEREMAIL as USEREMAIL0_0_, user0_.USERFIRSNAME as USERFIRS5_0_0_, user0_.USERLASTNAME as USERLAST6_0_0_, user0_.USERLOGIN as USERLOGIN0_0_, user0_.USERMASKIP as USERMASKIP0_0_, user0_.USERPASSWORD as USERPASS9_0_0_, user0_.USERPHONE as USERPHONE0_0_, user0_.USERPATRONYMIC as USERPAT11_0_0_, department1_.DNAME as DNAME2_1_ from suser user0_ left outer join department department1_ on user0_.DID=department1_.DID cross join department department2_ where user0_.DID=department2_.DID order by department2_.DNAME

So if you think that is incorrect - you should ask this question in Hbernate forums.
At first glance you are right: SQL query is correct but it does not handle null values.

You have provided only User entity but no Department (please make it complete next time) so I can only guess.
I can think of 2 possible cases:
1. Hibernate has a bug;
2. Your mapping is not correct. BTW: I would add nullable=true to your OneToOne annotation on getDepartment().

Regards,
Alius

[vostapenko]

Thanks for your fast reply.

1. Yes, seems like HQL is correct.
2.

Department.java

@Entity
@Table(name = "department")
public class Department implements Serializable
{
    private Long id;
    private String name;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "DID")
    public Long getId()
    {
        return id;
    }

    public void setId(Long id)
    {
        this.id = id;
    }

    @Column(name = "DNAME")
    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }
}

department.ds.xml

<DataSource ID="departments" serverType="hibernate" beanClassName="ru.eurotechnologygroup.etgcrm.server.model.Department"
            schemaBean="ru.eurotechnologygroup.etgcrm.server.model.Department">
    <fields>
        <field name="name" required="true" length="100"/>
    </fields>
</DataSource>

3. Probably that was my mistake to ask this question here, converting HQL to SQL is not SmartGWT Business indeed. Anyway help would be appriciated=)

4.

boolean nullable() default true;

in javax.persistence.JoinColumn, so no need to mention it.

[vostapenko]

Well, seems like i found the cause

SmartGWT generates the following HQL:

1. Without sorting

select _User, department1 from User _User left outer join _User.department department1

2.With sorting by FK

select _User, department1 from User _User left outer join _User.department department1 order by _User.department.name

It results in SQL:

select [fieldslist] from suser user0_ left outer join department department1_ on user0_.DID=department1_.DID cross join department department2_ where user0_.DID=department2_.DID order by department2_.DNAME

While the correct HQL which has to be generated is:

"select _User, department1 from User _User left outer join _User.department department1 order by department1.name

resulting in correct sql:

select [fieldslist] from suser user0_ left outer join department department1_ on user0_.DID=department1_.DID order by department1_.DNAME

So the problem is in SmartGwt generating incorrect HQL for this case or my configuration is incorrect in terms of SmartGWT.

Anyway, help is still appreciated.

[vostapenko]

Isomorphic, is this issue going to be fixed in future releases or should I start searching for a workaround?

I'll summarize it:

<field name="departmentName" type="text" hidden="true" valueXPath="department/name"/>
        <field name="department" displayField="departmentName"/>

HibernateDataSource generates HQL:

select _User, department1 
from User _User 
left outer join _User.department department1 
order by _User.department.name

While probably it should generate the following

select _User, department1 
from User _User 
left outer join _User.department department1 
order by department1.name

because SQL generated from the first HQL contains unexpected cross join which prevents us from getting foreignKey == null records.

Additional details can be found in earlier posts.

Thanks in advance.

[alius]

Still working on it.

[vostapenko]

Oh, thx a lot, i didn't realise you're isomorphic staff member. Will be waiting for the fix=)

[vostapenko]

Any ETA on this one? Still reproduced in v8.3p_2012-11-22/PowerEdition Deployment (built 2012-11-22)

[vostapenko]

Cmon guys, sorting on a foreign key is an important functionality. And such core thing is not working in HibernateDataSource.

If you're not going to fix it in any reasonable time - please give me an advice.

Should I consider totally switching to SQLDataSource if HibernateDataSource is the thing of third priority for Isomorphic? Or should I just wait a little more?

Thanks for any reply.

[alius]

Hi,

I've just committed changes. You can test it with next nightly build.
All related tables are joined via 'left outer join'. Fields in related tables are addressed with aliases.


Regards,
Alius

[vostapenko]

Thanks for your help.

Should the fix be present in v8.3p_2012-12-14/PowerEdition Deployment (built 2012-12-14)? I can't see any changes.

[alius]

Hi,

Now it is committed only to 9.0.
Check 8.3 with next nightly.

Alius

[vostapenko]

v8.3p_2012-12-15/PowerEdition Deployment (built 2012-12-15)

Still the same.

=== 2012-12-15 17:45:04,329 [l0-3] INFO  HibernateDataSource - [builtinApplication.listFetch] Query string: select _User, position1, department2 from ru.eurotechnologygroup.etgcrm.server.model.User _User left outer join _User.position position1 left outer join _User.department department2 order by _User.position.name
=== 2012-12-15 17:45:04,329 [l0-3] DEBUG SQL - select count(*) as col_0_0_ from suser user0_
=== 2012-12-15 17:45:04,342 [l0-3] DEBUG SQL - select top 76 user0_.USERID as USERID0_0_, rubsection1_.RSID as RSID9_1_, department2_.DID as DID4_2_, user0_.USERBLOCKED as USERBLOC2_0_0_, user0_.USERDATEBIRTH as USERDATE3_0_0_, user0_.DID as DID0_0_, user0_.USEREMAIL as USEREMAIL0_0_, user0_.USERFIRSNAME as USERFIRS5_0_0_, user0_.USERLASTNAME as USERLAST6_0_0_, user0_.USERLOGIN as USERLOGIN0_0_, user0_.USERMASKIP as USERMASKIP0_0_, user0_.USERPASSWORD as USERPASS9_0_0_, user0_.USERPHONE as USERPHONE0_0_, user0_.USERPOSTRSID as USERPOS14_0_0_, user0_.USERPATRONYMIC as USERPAT11_0_0_, user0_.USERWARRANT as USERWAR12_0_0_, rubsection1_.RSHIDDEN as RSHIDDEN9_1_, rubsection1_.RSNAME as RSNAME9_1_, rubsection1_.RSNAMEADD as RSNAMEADD9_1_, rubsection1_.RSPARENTID as RSPARENTID9_1_, rubsection1_.RUBID as RUBID9_1_, department2_.ddeleted as ddeleted4_2_, department2_.DNAME as DNAME4_2_, department2_.dparid as dparid4_2_ from suser user0_ left outer join rubsection rubsection1_ on user0_.USERPOSTRSID=rubsection1_.RSID left outer join department department2_ on user0_.DID=department2_.DID cross join rubsection rubsection3_ where user0_.USERPOSTRSID=rubsection3_.RSID order by rubsection3_.RSNAME
=== 2012-12-15 17:45:04,405 [l0-3] INFO  DSResponse - [builtinApplication.listFetch] DSResponse: List with 0 items

[vostapenko]

So is the fix present in current 3.1 builds and i'm missing something?