I am using Play 2.4.6 on top of a Postgres 9.4.5 database.
I am trying to get the sum of amounts of the Donations in my database, but I want my users to be able to dynamically filter the Donations that are included in the query. I followed some examples and have created a new class, DonationAggregate, annotated with @Entity and @Sql. Since I want to do an aggregate querym I have to manually set the Sql to be run, which works fine. The issue arises when I try to then add some filters to the ExpressionList attached to my query - I try to filter on property names, but the names are not being translated from logical names to physical names. They are being interpreted as logical names, and of course the columns don't exist.
My question is how do I use a RawSql query, but also allow filters on the ExpressionList to be added using physical names? Currently when I run the below code I get an exception: Execution exception[[PersistenceException: Query threw SQLException:ERROR: column "lastfour" does not exist.
It also might be worth noting that if I change
.eq("lastFour", "1234")
to
.eq("last_four", "1234")
things work as expected. But since the filters I will be applying are dynamic and generated based on the logical/property names, I cannot do those conversions ahead of time.
Donation.java
@Entity
@EntityConcurrencyMode(ConcurrencyMode.NONE)
public class Donation extends Model {
@Id
private Long id;
private BigDecimal amount;
private String lastFour;
public List<DonationAggregate> getAggregated() {
String rawSql = "select sum(d.amount) as total from donation d";
RawSql rawTransaction = RawSqlBuilder.parse(rawSql).create();
ExpressionList<DonationAggregate> donationFilters = Ebean.find(DonationAggregate.class)
.setRawSql(rawTransaction)
.where().eq("lastFour", "1234") //---eventually this will be dynamic, so I won't know which columns are being used
.findList();
}
//---getters/setters
}
DonationAggregate.java
@Entity
@Sql
public class DonationAggregate {
private BigDecimal total;
@OneToOne
private Donation donation;
//---Getters/setters
}
