Hibernate datasource + filtering many to one properties

[lkuchars]

Hello

I'm having trouble implementing advanced filtering with hibernate datasource. My observation is that if you have many to one relation then advanced criteria works only in specific situations.

For example. You feature Advanced filtering demo in your showcase "Server examples->Hibernate / Beans-> Advanced filtering".

I think SC can only filter by fields which are actually foreign keys held in Bean/DataSource being displayed.

In your example you filter grid by categoryName which happens to be foreign key held in SupplyItem. What if I wanted to filter by some other field which is not part of SupplyItem bean/table lets say categoryDescription (which would be held in SupplyCategory). This does not work for me because SC seems to always filter by foreign key.

Do you guys have any comments? It's important issue for us. We do not know if we are actually observing buggy behaviour or it's the way it's suppose to work.


Thanks
lkc

[lkuchars]

To clarify. I'm using 8.0 Power version.

I did another quick and dirty test on examples provided with distro.

I modified Hibernate / Beans-> Master detail Example. I added FilterBuilder to this example and button to filter ordersList grid. I added 2 new rows. And filtered orderList grid by what supposed to be items collection. Criteria generated by client is:

{
    criteria:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                fieldName:"items",
                operator:"iContains",
                value:"2"
            }
        ]
    },
    operationConfig:{
        dataSource:"masterDetail_orderHB",
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    startRow:0,
    endRow:75,
    componentId:"ordersList",
    appID:"builtinApplication",
    operation:"masterDetail_orderHB_fetch",
    oldValues:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                fieldName:"items",
                operator:"iContains",
                value:"2"
            }
        ]
    }
}

But SC instead of displaying some kind of error here (I don't expect it'll guess the item field I want to filter by) it happily generates following SQL

   select
        count(*) as y0_ 
    from
        masterDetail_order this_ 
    where
        (
            (
                lower(this_.orderID) like ? 
                and this_.orderID is not null
            )
        )

and filters primary key from orders. Why?

lkc

[lkuchars]

Hey Isomorphic,

It's been some time since I posted this question. Can you guys please give an opinion on this issue?

lkc

[Isomorphic]

Declare the related field in the DataSource and use valueXPath to extract it from the related bean. This will also cause filtering and sorting to work. But you should grab the latest nightly (smartclient.com/builds) because there are samples showing this for JPA (same declarations) and there's one known bug in this area that you might hit, and which has already been corrected in nightlies.

[lkuchars]

I did try valueXPath. I'll try this for nightlies.

Thanks for help

[chimpeenuts]

Using Smart GWT 2.5 Pro.

Not sure how to properly configure my DataSource file for a ManyToOne relationship to allow criteria to work on fetchdata(Criteria) operations.

I have two DB tables

----- Interface Table -----
id, long, pri key
name, string
device_id, long, foreign Key to Device Table

----- Device Table -----
id, long, pri key
name, string

I have two Hibernate objects

---- Interface Hibernate Bean ----

private long _id;
private String _name;

@ManyToOne
@JoinColumn(name="device_id")
private Device _device;


----- Device Hibernate Bean ----
private long _id;
private String _name;

@OneToMany(cascade = {CascadeType.ALL},
	mappedBy="device")
private Set<Interfaces> interfaces;

My Device DS file:

<DataSource ID="device" serverType="hibernate" beanClassName="com.bpt.db.model.Device"
  dropExtraFields="true">
  <fields>
    <field name="id" type="integer" primaryKey="true" />
    <field name="name" type="text" />
  </fields>
</DataSource>

My Interface DS file:

<DataSource ID="interfaces" serverType="hibernate"
  beanClassName="com.bpt.db.model.Interface" dropExtraFields="true">
  <fields>
    <field name="id" type="integer" primaryKey="true" />
    <field name="name" type="text" />
    <field name="device_name" type="text" valueXPath="device/name" />
    <field name="device_id" type="integer" valueXPath="device/id" />
  </fields>
</DataSource>

HERE IS MY ISSUE:

In my UI, I want to display a ListGrid with Interfaces, but only Interfaces with a specific device_id.

