Room DB SQLite query to get counts of one-to-many relationships from different tables-CodePudding

I'm trying to get a count of a one-to-many ralationship in my query.

My data class:

data class CustomerWithCounts(
@Embedded val customer: Customer,
@Embedded val address: Address,
val orderCount: Int,
val paymentCount: Int
)

I'm struggling to figure out how I can get the counts.

My current Query:

SELECT *, 
COUNT(SELECT * FROM tblOrder WHERE customerId = c.id) AS 'orderCount', 
COUNT(SELECT * FROM tblPayment WHERE customerId = c.id) AS 'paymentCount'
FROM tblCustomer c 
LEFT JOIN tblAddress a ON c.customerBillingAddressId = a.addressId 
ORDER BY c.customerFirstName, c.customerLastName

How do I achieve this?

CodePudding user response：

Assuming you have two tables - "Table1" and "Table2" - with a one-to-many relationship, you can use the following SQLite query to get the counts of the one-to-many relationships:

SELECT Table1.id, COUNT(Table2.id) AS Count 
FROM Table1 
LEFT OUTER JOIN Table2 
ON Table1.id = Table2.Table1Id 
GROUP BY Table1.id;