Performing Calculations Over Multiple Instances of the Same Entity With HQL

[Ricardo Peres]

Hello!

I wasn't really sure of what to call this post... Here's this situation, I have this entity (simplified):

public class AnalogicalValues

{

    public virtual DateTime Timestamp { get; set; }

    public virtual Double ? Value { get; set; }

    public virtual Int32 DeviceId { get; set; }

}

I want to be able to do something like this in SQL (simplified):

(SQL)

select (A.Value + B.Value) / 2, A.Timestamp

from AnalogicalValues A

full outer join AnalogicalValues B

on A.Timestamp = B.Timestamp

where A.DeviceId = 1

and B.DeviceId = 2

The problem is, although HQL supports full joins, it does not support specifying a join path; so the following HQL query is invalid:

(HQL)

select (A.Value + B.Value) / 2, A.Timestamp

from AnalogicalValues A

full join AnalogicalValues B

with A.Timestamp = B.Timestamp

where A.DeviceId = 1

and B.DeviceId = 2

You may wonder why I need the full outer join: that is because I want to group together values of the same time stamp, if and only if they exist on both sides. And, yes, I need to do it in HQL.

Here's how I solved it:

public class AnalogicalValues

{

    public virtual DateTime Timestamp { get; set; }

    public virtual Double ? Value { get; set; }

    public virtual Int32 DeviceId { get; set; }

    public IEnumerable<AnalogicalValues> SameTimestamp { get; set; }

}

As you can see, I mapped a set collection from AnalogicalValues to AnalogicalValues, and I joined it by the Timestamp property (using property-ref).

This way, this HQL query works:

(HQL)

select (A.Value + B.Value) / 2, A.Timestamp

from AnalogicalValues A

full join A.SameTimestamp B

where A.DeviceId = 1

and B.DeviceId = 2

My question is: is this the best approach?

Thanks!

RP