f

    Farzad

    3 years ago
    Hi, is there a way to select from multiple tables using DSL without joining them? For example:
    select *
    from Orders o,
         Customers c
    where o.customerId = c.id
      and c.id = 1234;
    christophsturm

    christophsturm

    3 years ago
    is that even valid sql?
    f

    Farzad

    3 years ago
    That's just a gist of what I want to achieve, the query will have a
    where
    condition and selected fields. Edited the query to something that makes more sense.
    The only reason I don't want to use
    Join
    is because I'm working on a legacy db where tables are not designed properly which causes the join to be slower than a normal select
    h

    hho

    3 years ago
    @christophsturm
    select * from foo, bar
    is valid SQL and equivalent to
    select * from foo cross join bar
    . It results in the cartesian product.
    @Farzad So basically, you can't select from multiple tables without joining, because that's not possible in SQL. You can try to use
    crossJoin
    to achieve what you want, but I'd recommend just rewriting your query to
    SELECT *
    FROM Orders o JOIN Customers c ON o.customerId=c.id
    WHERE c.id = 1234;
    f

    Farzad

    3 years ago
    Yeah that's initially what I did but again because of the table structure it slowed down the query significantly. You can select from multiple tables in SQL, just need to write the correct where condition. Although I don't know how to achieve it using Exposed DSL
    h

    hho

    3 years ago
    Yes, you can select from multiple tables in SQL, but not without joining. Your query is doing a
    cross join
    .
    f

    Farzad

    3 years ago
    Yes seems like cross join achieves the same thing, thanks! I have a couple of more questions but I'll ask in a separate thread
    tapac

    tapac

    3 years ago
    You may try to make your own version of join like :
    class SimpleJoin(private vararg val tables: Table) : ColumnSet() {
        override val columns: List<Column<*>>
            get() = tables.flatMap { it.columns }
    
        override fun describe(s: Transaction, queryBuilder: QueryBuilder): String = tables.joinToString { it.describe(s, queryBuilder) }
    
        override fun join(otherTable: ColumnSet, joinType: JoinType, onColumn: Expression<*>?, otherColumn: Expression<*>?, additionalConstraint: (SqlExpressionBuilder.() -> Op<Boolean>)?): Join {
            TODO("not implemented")
        }
    
        override fun innerJoin(otherTable: ColumnSet): Join = TODO("not implemented")
    
        override fun leftJoin(otherTable: ColumnSet): Join = TODO("not implemented")
    
        override fun crossJoin(otherTable: ColumnSet): Join = TODO("not implemented")
    }
    and use it like:
    SimpleJoin(Orders, Customers).select {
    Orders.customerId eq Customers.id and (Customers.id eq 1234)
    }
    I didn't test that code, so please let me know if that helps