This is sort of a continuation from my previous one, but I finally figured that one out (got rid of the duplication issue).
Android Room Relationship duplicating information
Customer table
@Entity(tableName = "customer_table")
public class Customer {
@ColumnInfo(name = "Customer_Serial", index = true)
@PrimaryKey
private int customerSerial;
@ColumnInfo(name = "Customer_Sort", index = true)
private String customerSort;
@ColumnInfo(name = "Customer_Name")
private String customerName;
public Customer(int customerSerial, String customerName) {
this.customerSerial = customerSerial;
this.customerName = customerName;
this.customerSort = String.format(Locale.ENGLISH, "%d-%d", new Date().getTime(), customerSerial);
}
}
Invoice table
@Entity(tableName = "invoice_table")
public class Invoice {
@ColumnInfo(name = "Invoice_Number", index = true)
@PrimaryKey
private int invoiceNumber;
@ColumnInfo(name = "Customer_Serial")
private int customerSerial;
@ColumnInfo(name = "Invoice_Sort", index = true)
private String invoiceSort;
@ColumnInfo(name = "Delivery_Status")
private int deliveryStatus;
public Invoice(int invoiceNumber, int customerSerial) {
this.invoiceNumber = invoiceNumber;
this.customerSerial = customerSerial;
this.invoiceSort = String.format(Locale.ENGLISH, "%d-%d", new Date().getTime(), invoiceNumber)
}
public void setDeliveryStatus(int deliveryStatus) {
this.deliveryStatus = deliveryStatus;
}
public int getDeliveryStatus() { return deliveryStatus; }
}
CustomerInvoice relation
public class CustomerInvoice {
@Embedded public Customer customer;
@Relation(
parentColumn = "Customer_Serial",
entityColumn = "Customer_Serial"
entity = Invoice.class
)
public List<Invoice> invoices;
}
DAO
public abstract class InvoiceDao {
@Transaction
@Query("SELECT * FROM invoice_table " +
"JOIN customer_table " +
"ON invoice_table.Debtor_Ser_No = customer_table.Customer_Serial " +
"WHERE invoice_table.Delivery_Status = :deliveryStatus " +
"GROUP BY customer_table.Customer_Serial " +
"ORDER BY customer_table.Customer_Sort, invoice_table.Invoice_Sort")
abstract public LiveData<List<CustomerInvoices>> getCustomerInvoices(int deliveryStatus);
abstract public void insert(Invoice... invoice);
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract public void insertCustomer(Customer... customer);
}
ViewModel public LiveData<List> getCustomerInvoices(int deliveryStatus) { return dao.getCustomerInvoices(); }
Test
Invoice invoice1 = new Invoice(1234, 1);
Invoice invoice2 = new Invoice(1235, 1);
Invoice invoice3 = new Invoice(2468, 2);
Invoice invoice4 = new Invoice(2469, 2);
Customer customer1 = new Customer(1, "Customer 1");
Customer customer2 = new Customer(2, "Customer 2");
dao.insertCustomer(customer1);
dao.insertCustomer(customer2);
dao.insert(invoice1);
dao.insert(invoice2);
dao.insert(invoice3);
dao.insert(invoice4);
invoice1.setDeliveryStatus(0);
invoice2.setDeliveryStatus(0);
invoice3.setDeliveryStatus(0);
invoice4.setDeliveryStatus(0);
viewModel.getCustomerInvoices2(0).observe(getViewLifeCycleOwner(), list -> { ... });
If I debug the output of the observer, it returns correctly, 2 customers with 2 invoices each.
However, if I do
Test2
invoice1.setDeliveryStatus(1);
viewModel.getCustomerInvoices2(1).observe(getViewLifeCycleOwner(), list -> { ... });
It returns 1 customer with 2 invoices, instead of 1 customer with 1 invoice, as the 2nd invoice for that customer still has a delivery status of 0.
I realise the problem is in the CustomerInvoice relation where it is ignoring the where clause for the invoice_table itself (It still does the customer where clause perfectly).
However I just can't seem to wrap my head around to fix it.
I have Google searched for quite a while now, and I know it is because it is basically just doing 'Get customer where they have at least 1 invoice with the correct delivery status', then it is doing 'Get all invoices for this customer', just that pretty much everything I can find gives basic samples that don't involve LiveData at all, and I need it to be using LiveData.
One of the many attempts I tried to make it work, was to do a lot of the legwork in the viewmodel itself.
DAO
@Query("SELECT * FROM customer_table " +
"JOIN invoice_table " +
"ON customer_table.Customer_Serial = invoice_table.Debtor_Ser_No " +
"WHERE invoice_table.Delivery_Status = :deliveryStatus " +
"GROUP BY customer_table.Customer_Serial ORDER BY customer_table.Customer_Sort")
abstract public Maybe<List<Customer>> getCustomersByDeliveryStatus(int deliveryStatus);
@Query("SELECT * FROM invoice_table " +
"WHERE invoice_table.Debtor_Ser_No = :debtorSerial " +
"AND invoice_table.Delivery_Status = :deliveryStatus " +
"ORDER BY invoice_table.Invoice_Sort")
abstract public Single<List<Invoice>> getCustomerInvoicesByDeliveryStatus(int debtorSerial, int deliveryStatus);
ViewModel
public LiveData<List<Map<Customer, List<Invoice>>>> getCustomerInvoices2(int deliveryStatus) {
MutableLiveData<List<Map<Customer, List<Invoice>>>> liveCustomerInvoices = new MutableLiveData<>();
List<Map<Customer, List<Invoice>>> listCustomerInvoices = new ArrayList<>();
mInvoiceDao
.getCustomersByDeliveryStatus(deliveryStatus)
.subscribeOn(Schedulers.io())
.subscribe(
(customers) -> {
for (Customer customer : customers) {
mInvoiceDao.getCustomerInvoicesByDeliveryStatus(
customer.getCustomerSerial(),
deliveryStatus
).subscribeOn(Schedulers.io())
.subscribe(
(invoices) -> {
listCustomerInvoices.add(Collections.singletonMap(customer, invoices));
}
);
}
liveCustomerInvoices.postValue(listCustomerInvoices);
}, throwable -> Log.e("Error", "Error")
);
return liveCustomerInvoices;
}
While it does work (to a varying degree, the LiveData isn't updated instantly, so sometimes it shows nothing or sometimes it shows 1 thing only until I refresh the display), and my recyclerview shows exactly what I need it to show, it doesn't maintain the order based on 'Customer_Sort' and 'Invoice_Sort' which has to be maintained.
I understand why on that too, it's because 'map' doesn't guarantee order.
First issue I believe is that when you have
@Embeddedand then@Relationthe@Embeddedis considered the parents (Customers). That is Room basically ignores (at first) the children (Invoices).You appear to be considering this from an Invoice perspective when Room considers it, as instructed by the
@Embedded/@Relation, from the Customer perspective.Once Room has (in theory) obtained the parents (customers) it then considers this from the object perspective and obtains ALL children (invoices), irrespective of SQL (e.g. WHERE ORDER) that affects the children retrieved and builds complete objects (all children for the parent).
The WHERE and ORDER are only affectual if it changes the number of parents.
This basically a convenience approach.
To affect the children (Invoices), prune them, sort them if using the Customer(@Embedded)Invoice(@Realtion) POJO needs a means of overriding Rooms handling.
Another issue is that your testing code changes the Invoice objects (e.g.
invoice1.setDeliveryStatus(0);) but does not apply that change to the database. So if you extract from the database then those changes will not have been applied.Without changing the CustomerInvoice class. Consider the following:-
Getters and setters added to the Customer and Invoice classes.
InvoiceDao transformed to be :-
Debtor_Ser_Nocustomer_serial hard coded.To demonstrate a modified Test, that a) updates the invoice delivery_status values in the database and b) uses a method to log the returned CustomerInvoices which allows the required delivery status to be passed:-
When run 6 sets (3 pairs) of results may be output to the log. The output being:-