github.com/yongjacky/phoenix-go-orm-builder@v0.3.5/builder_join_test.go (about)

     1  // Copyright 2018 The Xorm Authors. All rights reserved.
     2  // Use of this source code is governed by a BSD-style
     3  // license that can be found in the LICENSE file.
     4  
     5  package builder
     6  
     7  import (
     8  	"testing"
     9  
    10  	"github.com/stretchr/testify/assert"
    11  )
    12  
    13  func TestJoin(t *testing.T) {
    14  	sql, args, err := Select("c, d").From("table1").LeftJoin("table2", Eq{"table1.id": 1}.And(Lt{"table2.id": 3})).
    15  		RightJoin("table3", "table2.id = table3.tid").Where(Eq{"a": 1}).ToSQL()
    16  	assert.NoError(t, err)
    17  	assert.EqualValues(t, "SELECT c, d FROM table1 LEFT JOIN table2 ON table1.id=? AND table2.id<? RIGHT JOIN table3 ON table2.id = table3.tid WHERE a=?",
    18  		sql)
    19  	assert.EqualValues(t, []interface{}{1, 3, 1}, args)
    20  
    21  	sql, args, err = Select("c, d").From("table1").LeftJoin("table2", Eq{"table1.id": 1}.And(Lt{"table2.id": 3})).
    22  		FullJoin("table3", "table2.id = table3.tid").Where(Eq{"a": 1}).ToSQL()
    23  	assert.NoError(t, err)
    24  	assert.EqualValues(t, "SELECT c, d FROM table1 LEFT JOIN table2 ON table1.id=? AND table2.id<? FULL JOIN table3 ON table2.id = table3.tid WHERE a=?",
    25  		sql)
    26  	assert.EqualValues(t, []interface{}{1, 3, 1}, args)
    27  
    28  	sql, args, err = Select("c, d").From("table1").LeftJoin("table2", Eq{"table1.id": 1}.And(Lt{"table2.id": 3})).
    29  		CrossJoin("table3", "table2.id = table3.tid").Where(Eq{"a": 1}).ToSQL()
    30  	assert.NoError(t, err)
    31  	assert.EqualValues(t, "SELECT c, d FROM table1 LEFT JOIN table2 ON table1.id=? AND table2.id<? CROSS JOIN table3 ON table2.id = table3.tid WHERE a=?",
    32  		sql)
    33  	assert.EqualValues(t, []interface{}{1, 3, 1}, args)
    34  
    35  	sql, args, err = Select("c, d").From("table1").LeftJoin("table2", Eq{"table1.id": 1}.And(Lt{"table2.id": 3})).
    36  		InnerJoin("table3", "table2.id = table3.tid").Where(Eq{"a": 1}).ToSQL()
    37  	assert.NoError(t, err)
    38  	assert.EqualValues(t, "SELECT c, d FROM table1 LEFT JOIN table2 ON table1.id=? AND table2.id<? INNER JOIN table3 ON table2.id = table3.tid WHERE a=?",
    39  		sql)
    40  	assert.EqualValues(t, []interface{}{1, 3, 1}, args)
    41  
    42  	subQuery2 := Select("e").From("table2").Where(Gt{"e": 1})
    43  	subQuery3 := Select("f").From("table3").Where(Gt{"f": "2"})
    44  	sql, args, err = Select("c, d").From("table1").LeftJoin(subQuery2, Eq{"table1.id": 1}.And(Lt{"table2.id": 3})).
    45  		InnerJoin(subQuery3, "table2.id = table3.tid").Where(Eq{"a": 1}).ToSQL()
    46  	assert.NoError(t, err)
    47  	assert.EqualValues(t, "SELECT c, d FROM table1 LEFT JOIN (SELECT e FROM table2 WHERE e>?) ON table1.id=? AND table2.id<? INNER JOIN (SELECT f FROM table3 WHERE f>?) ON table2.id = table3.tid WHERE a=?",
    48  		sql)
    49  	assert.EqualValues(t, []interface{}{1, 1, 3, "2", 1}, args)
    50  }