How to retrieve data by association during a chosen period
Hello all, I have created two attributes in an entity, called StartDate and EndDate. I want to retrieve data by association from StartDate to EndDate and display them in a list. For example, there is a Book_Management Project. There are two entities called Books and BorrowingInformation. The BorrowingInformation is includes User, Borrow_Date, Fee, etc. Now I need to choose StartDate and EndDate, and retrieve BorrowingInformation_Books whose the Borrow_Date is from StartDate to EndDate. Then aggregate total Fee of each book during this period and display BookName and TotalFee in a list. The list’s datasource is a Microflow. I tried to retrieve data by database during a chosen period through XPath, but in the list there are all information but not each book’s total fee. If retrieve data by association, it cannot choose period, and even no items found in the list. So how to retrieve data by association during a chosen period? And where should I create StartDate and EndDate? Thanks in advance.
One way to do it would be :
First, create a new NPE to store Book details and aggregate fees in your domain model – BookBorrowing_NPE. Then in the source microflow :
Retrieve list of Books from database using xpath constraints over BorrowingInformation/Borrow_Date to get all the books borrowed between that time window
Create a new empty list of the NPE (BookBorrowing_NPE) created above.
Iterate over the list of books
Retrieve from DB the list of BorrowingInformation associated the book in current iteration and with the same date constraints in XPATH
aggregate book fees
create an object of the NPE (BookBorrowing_NPE) with required values
add the object to the list created outside this loop
return the list of NPE objects
Perhaps you can achieve the same with an OQL query without the loop.
The best way to approach this is to retrieve all books and filter, either by using the filter option (if possible) or by iterating over the list, finding which books fall within your parameters and adding those to a list which you eventually return into the listview.
This is probably the best way to go :)
You might want to retrieve BorrowingInformation from the database passing Book as a parameter over association as well as the range for the borrowing date.