Select data from two tables in Kotlin Anko

Question

In my database, there are two tables,

1) Task : (id (INTEGER + PRIMARY_KEY), title, description, timestamp, category_id(Foreign key of Category -> id ))

2) Category : (id (INTEGER + PRIMARY_KEY), name)

To select data from a table Anko is providing a method like :

var mDatabase: DatabaseManager = DatabaseManager.getInstance(activity)
var mResult: List<TaskModel> = ArrayList()
mDatabase.use {
    mResult = select(TABLE_TASK).parseList(classParser())
}

What I want is to show list of task with its category name I have checked all the tutorials and stack links but didn't get any way to achieve this.

Is there any way to select data from two tables in Kotlin Anko?

Thanks is Advance!

Answer 1

it's late but can be useful for someone like myself

the answer in short is ,Yes

you should create a data class including join column data variables like this:

data class joinTable(val id: Int, val title: String, val name:String) 

and use inner join in select command

database.use {
            select("table1 inner join table2",
                    "id,title,name").exec { parseList<joinTable>(classParser()) }
        }

that's it

Answer 2

I'm posting my answer for another problem (See https://github.com/Kotlin/anko/issues/622) here, too. I was trying to do a query in Anko SQLite that uses an inner join on two times the same table (e.g. SELECT ... FuelData AS FuelData1 INNER JOIN FuelData AS FuelData2 ON FuelData1.Id = FuelData2.Id).

I added a data class:

class FuelData2(
        val id : Int,
        val currentDate: String,
        val litersPer100Km: Double
)

and added the logic to my activity class:

private fun readFuelData2(): ArrayList<FuelData2> {
    val resultList = ArrayList<FuelData2>()
    val query = "SELECT FuelData1.Id, FuelData2.CurrentDate," +
            " (FuelData2.Fueled / (FuelData2.Mileage - FuelData1.Mileage) * 100) AS LitersPer100Km" +
            " FROM FuelData AS FuelData1" +
            " INNER JOIN FuelData AS FuelData2" +
            " ON FuelData1.Id = (FuelData2.Id - 1);"
    database.use {
        val cursor = database.writableDatabase.rawQuery(query, null)
        while (cursor.moveToNext()) {
            val fuelData = FuelData2(id = cursor.getInt(0),currentDate = cursor.getString(1),
                    litersPer100Km = cursor.getDouble(2))
            resultList.add(fuelData)
        }
    }
    return resultList
}

The query I wanted to do is:

SELECT FuelData1.Id, FuelData2.CurrentDate,
(FuelData2.Fueled / (FuelData2.Mileage - FuelData1.Mileage) * 100) AS LitersPer100Km
FROM FuelData AS FuelData1
INNER JOIN FuelData AS FuelData2
ON FuelData1.Id = (FuelData2.Id - 1);

I've created another issue for Anko to add one example to the documentation as it's really confusing: https://github.com/Kotlin/anko/issues/623