Query method on association links

Question

I have two entities User and Expense.

Expense

@Entity
@Table(name="Expenses")
public class Expense {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String name;    
    private BigDecimal amount;
    private Date date;

    @ManyToOne
    @JoinColumn(name="category_id")
    private Category category;  

    @ManyToOne
    @JoinColumn(name="mode_id")
    private Mode mode;

    @ManyToOne
    @JoinColumn(name="user_id")
    private User user;
    // Getter setter and constructor
}

User

@Entity
@Table(name="Users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String username;
    private String password;
    private String email;
    private boolean enabled;
    private String firstName;
    private String lastName;
    private String phoneNumber;
    private String country;

    @OneToMany(mappedBy = "user", fetch = FetchType.EAGER, cascade = CascadeType.REMOVE, orphanRemoval = true)
    private Set<Expense> expense;
    @OneToMany(mappedBy = "user", fetch = FetchType.EAGER, cascade = CascadeType.REMOVE, orphanRemoval = true)
    private Set<Mode> mode;
    @OneToMany(mappedBy = "user", fetch = FetchType.EAGER, cascade = CascadeType.REMOVE, orphanRemoval = true)
    private Set<Category> category;
    // Getter setter and constructor
}

Expense Repository

public interface ExpenseRepository extends CrudRepository<Expense, Integer> {
    List<Expense> findByDateBetween(@DateTimeFormat(pattern = "dd-MM-yyyy")@Param("startDate") Date startDate, @DateTimeFormat(pattern = "dd-MM-yyyy") @Param("endDate")Date endDate);
}

User Repository

public interface UserRepository extends CrudRepository<User, Integer>{
    Optional<User> findByUsername(String username);
}

Note: I am using Spring Data Rest.

I can access the list of expenses for User 1 with : http://localhost:8080/api/users/1/expense

Also I can get the list of expenses within a date range by : http://localhost:8080/api/expenses/search/findByDateBetween?startDate=01-02-2018&endDate=28-02-2018

What I am trying to achieve is to access the list of expense by using 'findByDateBetween' method for an user.

This Url is not working and throws 404 Error.

http://localhost:8080/api/users/1/expense/search/findByDateBetween?startDate=01-02-2018&endDate=28-02-2018

{
    "timestamp": 1517645444489,
    "status": 404,
    "error": "Not Found",
    "message": "No message available",
    "path": "/api/users/1/expense/search/findByDateBetween"
}

What am I doing wrong or is there a better way to do this ?

Answer 1

Date is a field in Expense and not in User. Create another method like this

public interface ExpenseRepository extends CrudRepository<Expense, Integer> {
    List<Expense> findByUser_IdAndDateBetween(Integer id, @DateTimeFormat(pattern = "dd-MM-yyyy")@Param("startDate") Date startDate, @DateTimeFormat(pattern = "dd-MM-yyyy") @Param("endDate")Date endDate);
}

And invoke it using

http://localhost:8080/api/expenses/search/findByDateBetween?id=1&startDate=01-02-2018&endDate=28-02-2018