Android Room Relationship duplicating information

Question

Having the weirdest issue here, all is working fine, except that my 1-to-M query is duplicating the data.

Customer table

@Entity(tableName = "customer_table")
public class Customer {
  @ColumnInfo(name = "Customer_Serial", index = true)
  @PrimaryKey
  private int customerSerial;

  @ColumnInfo(name = "Customer_Name")
  private String customerName;

  public Customer(int customerSerial, String customerName) {
    this.customerSerial = customerSerial;
    this.customerName = customerName;
  }
}

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;

  public Invoice(int invoiceNumber, int customerSerial) {
    this.invoiceNumber = invoiceNumber;
    this.customerSerial = customerSerial;
  }
}

CustomerInvoice relation

public class CustomerInvoice {
  @Embedded public Customer customer;
  @Relation(
    parentColumn = "Customer_Serial",
    entityColumn = "Customer_Serial"
  )
public List<Invoice> invoices;
}

DAO

@Transaction
@Query("SELECT * FROM customer_table INNER JOIN invoice_table ON invoice_table.Customer_Serial = customer_table.Customer_Serial")
List<CustomerInvoice> getAllCustInvoices();

@Insert
void insertInvoice(Invoice... invoice);

@Insert
void insertCustomer(Customer... customer);

If I debug my application, set a breakpoint to test the Room stuff, then use the 'Evaluate' feature in Android Studio, I do the following

Invoice invoice1 = new Invoice(1234, 1);
Invoice invoice2 = new Invoice(2468, 1);
Customer customer = new Customer(1, "Test Customer");

dao.insertCustomer(customer);
dao.insertInvoice(invoice1);
dao.insertInvoice(invoice2);

If I then retrieve the information using getAllCustInvoices()

The list returned has 2 in it.

It has the customer duplicated for each invoice assigned to them, and then both invoices listed in each 1.

I'm not entirely sure where I am going wrong here, this is a simplified example of what the app itself is actually doing, simplified enough to see if something else in my code was causing the problem or not.

Turns out, even with the simplified example, it has the issue.

MikeT · Accepted Answer · 2022-10-12T08:13:22.203

0

The issue

When @Relation is used Room extracts the children (ALL children) per parent (effectively running a second query to extract the children, hence the recommendation for using @Transaction ). By specifying the JOIN you are extracting the same parent for each child (i.e. the cartesian product) and hence the duplication.

i.e. Room does the equivalent of the JOIN internally

The Fix

@Transaction
@Query("SELECT * FROM customer_table")
List<CustomerInvoice> getAllCustInvoices();

edited Oct 12 '22 at 08:13

answered Oct 12 '22 at 08:07

MikeT

51,415
16
49
68

Ahh ok, that makes sense. How would I go about filtering the list of invoices? Lets say I have a field on the invoice called 'Is_Verified' which is a boolean (Which Room stores internally as an integer), and I only want to get the 'Customer' plus all their invoices for which 'Is_Verified' is 0? – Raymond Herring Oct 12 '22 at 08:37
@RaymondHerring three options. **1)** Loop through full list of CustomerInvoice objects returning desired result, or **2)** process the cartesian product building the desired result or **3)** see https://stackoverflow.com/questions/71615777/how-to-sort-child-data-using-relation-in-android-room-database-which-uses-one-t/71622438#71622438 (as a pointer i.e. main query to get parents, second query to get children). (method 3 would be the simplest, 2 probably most efficient from database perspective as just the one query does all the db access, 1 could return a lot on unnecessary data ). – MikeT Oct 12 '22 at 08:57
I thought I was onto something doing option 3, set up the abstract dao, set it up in my database abstract class (same as my existing DAO), set up some stuff in my repository and view model so that my activity can use it. All this using LiveData like I have been, then I get hit with the dreaded 'Cannot access database on the main thread since it may potentially lock the UI for a long period of time'... – Raymond Herring Oct 13 '22 at 06:37
I see the problem, I think. I have my main query which combines the other 2 queries set as being LiveData, but that one isn't the problem. The problem is the other 2 queries aren't LiveData, they just return their respsective List (List and List), so it's yelling at me because of those 2. – Raymond Herring Oct 13 '22 at 06:46
I thought I was onto something, but the observers just aren't working properly :( And it all comes out sorted incorrectly too. Not really sure why Android has to make this difficult, but I might have to just give up on it and figure something else out. – Raymond Herring Oct 13 '22 at 07:32

Android Room Relationship duplicating information

1 Answers1

Linked