HQL Many to Many JOIN with extra columns

Question

I have a many to many relationship in Hibernate with additional colums so I have an extra java class for the model and another class with the primary key... now in HQL I need a query that retrieve those data but I have problems with Join condition.

This is my First class:

@Entity
@Table(name = "Firsts")
public class First {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    @OneToMany(mappedBy = "primaryKey.second")
    private List<FirstsSeconds> seconds = new LinkedList<>();
    @Column(name="description")
    private String description;
    ...
}

The Second class:

@Entity
@Table(name="Seconds")
public class Second {
    @Id
    private String code;

    @OneToMany(mappedBy = "primaryKey.first")
    private List<FirtsSeconds> firsts = new LinkedList<>();

    ...
}

And the table manyToMany with additional columns:

@Entity
@Table(name = "firsts_seconds")
@AssociationOverrides({ @AssociationOverride(name = "primaryKey.first", joinColumns = @JoinColumn(name = "id")),
        @AssociationOverride(name = "primaryKey.second", joinColumns = @JoinColumn(name = "code")) })
public class FirstsSeconds{

    @EmbeddedId
    private FirstsSecondsId primaryKey = new FirstsSecondsId();

    @Column(name = "extra", nullable = false)
    private String extra;

    ...
}

So the id class:

@Embeddable
public class FirstsSecondsId {

    @ManyToOne
    private First first;
    @ManyToOne
    private Second second;

    ...
}

Finally to get HQL result I create a new class with the field that I want: public class NewObject

public CargoOrder(String firstDescription, String fsExtra) {
    this.firstDescription = firstDescription;
    this.fsExtra = fsExtra;
}
...

First of all I want First descrption and extra from FirstsSecond, so this is my query with JOIN fr.seconds as fs:

@Query("SELECT new com.mypackage.NewObject("
        + "fr.description as firstDescription, fs.extra as fsExtra) "
        + "FROM First as fr"
        + "JOIN fr.seconds as fs")
public List<NewObject> findManyToMany();

But I have no results :(... in this case I have to specify the where condition?

@Query("SELECT new com.mypackage.NewObject("
        + "fr.description as firstDescription, fs.extra as fsExtra) "
        + "FROM First as fr"
        + "JOIN fr.seconds as fs WHERE fr.first = fs.primaryKey.first")
public List<NewObject> findManyToMany();

Thats not compile on JOIN fr.seconds as fs WHERE fr.first = fs.primaryKey.first...

Kind regards.

Answer 1

Solved... debugging with spring.jpa.show_sql = true in application-properties I see that there was a bad matching in the join condition, I mapped wrong keys:

In First class it's primaryKey.first (not second):

@OneToMany(mappedBy = "primaryKey.first")
private List<FirstsSeconds> seconds = new LinkedList<>();

And in Second primaryKey.second (not first):

@OneToMany(mappedBy = "primaryKey.second")
private List<FirstsSeconds> firsts = new LinkedList<>();