Here is that code:

 Criteria c = new Criteria();
    c.addCriteria("device_id", selectedDeviceId);
    table.fetchData(c);

This, however, DOES NOT WORK because the Smart GWT server code either:
a) does not recognize device_id as being a valid method on the Hibernate object
OR
b) pass "device.id" in the addCriteria method above because it says that an "id" already exists for this table (the primary key id of Interface) so it's ignoring the criteria
OR
c) pass "device" in the addCriteria method above because it says it can't, obviously convert a long to an object of type Device.

I could use a SQLDataSource which would allow me to add "device_id" criteria, BUT i need to display the Device name along the interface data in the ListGrid and a SQLDataSource will only give me back device_ids (and I need the name for display purposes).

So my overall question is, how do I configure either:
a) my Hibernate DataSource mapping file to allow me to pass criteria to select device_ids
AND/OR
b) what value do I pass to addCriteria to actually only return Interfaces with device_ids that I want.

[Isomorphic]

First, SQLDataSource handles this - see ListGridField.optionDataSource and the linked Large Value Map sample.

Second, if you want to continue with Hibernate, you should post the complete server log of the request. It's not clear how you arrived at your conclusions regarding what the actual problem is, the server log will make things clear.

[chimpeenuts]

Thanks for the reply! My post is an issue I've encountered with most of my listgrids/datasources so a resolution would be fantastic. See my responses below.

First, SQLDataSource handles this - see ListGridField.optionDataSource and the linked Large Value Map sample.

Took a look at the Large Value Map sample. Yes, this does exactly what I want, but it uses SQL templating to join the two tables. Is SQL templating available with the 2.5 Pro version?

If not (because I'm limited to using Pro), is it possible to achieve this result *without* SQL templating?

I didn't think it was so that's why I went with the hibernate option (and also b/c I have hibernate beans already). My hibernate bean has a reference to the device (which is how I display the device name in the interface table using XPathValue) so I just need to be able to pass criteria to the Interface datasource to only return interfaces that have a device with a specific Id.

Second, if you want to continue with Hibernate, you should post the complete server log of the request. It's not clear how you arrived at your conclusions regarding what the actual problem is, the server log will make things clear.

I will follow up this post with another post of my server log later today, but the logs indicate that the criteria reference I'm currently passing ("device_id") does not exist. And the server is right...my Hibernate Interface bean *does not* have a "device_id" property but I was hoping the Smart GWT server would look at the Interface datasource and see that device_id maps (via the XPathValue) to the Hibernate bean property "Device" and then that bean's "Id" and run the criteria query against that. Does the XPathValue only work for display purposes?

As an alternative, what if I added a getMethod called "getDevice_id()" in my Hibernate bean that just returned "device.id". This way the criteria would match a known property on the Hibernate bean and I'm assuming Smart GWT server code would then process the criteria fetch just like if "device_id" was an actual Interface Hibernate bean variable.

Would this work? (I'm not sure how I would handle updates though..would I need to also create a setProperty on my Bean called "setDevice_id(id)" that my Hibernate bean would then call "device.id = id"?

[alius]

Hi,

For correct valueXPath handling you can:

1. Switch to JPA.
or
2. Use latest 3.x nightly build for Hibernate.

[chimpeenuts]

Alius -

Does this mean that 3.x will allow criteria queries against XPath defined properties?

If so, this is huge for us!

Also, do you know of any documentation that describes this? Or the options when using XPathValue? Aside from the online samples that only seem to use XPathValue to display related bean values, the documentation on the Xpath feature is a little sparse.

Thanks!

[alius]

Yes - you can use criteria against fields with valueXPath. Moreover 3.x implementation optimizes query so that all related objects will be fetched in a single query.

As for documentation:
You can use fields with valuseXPath as normal fields: presentation, filtering, sorting, updating.
You have to make sure that beans have proper relations mapped at Hibernate layer and have getters/setters to address related objects.
You can set field's (with valueXPath) property (recently added) useJoin=false (defaults to true) to suppress including related object into single query and rely on loading strategy defined at Hibernate layer configuration.

Same applies to JPA/JPA2 data source.

As you can see - there is not much special to document ;)

Best regards,
Alius