I am trying to use Room in my application but I have a very complex query that makes a recursive and inner join between multiple tables
here is my query,
fun test(categoryId: Int): String {
return "WITH CTE AS (SELECT id, parent_id, id AS CategoryID FROM categories WHERE parent_id= 0 UNION ALL SELECT t.id, t.parent_id, t.id|| ', ' || CategoryID AS CategoryID FROM categories t INNER JOIN CTE c ON t.parent_id = c.id) SELECT CTE.CategoryID FROM CTE where CTE.id= " + categoryId + " ORDER BY CTE.id"
}
this method supposes to return the query string and I have tested in mysql and it is working.
How to be able to convert this query to Room?
