How to merge data with Room Database in Android-CodePudding

I have two different class which one of these class includes list of other class.

There is a NewInvoice class below and it contains a list of invoice items.

@Entity(tableName = "invoices")
public class NewInvoice implements Parcelable {

@NonNull
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "transaction_id")
public int transaction_id;
@ColumnInfo(name = "invoiceNo")
public String invoiceNo;
@ColumnInfo(name = "contactId")
public String contactId;
@ColumnInfo(name = "currencyCode")
public String currencyCode;
@ColumnInfo(name = "description")
public String description;
@ColumnInfo(name = "creationDate")
public long creationDate;
@ColumnInfo(name = "invoiceDate")
public long invoiceDate;
@ColumnInfo(name = "dueDate")
public long dueDate;
@ColumnInfo(name = "shipmentDate")
public long shipmentDate;
@ColumnInfo(name = "invoiceDiscount")
public double invoiceDiscount;
@ColumnInfo(name = "discountType")
public byte discountType;
@ColumnInfo(name = "invoiceAmount")
public double invoiceAmount;
@ColumnInfo(name = "balance")
public double balance;
@ColumnInfo(name = "taxIncluded")
public boolean taxIncluded;
@ColumnInfo(name = "status")
public int status;
@ColumnInfo(name = "type")
public int type;

@Ignore
public List<InvoiceItem> invoiceItems;


public NewInvoice() {
}

Below is the InvoiceItem class

@Entity(tableName = "invoice_items")
public class InvoiceItem implements Parcelable {
@NonNull
@PrimaryKey()
@ColumnInfo(name = "itemId")
private String itemId;
@ColumnInfo(name = "invoiceId")
private String invoiceId;
@ColumnInfo(name = "unitPrice")
private double unitPrice;
@ColumnInfo(name = "quantity")
private double quantity;
@ColumnInfo(name = "inventoryId")
private String inventoryId;
@ColumnInfo(name = "taxIncluded")
private int taxIncluded;
@ColumnInfo(name = "taxRate")
private double taxRate;
@ColumnInfo(name = "discountRate")
private double discountRate;
@ColumnInfo(name = "discountType")
private int discountType;

public InvoiceItem() {
}

I am using Room database. Currently, I query NewInvoice records firstly and then InvoiceItem records related with NewInvoice record and adding InvoiceItem objects to the NewInvoice obcejt's invoiceItems array list.

Is there a way to query NewInvoice records that includes InvoiceItem records.

CodePudding user response：

If you want to get NewInvoice records that include InvoiceItem records, you need this query:

SELECT invoiceNo
  FROM invoices
 WHERE invoiceNo IN (
           SELECT invoiceId
             FROM invoice_items
       );

If 'invoiceNo' in 'invoices' equals 'invoiceId' in 'invoice_items'.

In case you are new to subqueries: https://www.sqlitetutorial.net/sqlite-subquery/

CodePudding user response：

Typically, this would be achieved by having the two entities with NewInvoice (the list of invoiceItems isn't then needed but as it is ignored can be left) and then a third POJO class that embeds the NewInvoice with the @Embedded annotation and has the list of InvoiceItems annotated with @Relation.

e.g.

class NewInvoiceWithListOfRelatedInvoiceItems {
    @Embedded
    private NewInvoice newInvoice;
    @Relation(
            entity = InvoiceItem.class,
            parentColumn = "invoiceNo",
            entityColumn = "invoiceId"
    )
    private List<InvoiceItem> invoiceItemList;

    public NewInvoice getNewInvoice() {
        return newInvoice;
    }

    public void setNewInvoice(NewInvoice newInvoice) {
        this.newInvoice = newInvoice;
    }

    public List<InvoiceItem> getInvoiceItemList() {
        return invoiceItemList;
    }

    public void setInvoiceItemList(List<InvoiceItem> invoiceItemList) {
        this.invoiceItemList = invoiceItemList;
    }
}

Then you can have an @Query such as (for a single NewInvoice)

@Transaction
@Query("SELECT * FROM invoices WHERE invoiceNo=:invoiceNo")
NewInvoiceWithListOfRelatedInvoiceItems getInvoiceWithItems(String invoiceNo);

or for all NewInvoices then :-

@Transaction
@Query("SELECT * FROM invoices")
List<NewInvoiceWithListOfRelatedInvoiceItems> getAllInvoicesWithItems();

Alternatively you could, if you make the @Dao an abstract class, use just the classes you have (the POJO is then not required). By having the following Dao's e.g. :-

@Dao
abstract class NewInvoiceDao {

    @Query("SELECT * FROM invoice_items WHERE invoiceId=:invoiceNo")
    abstract List<InvoiceItem> getInvoiceItemsForANewInvoice(String invoiceNo);

    @Query("SELECT * FROM invoices WHERE invoiceNo=:invoiceNo")
    abstract NewInvoice getNewInvoiceByInvoiceNo(String invoiceNo);

    @Transaction
    @Query("")
    NewInvoice getNewInvoiceWithInvoiceItems(String invoiceNo) {
        NewInvoice rv = getNewInvoiceByInvoiceNo(invoiceNo);
        rv.setInvoiceItems(getInvoiceItemsForANewInvoice(invoiceNo));
        return rv;
    }
}

Additional

In either case it would be advisable to have an index on the invoiceNo and the invoiceId columns (assuming that this is the column used for the relationship).

So for NewInvoice you could have the @Entity annotation as :-

@Entity(tableName = "invoices", indices = {@Index(value = "invoiceNo", unique = true)})

And for InvoiceItem have the @ColumnInfo for the invoiceId column as :-

@ColumnInfo(name = "invoiceId", index = true)
private String invoiceId;