Issues with bidirectional OneToMany mapping and NamedQuery

Question

I have two entities connected bidirectional and I want to query the Location and its votes only for a specific date.

Location:

@Entity
@Table(name = "TAB_LOCATION")
@NamedQueries({
    @NamedQuery(name = "Location.getVotedLocations", query = "SELECT l FROM Location l JOIN l.votes v WHERE v.location = l AND DATE(v.createdAt) = DATE(:date) ORDER BY l.name")
})
public class Location extends AbstractEntity {

  @Basic
  @Size(min = 5, max = 50)
  private String name;

  @Basic
  @Size(min = 0, max = 50)
  private String address;

  @OneToMany(mappedBy = "location")
  private Set<Vote> votes;

  @Basic
  private String description;

Vote:

@Entity
@Table(name = "TAB_VOTE")
public class Vote extends AbstractEntity {

  @Basic
  @ManyToOne
  @NotNull
  private User user;

  @Basic
  @ManyToOne
  @NotNull
  private Location location;

I was trying to use the named query but it seems that the Location always contains all votes regardless the condition.
Isn't it possible to map the queried values back to the object?

entityManager.createNamedQuery("Location.getVotedLocations").
  setParameter("date", date).getResultList();

What is wrong?
If it isn't possible with NamedQueries, I also can use the Criteria API.

@Ankit I want a list of locations with votes on a particular date, if there are no votes the location should be skipped. — CSchulz, Jan 12 '15 at 16:39

score 2 · Answer 1 · answered Jan 12 '15 at 16:55

2

Here's a simple MySql statement that will return locationIDs not Location entity which are voted on a particular date.

 Select DISTINCT LocationID FROM VOTE WHERE DATE == dateCreated;

And you can then get the Location entity by LocationID.

answered Jan 12 '15 at 16:55

Ankit

1,075
1
8
19

This will get him the whole location objects, with all votes attached, but he wants the votes to be filtered out by date. – Predrag Maric Jan 12 '15 at 17:02
He cannot do this in a single query then. – Ankit Jan 12 '15 at 17:06

score 1 · Accepted Answer · answered Jan 12 '15 at 16:31

1

When you get an entity as a result from some query, you get the whole entity. It is not possible in JPA to get just a subset of all data, trimmed by where condition.

Well, if you use Hibernate, take a look at Hibernate Filters, with them you could get the result you want.

Note about your query, you have JOIN l.votes so you don't need to join it again with WHERE v.location = l.

answered Jan 12 '15 at 16:31

Predrag Maric

23,938
5
52
68

With Criteria API it isn't possible too? – CSchulz Jan 12 '15 at 17:04
I don't think it's possible, read [this](http://stackoverflow.com/questions/10200985/jpql-query-how-to-filter-rows-on-a-relationship) and [this](https://docs.oracle.com/html/E24396_01/ejb3_overview_query.html#ejb3_overview_query_relations). I'll quote the relevant sentence from second link: `This query says that for each Magazine x, traverse the articles relation and check each Article y, and pass the filter if y's authorName field is equal to "John Doe". In short, this query will return all magazines that have any articles written by John Doe.` – Predrag Maric Jan 12 '15 at 17:13
So the only way would be to query all votes with location and store them into a format which will help me? – CSchulz Jan 12 '15 at 17:21
Yes, something like that. – Predrag Maric Jan 12 '15 at 17:27

Issues with bidirectional OneToMany mapping and NamedQuery

2 Answers2