Making and using a foreign key for relation in SQLite Android

Question

Suposse that i have an android studio App where i can add companies, and inside those companies i can also add employees. Now, i'm trying to get the employees list showing only the elements of the company I selected before (In an unique activity for showing employees).

I created two classes:

data class Company(val id:Int, val name:String, val telephone:String)
data class Employee (val id:Int, val name:String, val telephone:String, val idCompany:Int)

And in my SQLiteHelper I created the tables for each one, with a foreign key in Employees to make a relation

    private val TABLE_COMPANY = "CompanyTable"
    private val TABLE_EMPLOYEE = "EmployeeTable"


    //Company table
    private val COMPANY_ID = "_id"
    private val COMPANY_NAME = "name"
    private val COMPANY_TL = "telephone"

    //Employee table
    private val EMPLOYEE_ID = "id"
    private val EMPLOYEE_NAME = "name"
    private val EMPLOYEE_TL = "telephone"
    private val EMPLOYEE_COMPANY_ID = "id"



}

override fun onCreate(db: SQLiteDatabase?) {

    val CREATE_COMPANY_TABLE = ("CREATE TABLE " + TABLE_COMPANY + "("
            + COMPANY_ID + " INTEGER PRIMARY KEY,"
            + COMPANY_NAME + " TEXT,"
            + COMPANY_TL + " TEXT" + ")")

    val CREATE_EMPLOYEE_TABLE = ("CREATE TABLE " + TABLE_EMPLOYEE + "("
            + EMPLOYEE_ID + " INTEGER PRIMARY KEY,"
            + EMPLOYEE_NAME + " TEXT,"
            + EMPLOYEE_TL + " INTEGER,"
            + EMPLOYEE_COMPANY_ID + " INTEGER,"
            + " FOREIGN KEY ("+ EMPLOYEE_COMPANY_ID+") REFERENCES "+TABLE_COMPANY+"("+ COMPANY_ID+"))")


    db?.execSQL(CREATE_EMPLOYEE_TABLE)
    db?.execSQL(CREATE_COMPANY_TABLE)

}

So, I made two activities with recyclerviews, one for the Companies and the other for the employees. When i click a company, the employees activity opens and it shows a list of them.

But it shows all the employees i have, so i'm trying to figure out how to show only the ones that i saved with the same id of the Company that i clicked in the previous activity.

But i don't know how to proceed now

Here is the DAO function that shows the employees:

fun viewEmployee(): ArrayList<Employee> {

    val empList: ArrayList<Employee> = ArrayList<Employee>()

    // Query to select the records
    val selectQuery = "SELECT L.$EMPLOYEE_NAME, L.$EMPLOYEE_TL, L.$EMPLOYEE_ID, L.$EMPLOYEE_COMPANY_ID, C.$COMPANY_ID" +
            "FROM $TABLE_EMPLOYEE as L, $TABLE_COMPANY as C" +
            "WHERE L.$EMPLOYEE_COMPANY_ID = C.$COMPANY_ID"



    val db = this.readableDatabase
   
    var cursor: Cursor? = null

    try {
        cursor = db.rawQuery(selectQuery, null)

    } catch (e: SQLiteException) {
        db.execSQL(selectQuery)
        return ArrayList()
    }

    var id: Int
    var name: String
    var telephone: String
    var idCompany: Int

    if (cursor.moveToFirst()) {
        do {
            id = cursor.getInt(cursor.getColumnIndex(EMPLOYEE_ID))
            name = cursor.getString(cursor.getColumnIndex(EMPLOYEE_NAME))
            telephone = cursor.getString(cursor.getColumnIndex(EMPLOYEE_TL))
            idCompany = cursor.getInt(cursor.getColumnIndex(EMPLOYEE_COMPANY_ID))

            val employee = Employee(id = id, name = name, telephone = telephone, idCompany = idCompany)
            empList.add(employee)

        } while (cursor.moveToNext())
    }
    return empList
}

And here is the activity that shows the employees

class ManagerEmp : AppCompatActivity() {




override fun onCreate(savedInstanceState: Bundle?) {
    super.onCreate(savedInstanceState)
    setContentView(R.layout.activity_manager_emp)

    setList()
    
    createEmp.setOnClickListener{
        val intent = Intent(this, CreateEmp::class.java)
        startActivity(intent)
    }
}





/**
 * Gets DB employee list
 */
private fun getItemsList(): ArrayList<Employee> {
    //creating the instance of DatabaseHandler class
    val databaseHandler = DataBaseHelper(this)
    //calling the viewEmployee method of DatabaseHandler class to read the records
    val empList: ArrayList<Employee> = databaseHandler.viewEmployee()

    return empList
}



/**
 * Generates the list
 */
private fun setList() {

    recyclerEmp.layoutManager = LinearLayoutManager(this)
    val itemAdapter = empAdapter(this, getItemsList())
    recyclerEmp.adapter = itemAdapter
}

}

It sounds simple but it isn't (At least for me) I thought of getting the id value of the company and pass it to the employee list activity, so i can use to compare it, but idk how, i'm pretty new in kotlin (And programming in general)

If you can give an answer, you would be saving my life.

H E L P

Answer 1

First you have to obtain companyId you want to filter by. It can be done for example by adding onClickListener in RecyclerView.

Then you will need additional query which will be used for filtering by companyId.

For example if companyId is stored in filteredCompanyId variable you can add additional filter condition (WHERE ... AND C.$COMPANY_ID == $filteredCompanyId):

val filteredCompanyId = 5
val query = """
SELECT L.$EMPLOYEE_NAME, 
       L.$EMPLOYEE_TL, 
       L.$EMPLOYEE_ID, 
       L.$EMPLOYEE_COMPANY_ID, 
       C.$COMPANY_ID 
FROM   $TABLE_EMPLOYEE AS L, 
       $TABLE_COMPANY AS C 
WHERE  L.$EMPLOYEE_COMPANY_ID = C.$COMPANY_ID 
       AND C.$COMPANY_ID == $filteredCompanyId
"""