Hello! We have the following inheritance hierarchy...

# exposed

[Kristian Nilsen]

Hello! We have the following inheritance hierarchy between some DAO classes: A > B > C

"A" contains the following joins:

val aJoins: () -> Join = {
        EntityA.join(
            SomeEntity,
            joinType = JoinType.INNER,
            additionalConstraint = ...
        )
            .join(
                AnotherEntity,
                joinType = JoinType.INNER,
                additionalConstraint = ....
            )
            .join(
                SomeOtherEntity,
                joinType = JoinType.LEFT,
                additionalConstraint = ...
            )
    }

"B" contains the following joins:

val bJoins: () -> Join = {
    super.aJoins().join(
        EntityB,
        joinType = JoinType.INNER,
        onColumn = ...,
        otherColumn = ...
    ).join(
        TypeOfUsageParameterEntity,
        joinType = JoinType.LEFT,
        onColumn = ...,
        otherColumn = ...
    )
}

And finally, "C" contains the following join

private val query: () -> Query = {
        super.bJoins().join(
            EntityC,
            joinType = JoinType.INNER,
            onColumn = ...,
            otherColumn = ...
        ).selectAll()
    }

Thus, for a select all statement in C I depend on joins from both parent classes. Is there a better way to achieve this using the DSL?
Appreciate any help and pointers I can get

[Oleg Babichev]

Hi,

You mentioned DAO classes, but is the actual question about DSL queries?

Additionally, just to clarify, are you dealing with two types of SQL queries? The first one being a comprehensive query like:

select ...
from A
  join some ...
  join another ...
  join some_other ...
  join B ...
  join type_of_usage_parameter
  join C

And the second type where you only want entries from C, like:

select ...
from A
  join B ...
  join C ...

?

And the question is more about how to use both of these approaches with minimal code duplication?

If so, we can consider how to better organize the code. I tried to write a similar table structure and query it in two different ways. Finally, I wrote some extension functions to the tables that allow joining specific tables to others:

object A : IntIdTable() {
        fun Table.withAJoins() = joinOtherToA().joinSomeOtherToA().joinBToA()
    }

    object Other : IntIdTable() {
        val a = reference("a", A.id)

        fun Join.joinOtherToA() = join(Other, JoinType.LEFT, onColumn = Other.a, otherColumn = A.id)

        fun Table.joinOtherToA() = Join(this).joinOtherToA()
    }

    object SomeOther : IntIdTable() {
        val a = reference("a", A.id)

        fun Join.joinSomeOtherToA() = join(SomeOther, JoinType.LEFT, onColumn = SomeOther.a, otherColumn = A.id)

        fun Table.joinSomeOtherToA() = Join(this).joinSomeOtherToA()
    }

    object B : IntIdTable() {
        val a = reference("a", A.id)

        fun Join.joinBToA() = join(B, JoinType.LEFT, onColumn = B.a, otherColumn = A.id)

        fun Table.joinBToA() = Join(this).joinBToA()

        fun Join.withBJoins() = joinTypeOfUsageParameterToB().joinCToB()
    }

    object TypeOfUsageParameter : IntIdTable() {
        val b = reference("b", B.id)

        fun Join.joinTypeOfUsageParameterToB() = join(TypeOfUsageParameter, JoinType.LEFT, onColumn = TypeOfUsageParameter.b, otherColumn = B.id)
    }

    object C : IntIdTable() {
        val b = reference("b", B.id)

        fun Join.joinCToB() = join(C, JoinType.LEFT, onColumn = C.b, otherColumn = B.id)
    }

    fun test() {
        A
            .join(B, JoinType.LEFT, onColumn = B.a, otherColumn = A.id)
            .join(C, JoinType.LEFT, onColumn = C.b, otherColumn = B.id)

        val fullQuery = A.withAJoins().withBJoins()

        val shortQuery = A.joinBToA().joinCToB()
    }

The join<Table1>To<Table2> functions help organize possible joins. The with<Table>Joins methods know all possible joins to the current table and allow for getting a full set of joins for it.

Thus, the full join will look like A.withAJoins().withBJoins() and the shorter one like A.joinBToA().joinCToB(). If you need all joins of A but not the joins of B, you can use A.withAJoins().joinCToB().

This is just one way to organize large queries. It would be great if others who have faced similar issues could share their solutions or thoughts.

[Kristian Nilsen]

Hi, @Oleg Babichev. I'm really sorry about the late reply. Thank you so much for taking the time to come up with a suggestion; I tried it out, and it works like charm and made our code way easier to read and more maintainable than before 🙌

[Oleg Babichev]

You're welcome!

Glad to hear that it was useful. It's actually still an open question of organization complex table structures, so I'd be happy to know about any other variants.