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.
