0

I have a ManyToMany which I have mapped like this question.

Please note, I have removed boilerplate for simplicity

@Entity
class Person {

    @OneToMany(mappedBy = "person")
    @OrderBy("sort")
    private List<PersonAddress> adresses = new ArrayList<>();

}

@Entity
class PersonAdress {
    @EmbeddedId
    private PersonAdressId id;
    @Column
    private int sort;

    @ManyToOne
    private Person person;

    @ManyToOne
    private Address address;
}

@Entity
class Address {
    @OneToMany(mappedBy = "address")
    @OrderBy("sort")
    private List<PersonAddress> persons = new ArrayList<>();
}

@Embeddable
public class PersonAdressId implements Serializable {
    @Column(name = "person_id")
    private long personId;
    @Column(name = "address_id")
    private long addressId;
}

I am trying to get all the adresses for person, and order by the sort attribute. But for some reason I get exception or I don't get it sorted.

I have tried the following:

"select p from Person p where p.id=pid join fetch p.address a order by a.sort"

I have also tried:

Person person = entityManager.find(Person.class, personId);
person.getAddress() //<-- This should use the @OrderBy, but I don't get it ordered nor does it print out order by in the output

Can anyone spot why its not working?

Community
  • 1
  • 1
Shervin Asgari
  • 23,901
  • 30
  • 103
  • 143
  • and have you actually looked at the SQL being invoked? for these 2 1-N relations. Besides, as 1 answer says, your mapping is wrong. – Neil Stockton Sep 22 '15 at 08:18

2 Answers2

0

Did you try following query:

Select p From Person p Left Join p.adresses a Where p.id = :pid Order By a.sort

See the HQL reference for more information.

Tom
  • 21
  • 1
0

To start with, your mapping is false : you want a many-to-many association between Person and Address ? and that's why you have that PersonAddress entity in the middle ?

  • In class Person, it should be :

    @OneToMany(mappedBy = "person") private List<PersonAddress> personAdresses = new ArrayList<>();

  • In class Address, it should be :

    @OneToMany(mappedBy = "address") private List<PersonAddress> personAddresses = new ArrayList<>();

Only then, your sort will work, as it is a member of PersonAddress entity

Pras
  • 1,068
  • 7
  • 18
  • My mapping is not false. Did you look at the link I posted? http://stackoverflow.com/questions/23837561/jpa-2-0-many-to-many-with-extra-column PS: My person and address is not my real entities, just something I used to illustrate my problem – Shervin Asgari Sep 22 '15 at 07:21
  • come on, it is a many-to-many ... and your PersonAddress is linked to nothing in your mapping ! ... I still maintain that your mapping is false ! – Pras Sep 22 '15 at 07:27
  • working sql : `select p from Person p where p.id=pid join fetch p.personAddresses pa join fetch pa.address a order by pa.sort` – Pras Sep 22 '15 at 07:49
  • Indeed it is wrong. If he has a M-N and wants an extra column, he should have the 2 1-Ns referring to PersonAddress. In addition, once he's done that it should not be referred to as an M-N in the question. +1 – Neil Stockton Sep 22 '15 at 08:04
  • I changed the question. Still it doesn't work. What I want is to have `person.getAddress()` to correctly sort the list. But it doesn't – Shervin Asgari Sep 22 '15 at 09:06
  • can you please post the sql being invoked here ? it should be something like the jpql i posted above : `select p from Person p where p.id=pid join fetch p.personAddresses pa order by pa.sort asc` – Pras Sep 22 '15 at 09:22