github.com/octohelm/storage@v0.0.0-20240516030302-1ac2cc1ea347/pkg/sqlbuilder/addition_join_test.go (about)

     1  package sqlbuilder_test
     2  
     3  import (
     4  	"testing"
     5  
     6  	"github.com/octohelm/storage/internal/testutil"
     7  	. "github.com/octohelm/storage/pkg/sqlbuilder"
     8  )
     9  
    10  func TestJoin(t *testing.T) {
    11  	tUser := T("t_user",
    12  		Col("f_id", ColTypeOf(uint64(0), ",autoincrement")),
    13  		Col("f_name", ColTypeOf("", ",size=128,default=''")),
    14  		Col("f_org_id", ColTypeOf("", ",size=128,default=''")),
    15  	)
    16  
    17  	tOrg := T("t_org",
    18  		Col("f_org_id", ColTypeOf(uint64(0), ",autoincrement")),
    19  		Col("f_org_name", ColTypeOf("", ",size=128,default=''")),
    20  	)
    21  
    22  	t.Run("JOIN ON", func(t *testing.T) {
    23  		testutil.ShouldBeExpr(t,
    24  			Select(MultiWith(", ",
    25  				Alias(tUser.F("f_id"), "f_id"),
    26  				Alias(tUser.F("f_name"), "f_name"),
    27  				Alias(tUser.F("f_org_id"), "f_org_id"),
    28  				Alias(tOrg.F("f_org_name"), "f_org_name"),
    29  			)).From(
    30  				tUser,
    31  				Join(Alias(tOrg, "t_org")).On(
    32  					TypedColOf[int](tUser, "f_org_id").V(
    33  						EqCol(TypedColOf[int](tOrg, "f_org_id")),
    34  					),
    35  				),
    36  			),
    37  			`
    38  SELECT t_user.f_id AS f_id, t_user.f_name AS f_name, t_user.f_org_id AS f_org_id, t_org.f_org_name AS f_org_name FROM t_user
    39  JOIN t_org AS t_org ON t_user.f_org_id = t_org.f_org_id
    40  `,
    41  		)
    42  	})
    43  	t.Run("JOIN USING", func(t *testing.T) {
    44  		testutil.ShouldBeExpr(t,
    45  			Select(nil).
    46  				From(
    47  					tUser,
    48  					Join(tOrg).Using(tUser.F("f_org_id")),
    49  				),
    50  			`
    51  SELECT * FROM t_user
    52  JOIN t_org USING (f_org_id)
    53  `,
    54  		)
    55  	})
    56  }