Is there any way to retrieve a List where only the children within a certain date range is included?
Not that easily. @Relation works by getting ALL the children of the parent based upon a second query (hence why @Transaction is recommended). Filtering only applies to the selection of the parent.
The problem is that if you use something like :-
SELECT * FROM parent JOIN child ON child.Id = parent.elementId WHERE date BETWEEN fromDate and toDate;
What is returned is a list where each row contains the parent and the single child. To extract the parent and all the filtered children requires the list to then be processed to single out the parent's with it's children.
Based upon the available code, here's an example.
One exception is the Id column which would typically be the ID of the child, so a parentId columns has been added that hold the ID of the parent.
Another exception is that the date, for convenience/brevity, is just a String (TEXT) rather than Instant which requires an @TypeConverter.
A Third exception is that a companion object has been used (as will be shown in due course).
First a new POJO, Filter for the extraction of the filtered list (a row for each parent and child)
data class Filter(
@Embedded
val parent: Parent,
@Embedded
val child: Child
)
Next a suitable Dao getFiltered for the query (pretty much as above) :-
@Query("SELECT * FROM parent JOIN child ON child.parentId = parent.elementId WHERE date BETWEEN :fromDate AND :toDate")
abstract fun getFiltered(fromDate: String, toDate:String): List<Filter>
- Note that this returns a List of Filters NOT a List of Combineds
Last the Child with the companion object to facilitate building a List of Combineds from the List of Filters:-
@Entity
data class Child(
@PrimaryKey
var id: Long? = null,
var parentId: Long,
@NotNull
var childName: String,
var date: String
) {
companion object {
fun buildParentsWithChildren(filter: List<Filter>): List<Combined> {
var rv: ArrayList<Combined> = arrayListOf()
if (filter.size < 1) return rv
for (f: Filter in filter) {
addChild(rv, getOrAddParent(rv, f), f)
}
return rv
}
private fun getOrAddParent(built: ArrayList<Combined>, f: Filter): Int {
for (i in 0..(built.size-1)) {
if (built.get(i).parent.parentName == f.parent.parentName) {
return i
}
}
var newCombined: Combined = Combined(f.parent, emptyList())
built.add(newCombined)
return built.size - 1
}
private fun addChild(built: ArrayList<Combined>, parentIx: Int, f: Filter) {
var currentChildren: ArrayList<Child> = arrayListOf<Child>()
currentChildren.addAll(built.get(parentIx).children)
currentChildren.add(f.child)
built[parentIx] = Combined(parent = built.get(parentIx).parent, currentChildren)
}
}
}
Example
Here's an example of using the above.
First it builds some data, 3 parents 5 children (3 to the first parent, 2 to the second parent, 0 to the third parent) :-
and
It then uses the query to extract some data and converts this to a List. It then traverses the list outputting to the Log.
Here's the code from an Activity :-
db = TheDatabase.getInstance(this)
dao = db.getAllDao()
var p1 = dao.insert(Parent(parentName = "Parent1"))
var p2 = dao.insert(Parent(parentName = "Parent2"))
var p3 = dao.insert(Parent(parentName = "Parent3"))
dao.insert(Child(parentId = p1,childName = "Child1",date = "2000-01-01"))
dao.insert(Child(parentId = p1,childName = "Child2",date = "2003-01-01"))
dao.insert(Child(parentId = p1,childName = "Child3",date = "2005-01-01"))
dao.insert(Child(parentId = p2,childName = "Child4",date = "2006-01-01"))
dao.insert(Child(parentId = p2,childName = "Child5",date = "2007-01-01"))
for(cmbnd: Combined in Child.buildParentsWithChildren(dao.getFiltered("2004-12-31","2010-01-01"))) {
Log.d("DBINFO","Parent is ${cmbnd.parent.parentName}")
for(c: Child in cmbnd.children)
Log.d("DBINFO","Child is ${c.childName} date is ${c.date}")
}
and the result :-
2021-08-02 08:38:50.426 D/DBINFO: Parent is Parent1
2021-08-02 08:38:50.426 D/DBINFO: Child is Child3 date is 2005-01-01
2021-08-02 08:38:50.426 D/DBINFO: Parent is Parent2
2021-08-02 08:38:50.427 D/DBINFO: Child is Child4 date is 2006-01-01
2021-08-02 08:38:50.427 D/DBINFO: Child is Child5 date is 2007-01-01
i.e. Only 1 of the 3 children from the first parent as only 1 has a date between 2005 and 2009. However, both child of the second parent as they fit the date range. Nothing for the third parent.
