CODEWITHSUNDEEP
javajpajpa-2.0jpql
Skizzo
Skizzo

Reputation: 2983

One-To-Many query jpql

I'm new of jpql and I have the following situation.

I have two entities Place and address.

@Entity
public class Place{


   @OneToMany
   private List<Address> addresses;

   ....
}

 @Entity
 public class Address{

   String description; 

   Date dataFrom;

   Date dataTo;

   @ManyToOne
   private Place place;


   ....
}

I would want to get the description of the last address. I'm trying to do this :

select a.description from place p join p.addresses a.....

and now i should get the last the last address in order of time. How can I do?

Upvotes: 0

Views: 8962

Answers (3)

Chris
Chris

Reputation: 21145

Try using a subquery, something like

select a.description from place p join p.addresses a where a.dataFrom = (select max(address.dataFrom) from Address address where address.place = p)

Upvotes: 1

Prabhakaran Ramaswamy
Prabhakaran Ramaswamy

Reputation: 26094

  Criteria criteria = session.createCriteria(Place.class, "place"); 
  criteria.createAlias("place.addresses", "addresses")
  criteria.add(Order.desc("addresses.dataFrom"));
  criteria.setProjection(Projections.property("addresses"));

  List<Address> addresses = criteria.list();

  for (Address address : addresses) {
       System.out.println(address.description);
  }

if you want to find the addresses based on the place id use below one.

  Criteria criteria = session.createCriteria(Place.class, "place"); 
  criteria.add(Restrictions.eq("place.id", put the place id here);
  criteria.createAlias("place.addresses", "addresses")
  criteria.add(Order.desc("addresses.dataFrom"));
  criteria.setProjection(Projections.property("addresses"));

  List<Address> addresses = criteria.list();

  for (Address address : addresses) {
       System.out.println(address.description);
  }

Upvotes: 0

David
David

Reputation: 20063

SELECT addresses.description 
FROM place p JOIN p.addresses addresses 
ORDER BY addresses.dateFrom

Then return this as a resultList and get the first item on the list, I would say you might be able to do like in T-SQL SELECT TOP 1, however, I don't believe JPQL supports this.

Upvotes: 4

Related Questions