CODEWITHSUNDEEP
javaspring-data-jpacrud
Angelina
Angelina

Reputation: 2265

Use Spring Data JPA to Find By Column from 1 table and order by column from another table

I am trying to return data from 2 different tables using WorkoutCaseId from ReportedWorkout and sort them using PaymentDate from Payment.

ReportedWorkout

+-------------------+----------------+
| ReportedWorkoutId | WorkoutCaseId  |
+-------------------+----------------+

Payment

+-----------+--------------------+--------------+---------------+
| PaymentId | ReportedWorkoutId  | PaymentDate  | PaymentAmount |
+-----------+--------------------+--------------+---------------+

I want to return data as:

SELECT * FROM ReportedWorkout
JOIN table2 ON ReportedWorkout.ReportedWorkoutId = Payment.ReportedWorkoutId
WHERE WorkoutCaseId = '123'
ORDER BY PaymentDate DESC

@Entity
@Table(name = "ReportedWorkout")
public class ReportedWorkoutEntity extends BaseEntity{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ReportedWorkoutId")
    private Long reportedWorkoutId;

    @Column(name = "WorkoutCaseId")
    private String workoutCaseId;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, mappedBy = "ReportedWorkout")
    private Set<PaymentEntity> payments = new LinkedHashSet<>();

...
}
@Entity
@Table(name = "Payment")
public class PaymentEntity extends BaseEntity{

    @Id
    @Column(name = "PaymentId" , nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long paymentId;

    @ManyToOne
    @JoinColumn(name = "ReportedWorkoutId")
    private ReportedWorkoutEntity reportedWorkout;

    @Column(name = "PaymentAmount")
    private BigDecimal paymentAmount;

    @Column(name = "PaymentDate" , nullable = false)
    private LocalDate paymentDate;
...
}

I got it to return data by WorkoutCaseId:

@Repository
public interface ReportedWorkoutRepository extends CrudRepository<ReportedWorkoutEntity, Long> {    
    ReportedWorkoutEntity findByWorkoutCaseId(String workoutCaseId);
}

But I don't know how to order it by PaymentDate?

findByWorkoutCaseIdOrderByPaymentDateDesc

I get following error:

Caused by: org.springframework.data.mapping.PropertyReferenceException: No property paymentDate found for type ReportedWorkoutEntity!

Upvotes: 3

Views: 4528

Answers (2)

Atul
Atul

Reputation: 3367

When you want to use another entity, you have to mentioned it in your method signature which you defined in @Repository interface.

As mentioned by @churd, you have to create the method signature with that format only.

There is another option like mentioned @Query on top of method and define sql in it which you want to execute.

Upvotes: 1

camtastic
camtastic

Reputation: 998

Since paymentDate is not a property of ReportedWorkoutEntity the error makes sense. For the repository method use the fully qualified name for paymentDate relative to ReportedWorkoutEntity.

So: findByWorkoutCaseIdOrderByPaymentsPaymentDateDesc

Upvotes: 3

Related Questions