*Closed* Kotlin Exposed - innerJoin when null

Support
1

I’m working an an api and want to query some data from my database. In this case the table has multiple references to other tables and I need some information from them so I’m using “.innerJoin”.

suspend fun readNew(): List<ResponseServiceOrder> {
        return dbQuery {
            ServiceOrderTable
                .innerJoin(CustomerService.CustomerTable)
                .innerJoin(ClientService.ClientTable)
                .innerJoin(UserService.Users)
                .innerJoin(MachineService.MachineTable)
                .selectAll().map { mapToServiceOrderResponse(it) }
        }
    }

This works generally as expected but the client is optional and in my case a “null”. This is where my problem lies. It will go through this function and stop at “.innerJoin(ClientService.ClientTable)”. It does not throw an exception it just stops and the rest of the function is not being executed.

Maybe someone has had this problem and can provide a solution?

Thank you in advance!

2

What does it mean it is “null”? The data in the table is null? How do you observe it stops at the line?

3

This is the line from the ServiceOrderTable table

val clientId = integer("client_id").references(ClientService.ClientTable.id, onDelete = ReferenceOption.RESTRICT).nullable()

This can be null. I guess it stops because there is no clientId given but I want it to ignore that and keep going.
I can observe that it stops by using the debugger. If I put a put a breakpoint after “.innerJoin(ClientService.ClientTable)” it does not stop.

Correction: I tested it again with the debugger and it executes everything but the “mapToServiceOrderResponse(it)” function, if “clientId” is null.
I added another entry into the database that has a value for “clientId” and it calls the function.

1 Like
4

I looked into the source code of the join function itself. I was mainly wondering why it returned an empty list when the input was null. Here is the reason:

    override fun join(
        otherTable: ColumnSet,
        joinType: JoinType,
        onColumn: Expression<*>?,
        otherColumn: Expression<*>?,
        additionalConstraint: (SqlExpressionBuilder.() -> Op<Boolean>)?
    ): Join {
        val cond = if (onColumn != null && otherColumn != null) {
            listOf(JoinCondition(onColumn, otherColumn))
        } else {
            emptyList()
        }
        return join(otherTable, joinType, cond, additionalConstraint)
    }

    override infix fun innerJoin(otherTable: ColumnSet): Join = implicitJoin(otherTable, JoinType.INNER)

I’m not experienced enough with Kotlin, can I customize that behavior maybe with an extension function to just skip the join function in case of null?

5

I found a solution. I just used “.leftJoin” instead of “.innerJoin” and it does exactly what I wanted.
Now that I know that its an easy solution but I was getting frustrated that something like this is not part of the documentation and I still don’t know the real difference between leftJoin, innerJoin and rightJoin.

1 Like
6

Hi sgomer,
I think the reason that the documentation does not describe the join types in detail is that they are established terms for databases (SQL) - so these names (e.g. “inner join”) are not specific to Kotlin.
One explanation of the difference between the types can be found e.g. at

(section “Different Types of SQL JOINs”)

Generally, knowing that it are terms from SQL, you can find a lot of explanation articles when searching for “SQL join types”.

I hope this helps,
live long and prosper,
tlin47

2 Likes
7

I don’t know the specific library you’re using. But I’ll try to answer for relational databases in general.

Suppose you’re joining table L to table R, on a column ‘joinColumn’ that both tables have. The result of that query is a list of rows containing (some of) the columns from L and also (some of) the columns from R.

In all cases, the results will include every combination of rows from L and R which have matching (non-null) values for ‘joinColumn’. The different types of join differ on whether they include unmatched rows too:

  • An inner join (which is usually the default) returns only the matched rows.

  • A left join returns all rows from table L, including any unmatched ones (with NULLs where the values from table R would go).

  • A right join returns all rows from table R, including any unmatched ones (with NULLs where the values from table L would go).

  • An outer join returns all rows from both tables (with NULLs filling in the missing values on either side where they weren’t matched).

A trivial example may help. Suppose we have two tables, with just a couple of rows each:

Table L:

+------+---------+
| keyL | joinCol |
+------+---------+
|    1 | A       |
|    2 | B       |
+------+---------+

Table R:

+------+---------+
| keyR | joinCol |
+------+---------+
|   10 | A       |
|   11 | C       |
+------+---------+

Both tables have a row with ‘A’ for joinCol, so a simple inner join of L to R on joinCol would give just those:

+---------+------+------+
| joinCol | keyL | keyR |
+---------+------+------+
| A       |    1 |   10 |
+---------+------+------+

While a left join would also give the other row from L:

+---------+------+------+
| joinCol | keyL | keyR |
+---------+------+------+
| A       |    1 |   10 |
| B       |    2 | NULL |
+---------+------+------+

Similarly, a right join would include R’s unmatched row:

+---------+------+------+
| joinCol | keyL | keyR |
+---------+------+------+
| A       |    1 |   10 |
| C       | NULL |   11 |
+---------+------+------+

And finally, a full outer join would include all of those:

+---------+------+------+
| joinCol | keyL | keyR |
+---------+------+------+
| A       |    1 |   10 |
| B       |    2 | NULL |
| C       | NULL |   11 |
+---------+------+------+

In my experience, inner joins are most common; it’s also fairly common to use left joins to pull in extra info from a lookup table or a parent table which might not include the value you’re looking for. I can’t recall ever using a right join or an outer join, though.

1 Like