CODEWITHSUNDEEP
androidsqlsqlitekotlinandroid-room
rost
rost

Reputation: 4077

Trouble with one to many relations via room database

I'm looking for a way to create a one-to-many relationship using room. The problem is to get specific data. I found the recommended way to do this in the official documentation.

Customer.class

@Entity(tableName = "customers")
data class Customer(
        val name: String
) {
    @PrimaryKey(autoGenerate = true) val id: Long = 0
}

Order.class

@Entity(tableName = "orders")
data class Order(
        val customerId: Long,
        val date: Long,
        val description: String
) {
    @PrimaryKey(autoGenerate = true) val id: Long = 0
}

CustomerWithOrders.class

class CustomerWithOrders() {

    @Embedded
    lateinit var customer: Customer

    @Relation(parentColumn = "id", entityColumn = "customerId", entity = Order::class)
    var orders: List<Order> = arrayListOf()
}

Dao.interface

@Transaction
    @Query("SELECT * FROM customers")
    fun getCustomersWithOrders(): LiveData<List<CustomerWithOrders>>

But the official documentation says nothing about how to make a request with condition, while maintaining one-to-many relation. I wrote a sample query, but it gives incorrect data.

Query with incorrect data.

@Transaction
    @Query("SELECT * FROM customers LEFT JOIN orders ON orders.customerId=customers.id WHERE date BETWEEN :dateStart AND :dateEnd")
    fun getCustomersWithOrdersByDate(dateStart: Calendar, dateEnd: Calendar): LiveData<List<CustomerWithOrders>>

Please tell me how to correctly complete my query or tell the source where something like this is being performed. And no matter how. Thanks.

Upvotes: 2

Views: 349

Answers (1)

MikeT
MikeT

Reputation: 56938

You could use the following as the basis (java rather than kotlin):-

CustomerWithOrders.java

public class CustomerWithOrders {

    Customer customer;
    List<Order> orders;

    //<<<<<<<<<<< this constructor >>>>>>>>>>
    public CustomerWithOrders(Customer customer, List<Order> orders) {
        this.customer = customer;
        this.orders = orders;
    }

    public Customer getCustomer() {
        return customer;
    }

    public void setCustomer(Customer customer) {
        this.customer = customer;
    }

    public List<Order> getOrders() {
        return orders;
    }

    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }
}

in conjunction with (adding between dates to the WHERE clause) (in PersonDao.java)

@Query("SELECT * FROM Orders WHERE customerId=:customerId")
abstract List<Order> getCustomerOrders(long customerId);

and then use something like :-

    List<Customer> allCustomers = mPDB.personDao().getAllCustomers(); //<<<<<<<< Get the required Customers (all in this example)
    ArrayList<CustomerWithOrders> allCWO = new ArrayList<>(); //<<<<<<<<<< Empty CustomerWithOrders array
    for (Customer c: allCustomers) {
        allCWO.add(new CustomerWithOrders(c,mPDB.personDao().getCustomerOrders(c.getId()))); //<<<<<<<< tie the orders to the customer
    }
    //<<<<<<<<< for demo >>>>>>>>>>
    String TAG = "CWOINFO";
    StringBuilder sb = new StringBuilder();
    for (CustomerWithOrders cwo: allCWO ) {
        sb.append("\nCustomer is " + cwo.getCustomer().getName());
        for (Order o: cwo.getOrders()) {
            sb.append("\n\tOrder is " + o.getDescription());
        }
    }
    Log.d(TAG,sb.toString());

Results in (all orders listed for checking) :-

2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 1Description = Order1 for Customer1Cust ID = 1
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 2Description = Order2 for Customer1Cust ID = 1
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 3Description = Order3 for Customer1Cust ID = 1
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 4Description = Order1 for Customer2Cust ID = 2
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 5Description = Order2 for Customer2Cust ID = 2
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 6Description = Order3 for Customer2Cust ID = 2
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 7Description = Order4 for Customer2Cust ID = 2
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 8Description = Order1 for Customer3Cust ID = 3


<<<<<<<<<< The output from the above code >>>>>>>>>>
2019-06-29 18:27:49.377 D/CWOINFO: Customer is Customer1
        Order is Order1 for Customer1
        Order is Order2 for Customer1
        Order is Order3 for Customer1
    Customer is Customer2
        Order is Order1 for Customer2
        Order is Order2 for Customer2
        Order is Order3 for Customer2
        Order is Order4 for Customer2
    Customer is Customer3
        Order is Order1 for Customer3

Alternative

By adding the following method to CustomerWithOrders.java

    public void addOrder(Order newOrder) {
        this.orders.add(newOrder);
    }

And adding the following to the Dao :-

@Query("SELECT * FROM customers WHERE id=:customerId")
abstract Customer getCustomerById(long customerId);

@Query("SELECT * FROM orders LEFT JOIN customers ON orders.customerId = customers.id WHERE date BETWEEN :dateStart AND :dateEnd ORDER BY customerId")
abstract List<Order> getOrders(long dateStart, long dateEnd); 

@Transaction
public List<CustomerWithOrders> getCustomersWithOrdersInDateRange(long dateStart, long dateEnd) {
    List<Order> ordersWithCustomer = getOrders(dateStart,dateEnd);
    ArrayList<CustomerWithOrders> cwo = new ArrayList<>();
    long currentCustomerId = 0;
    for (Order o: ordersWithCustomer) {
        if (o.getCustomerId() != currentCustomerId) {
            currentCustomerId = o.getCustomerId();
            cwo.add(new CustomerWithOrders(getCustomerById(o.getCustomerId()),new ArrayList<Order>()));
        }
        cwo.get(cwo.size()-1).addOrder(o);
    }
    return cwo;
}

The you can use code along the lines of :-

List<CustomerWithOrders> selectedCustomersWithOrders = mPDB.customerOrderDao().getCustomersWithOrdersInDateRange(0,99999999999999999L);

With LiveData (partial example)

Note not fully tested as I typically use allowMainThreadQueries() for answers

@Query("SELECT * FROM orders LEFT JOIN customers ON orders.customerId = customers.id WHERE date BETWEEN :dateStart AND :dateEnd ORDER BY customerId")
abstract LiveData<List<Order>> getOrders(long dateStart, long dateEnd); //<<<<<<<<<<< LD

@Transaction
public List<CustomerWithOrders> getCustomersWithOrdersInDateRange(long dateStart, long dateEnd) {
    LiveData<List<Order>> ordersWithCustomer = getOrders(dateStart,dateEnd); //<<<<<<<<<<< LD
    ArrayList<CustomerWithOrders> cwo = new ArrayList<>();
    long currentCustomerId = 0;
    for (Order o: ordersWithCustomer.getValue()) { //<<<<<<<<<<< LD
        if (o.getCustomerId() != currentCustomerId) {
            currentCustomerId = o.getCustomerId();
            cwo.add(new CustomerWithOrders(getCustomerById(o.getCustomerId()),new ArrayList<Order>()));
        }
        cwo.get(cwo.size()-1).addOrder(o);
    }
    return cwo;
}
  • Comments indicate changes

Upvotes: 2

Related Questions