Improve creating multiple table joins #6

Closed
hisystems opened this Issue Mar 4, 2012 · 1 comment

Projects

None yet

1 participant

@hisystems
Owner

Currently, the multiple table joins are supported, however it is not very intuitive when creating two or more table joins. Creating one table join is somewhat intuitive:

        Dim table1 As New SQLSelectTable("Table1")
        Dim table2 As New SQLSelectTable("Table2")

        Dim selectStatement As New SQLSelect()
        With selectStatement.Tables.Joins.Add(table1, SQLSelectTableJoin.Type.Inner, table2)
            .Where.Add("Table1Key", SQL.ComparisonOperator.EqualTo, "Table2Key")
        End With

However, creating multiple joins is not, because the second join is really based on the results of the first join, and not really from table2 to table3 as the code reads. The join is from table1+table2 to table3:

        Dim table1 As New SQLSelectTable("Table1")
        Dim table2 As New SQLSelectTable("Table2")
        Dim table3 As New SQLSelectTable("Table3")

        Dim selectStatement As New SQLSelect()
        selectStatement.Tables.Add(table1)

        With selectStatement.Tables.Joins.Add(table1, SQLSelectTableJoin.Type.Inner, table2)
            .Where.Add("Table1Key", SQL.ComparisonOperator.EqualTo, "Table2Key")
        End With

        With selectStatement.Tables.Joins.Add(table2, SQLSelectTableJoin.Type.Inner, table3)
            .Where.Add("Table2Key", SQL.ComparisonOperator.EqualTo, "Table3Key")
        End With
SELECT * FROM 
(([Table1] INNER JOIN [Table2] ON [Table1].[Table1Key] = [Table2].[Table2Key]) 
INNER JOIN [Table3] ON [Table2].[Table2Key] = [Table3].[Table3Key])
@hisystems hisystems was assigned Mar 4, 2012
@hisystems
Owner

The best solution would be to still utilise the SQLSelectTableJoins collection, but instead from the SQLSelectTableJoin add a function called JoinTo. This will maintain the existing API while providing a more intuitive approach. For example:

Dim table1 As New SQLSelectTable("Table1")
Dim table2 As New SQLSelectTable("Table2")
Dim table3 As New SQLSelectTable("Table3")

Dim selectStatement As New SQLSelect()
selectStatement.Tables.Add(table1)

Dim table1Table2Join = selectStatement.Tables.Joins.Add(table1, SQLSelectTableJoin.Type.Inner, table2)
table1Table2Join.Where.Add("Table1Key", SQL.ComparisonOperator.EqualTo, "Table2Key")

Dim table2Table3Join = table1Table2Join.JoinTo(SQLSelectTableJoin.Type.Inner, table2)
table2Table3Join.Where.Add("Table2Key", SQL.ComparisonOperator.EqualTo, "Table3Key")

The only issue will be that the left field name for the second join will be unknown, in which case the table joins should be changed to use an SQLFieldExpression (which also indicates the associated table).

@hisystems hisystems added a commit that closed this issue Mar 4, 2012
@hisystems Improved creating multiple table joins. Closes #6. Closes #7.
Creating a single table join remains the same, there is no breaking change to the API. However, creating two or more table joins is now more intuitive. The return value from 'table1AndTable2 = sqlSelect.Tables.Joins.Add(table1, Type.Inner, table2)' can then be used in subsequent table join calls i.e. 'sqlSelect.Tables.Joins.Add(table1AndTable2 , Type.Inner, table3)'. This indicates that the table1AndTable2 join created in the first step is then joined to table3.

Also changed the SQLSelectTableJoinCondition to accept SQLExpression objects rather than the left and right table field names. This was required because now that the left table for a join can be either a table or a joined table the left field table alias may be unknown if it is an table join. In which case the table alias must come from an SQLFieldExpression (inherits SQLExpression) which will indicate the table alias. The way it was working where it would obtain the table name from the join was incorrect. So also closing issue #7.
6f65497
@hisystems hisystems closed this in 6f65497 Mar 4, 2012
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment