is there any clean way to use joining two tables in SqlDelight in android?

Question

For example: the Employee table would have columns such as empId, name, and the Course table would have columns empId, courseId, courseName.

getEmployee:

SELECT * 
FROM EMPLOYEE 
LEFT JOIN COURSE ON EMPLOYEE.empId = COURSE.empId 
WHERE dept = 'Sales'

The result of this query will be a data class containing both table's columns - like:

GetEmployee
(
    empId : String,
    name : String,
    courseId : String?, 
    courseName : String?
)

Note: the second table's columns become nullable types, so I get to check all the columns if they are null.

When I map these to an usable data class code becomes too messy, is there any better approach to handle relations, joins in sqldelight 🤧?

I was expecting the second tables data will be a list inside a data class of first table.

Answer 1

That's just how SQL works and sqldelight only creates what your queries return. So in your example you have these tables:

Employee:

| empId | name |
|-------|------|
| ...   | ...  |

Course:

| courseId | courseName | empId |
|----------|------------|-------|
| ...      | ...        | ...   |

When you use your LEFT JOIN query you get the following result from SQL:

| empId | name | courseId | courseName | empId |
|-------|------|----------|------------|-------|
| ...   | ...  | ...      | ...        | ...   |

So sqldelight runs this query, gets this result and maps this to a class it has generated for this query with all the columns. The columns from the course table are nullable, because LEFT JOIN returns ALL rows from the employee table and ONLY those rows from the course table where the join condition is met (EMPLOYEE.empId = COURSE.empId). This means you can have employes who don't have any courses, therefore the colums need to be nullable.

So you need to map the result yourself, if you want a better representation of you data. This is not that hard, you can do something like this:

val queryResult = database.employeeQueries.getEmployee().exectueAsList()

queryResult.groupBy { it.id }.map { map ->
    val first = map.value.first()

    EmployeeCourse(
        id = first.empId,
        name = first.name,
        courses = map.value.filter { it.id_ != null }.map {
            Course(
                courseId = it.courseId!!,
                courseName = it.courseName!!,
                empId = it.empId_!!
            )
        }
    )
}

If you want to find only employees who have courses use a INNER JOIN, which creates an intersection with the two tables. This creates a class with non-nullable types.