Android Room Relationship duplicating information

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.

Answer

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();