I want to convert this sql query to a JPA query, but I can't seem make sense of it... Should I use findByMarinaIdAndMovementGroupMeanId?? or findByMarinaIdAndMovementGroupMeanIdAndMovementMeanId??

Sql:

 select m.* from movement_group m
    join movement_group_mean mgm on m.id = mgm.movement_group_id
    join movement_mean mm on mgm.movement_mean_id = mm.id
where mm.id = 1 and m.marina_id = :marinaId and mm.active = true;

MovementGroup:

@Entity
public class MovementGroup {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;
    private String code;
    private LocalDateTime createdAt;
    private LocalDateTime updatedAt;
    private Boolean active;
    private String iconUrl;
    
    
    @OneToMany(mappedBy = "movementGroup")
    private Set<MovementGroupMean> movementGroupMeans;
    
    @JsonIgnore
    @ManyToOne()
    @JoinColumn(name = "marina_id")
    private Marina marina;

MovementGroupMean:

@Entity
public class MovementGroupMean {

    @EmbeddedId
    @JsonIgnore
    private MovementGroupMeanPK movementGroupMeanPK;

    @JsonBackReference
    @ManyToOne
    @JoinColumn(name = "movement_group_id", insertable = false, updatable = false)
    private MovementGroup movementGroup;
    
    @ManyToOne
    @JoinColumn(name = "movement_mean_id", insertable = false, updatable = false)
    private MovementMean movementMean;

MovementMean:

@Entity
public class MovementMean {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;
    @Enumerated(EnumType.STRING)
    private MovementMeanType movementMeanType;
    private Boolean active;
    private LocalDateTime createdAt;
    private LocalDateTime updatedAt;
    
    @JsonBackReference
    @ManyToOne()
    @JoinColumn(name = "marina_id")
    private Marina marina;

CodePudding user response：

Not sure where the problem lies, so excuse the lengthy explanation on SQL->JPQL:

1. Replace your table names with your entity names

   • movement_group -> MovementGroup

2. Replace your joins with the java references, letting JPA use the relationship mapping you've defined instead.

   • "join movement_group_mean mgm on m.id = mgm.movement_group_id" becomes "join m.movementGroupMeans mgm"
   • "join movement_mean mm on mgm.movement_mean_id = mm.id becomes "join mgm.movementMean mm"

Only tricky spot is your entities do not define a basic mapping for the marina_id value. So to get at m.marina_id, you will have to use the 'marina' reference and use its presumably ID value: "m.marina_id = :marinaId" -> "m.marina.id = :marinaId"

Giving you JPQL:

"Select m from MovementGroup m join m.movementGroupMeans mgm join mgm.movementMean mm where mm.id = 1 and m.marina.id = :marinaId and mm.active = true"