github.com/ecodeclub/eorm@v0.0.2-0.20231001112437-dae71da914d0/select_test.go (about)

     1  // Copyright 2021 ecodeclub
     2  //
     3  // Licensed under the Apache License, Version 2.0 (the "License");
     4  // you may not use this file except in compliance with the License.
     5  // You may obtain a copy of the License at
     6  //
     7  // http://www.apache.org/licenses/LICENSE-2.0
     8  //
     9  // Unless required by applicable law or agreed to in writing, software
    10  // distributed under the License is distributed on an "AS IS" BASIS,
    11  // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    12  // See the License for the specific language governing permissions and
    13  // limitations under the License.
    14  
    15  package eorm
    16  
    17  import (
    18  	"context"
    19  	"database/sql"
    20  	"fmt"
    21  	"testing"
    22  
    23  	"github.com/ecodeclub/eorm/internal/datasource/single"
    24  
    25  	"github.com/DATA-DOG/go-sqlmock"
    26  	"github.com/ecodeclub/eorm/internal/errs"
    27  	"github.com/ecodeclub/eorm/internal/test"
    28  	"github.com/stretchr/testify/assert"
    29  	"github.com/stretchr/testify/require"
    30  )
    31  
    32  func TestRawQuery_Get_baseType(t *testing.T) {
    33  	mockDB, mock, err := sqlmock.New(
    34  		sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
    35  	if err != nil {
    36  		t.Fatal(err)
    37  	}
    38  	defer func() { _ = mockDB.Close() }()
    39  	db, err := OpenDS("mysql", single.NewDB(mockDB))
    40  	if err != nil {
    41  		t.Fatal(err)
    42  	}
    43  
    44  	testCases := []struct {
    45  		name      string
    46  		queryRes  func(t *testing.T) any
    47  		mockErr   error
    48  		mockOrder func(mock sqlmock.Sqlmock)
    49  		wantErr   error
    50  		wantVal   any
    51  	}{
    52  		{
    53  			name: "res RawQuery int",
    54  			queryRes: func(t *testing.T) any {
    55  				queryer := RawQuery[int](db, "SELECT `age` FROM `test_model` AS `t1` LIMIT ?;", 1)
    56  				result, err := queryer.Get(context.Background())
    57  				require.NoError(t, err)
    58  				return result
    59  			},
    60  			mockOrder: func(mock sqlmock.Sqlmock) {
    61  				rows := mock.NewRows([]string{"age"}).AddRow(10)
    62  				mock.ExpectQuery("SELECT `age` FROM `test_model` AS `t1` LIMIT ?;").
    63  					WithArgs(1).
    64  					WillReturnRows(rows)
    65  			},
    66  			wantVal: func() *int {
    67  				val := 10
    68  				return &val
    69  			}(),
    70  		},
    71  		{
    72  			name: "res RawQuery bytes",
    73  			queryRes: func(t *testing.T) any {
    74  				queryer := RawQuery[[]byte](db, "SELECT `first_name` FROM `test_model` WHERE `id`=? LIMIT ?;", 1, 1)
    75  				result, err := queryer.Get(context.Background())
    76  				require.NoError(t, err)
    77  				return result
    78  			},
    79  			mockOrder: func(mock sqlmock.Sqlmock) {
    80  				rows := mock.NewRows([]string{"first_name"}).AddRow([]byte("Li"))
    81  				mock.ExpectQuery("SELECT `first_name` FROM `test_model` WHERE `id`=? LIMIT ?;").
    82  					WithArgs(1, 1).
    83  					WillReturnRows(rows)
    84  			},
    85  			wantVal: func() *[]byte {
    86  				val := []byte("Li")
    87  				return &val
    88  			}(),
    89  		},
    90  		{
    91  			name: "res RawQuery string",
    92  			queryRes: func(t *testing.T) any {
    93  				queryer := RawQuery[string](db, "SELECT `first_name` FROM `test_model` WHERE `id`=? LIMIT ?;", 1, 1)
    94  				result, err := queryer.Get(context.Background())
    95  				require.NoError(t, err)
    96  				return result
    97  			},
    98  			mockOrder: func(mock sqlmock.Sqlmock) {
    99  				rows := mock.NewRows([]string{"first_name"}).AddRow("Da")
   100  				mock.ExpectQuery("SELECT `first_name` FROM `test_model` WHERE `id`=? LIMIT ?;").
   101  					WithArgs(1, 1).
   102  					WillReturnRows(rows)
   103  			},
   104  			wantVal: func() *string {
   105  				val := "Da"
   106  				return &val
   107  			}(),
   108  		},
   109  		{
   110  			name: "res RawQuery sql.NullString",
   111  			queryRes: func(t *testing.T) any {
   112  				queryer := RawQuery[sql.NullString](db, "SELECT `last_name` FROM `test_model` WHERE `id`=? LIMIT ?;", 1, 1)
   113  				result, err := queryer.Get(context.Background())
   114  				require.NoError(t, err)
   115  				return result
   116  			},
   117  			mockOrder: func(mock sqlmock.Sqlmock) {
   118  				rows := mock.NewRows([]string{"last_name"}).AddRow([]byte("ming"))
   119  				mock.ExpectQuery("SELECT `last_name` FROM `test_model` WHERE `id`=? LIMIT ?;").
   120  					WithArgs(1, 1).
   121  					WillReturnRows(rows)
   122  			},
   123  			wantVal: func() *sql.NullString {
   124  				return &sql.NullString{String: "ming", Valid: true}
   125  			}(),
   126  		},
   127  	}
   128  
   129  	for _, tc := range testCases {
   130  		t.Run(tc.name, func(t *testing.T) {
   131  			tc.mockOrder(mock)
   132  			res := tc.queryRes(t)
   133  			assert.Equal(t, tc.wantVal, res)
   134  		})
   135  	}
   136  }
   137  
   138  func TestRawQuery_GetMulti_baseType(t *testing.T) {
   139  	mockDB, mock, err := sqlmock.New(
   140  		sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
   141  	if err != nil {
   142  		t.Fatal(err)
   143  	}
   144  	defer func() { _ = mockDB.Close() }()
   145  	db, err := OpenDS("mysql", single.NewDB(mockDB))
   146  	if err != nil {
   147  		t.Fatal(err)
   148  	}
   149  
   150  	testCases := []struct {
   151  		name      string
   152  		queryRes  func(t *testing.T) any
   153  		mockErr   error
   154  		mockOrder func(mock sqlmock.Sqlmock)
   155  		wantErr   error
   156  		wantVal   any
   157  	}{
   158  		{
   159  			name: "res int",
   160  			queryRes: func(t *testing.T) any {
   161  				queryer := RawQuery[int](db, "SELECT `age` FROM `test_model`;")
   162  				result, err := queryer.GetMulti(context.Background())
   163  				require.NoError(t, err)
   164  				return result
   165  			},
   166  			mockOrder: func(mock sqlmock.Sqlmock) {
   167  				rows := mock.NewRows([]string{"age"}).AddRow(10).
   168  					AddRow(18).AddRow(22)
   169  				mock.ExpectQuery("SELECT `age` FROM `test_model`;").
   170  					WillReturnRows(rows)
   171  			},
   172  			wantVal: func() (res []*int) {
   173  				vals := []int{10, 18, 22}
   174  				for i := 0; i < len(vals); i++ {
   175  					res = append(res, &vals[i])
   176  				}
   177  				return
   178  			}(),
   179  		},
   180  		{
   181  			name: "res byte",
   182  			queryRes: func(t *testing.T) any {
   183  				queryer := RawQuery[byte](db, "SELECT `first_name` FROM `test_model`;")
   184  				result, err := queryer.GetMulti(context.Background())
   185  				require.NoError(t, err)
   186  				return result
   187  			},
   188  			mockOrder: func(mock sqlmock.Sqlmock) {
   189  				rows := mock.NewRows([]string{"first_name"}).AddRow('D').AddRow('a')
   190  				mock.ExpectQuery("SELECT `first_name` FROM `test_model`;").
   191  					WillReturnRows(rows)
   192  			},
   193  			wantVal: func() (res []*byte) {
   194  				vals := []byte{'D', 'a'}
   195  				for i := 0; i < len(vals); i++ {
   196  					res = append(res, &vals[i])
   197  				}
   198  				return
   199  			}(),
   200  		},
   201  		{
   202  			name: "res bytes",
   203  			queryRes: func(t *testing.T) any {
   204  				queryer := RawQuery[[]byte](db, "SELECT `first_name` FROM `test_model`;")
   205  				result, err := queryer.GetMulti(context.Background())
   206  				require.NoError(t, err)
   207  				return result
   208  			},
   209  			mockOrder: func(mock sqlmock.Sqlmock) {
   210  				rows := mock.NewRows([]string{"first_name"}).AddRow([]byte("Li")).AddRow([]byte("Liu"))
   211  				mock.ExpectQuery("SELECT `first_name` FROM `test_model`;").
   212  					WillReturnRows(rows)
   213  			},
   214  			wantVal: func() (res []*[]byte) {
   215  				vals := [][]byte{[]byte("Li"), []byte("Liu")}
   216  				for i := 0; i < len(vals); i++ {
   217  					res = append(res, &vals[i])
   218  				}
   219  				return
   220  			}(),
   221  		},
   222  		{
   223  			name: "res string",
   224  			queryRes: func(t *testing.T) any {
   225  				queryer := RawQuery[string](db, "SELECT `first_name` FROM `test_model`;")
   226  				result, err := queryer.GetMulti(context.Background())
   227  				require.NoError(t, err)
   228  				return result
   229  			},
   230  			mockOrder: func(mock sqlmock.Sqlmock) {
   231  				rows := mock.NewRows([]string{"first_name"}).AddRow("Da").AddRow("Li")
   232  				mock.ExpectQuery("SELECT `first_name` FROM `test_model`;").
   233  					WillReturnRows(rows)
   234  			},
   235  			wantVal: func() (res []*string) {
   236  				vals := []string{"Da", "Li"}
   237  				for i := 0; i < len(vals); i++ {
   238  					res = append(res, &vals[i])
   239  				}
   240  				return
   241  			}(),
   242  		},
   243  		{
   244  			name: "res sql.NullString",
   245  			queryRes: func(t *testing.T) any {
   246  				queryer := RawQuery[sql.NullString](db, "SELECT `last_name` FROM `test_model`;")
   247  				result, err := queryer.GetMulti(context.Background())
   248  				require.NoError(t, err)
   249  				return result
   250  			},
   251  			mockOrder: func(mock sqlmock.Sqlmock) {
   252  				rows := mock.NewRows([]string{"last_name"}).
   253  					AddRow([]byte("ming")).AddRow([]byte("gang"))
   254  				mock.ExpectQuery("SELECT `last_name` FROM `test_model`;").
   255  					WillReturnRows(rows)
   256  			},
   257  			wantVal: []*sql.NullString{
   258  				{
   259  					String: "ming",
   260  					Valid:  true,
   261  				},
   262  				{
   263  					String: "gang",
   264  					Valid:  true,
   265  				},
   266  			},
   267  		},
   268  	}
   269  
   270  	for _, tc := range testCases {
   271  		t.Run(tc.name, func(t *testing.T) {
   272  			tc.mockOrder(mock)
   273  			res := tc.queryRes(t)
   274  			assert.EqualValues(t, tc.wantVal, res)
   275  		})
   276  	}
   277  }
   278  
   279  func TestSelector_Get_baseType(t *testing.T) {
   280  	mockDB, mock, err := sqlmock.New(
   281  		sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
   282  	if err != nil {
   283  		t.Fatal(err)
   284  	}
   285  	defer func() { _ = mockDB.Close() }()
   286  	db, err := OpenDS("mysql", single.NewDB(mockDB))
   287  	if err != nil {
   288  		t.Fatal(err)
   289  	}
   290  
   291  	testCases := []struct {
   292  		name      string
   293  		queryRes  func(t *testing.T) (any, error)
   294  		mockErr   error
   295  		mockOrder func(mock sqlmock.Sqlmock)
   296  		wantErr   string
   297  		wantVal   any
   298  	}{
   299  		{
   300  			name: "res int",
   301  			queryRes: func(t *testing.T) (any, error) {
   302  				tm := TableOf(&TestModel{}, "t1")
   303  				queryer := NewSelector[int](db).Select(C("Age")).From(tm)
   304  				return queryer.Get(context.Background())
   305  			},
   306  			mockOrder: func(mock sqlmock.Sqlmock) {
   307  				rows := mock.NewRows([]string{"age"}).AddRow(10)
   308  				mock.ExpectQuery("SELECT `age` FROM `test_model` AS `t1` LIMIT ?;").
   309  					WithArgs(1).
   310  					WillReturnRows(rows)
   311  			},
   312  			wantVal: func() *int {
   313  				val := 10
   314  				return &val
   315  			}(),
   316  		},
   317  		{
   318  			name: "res int32",
   319  			queryRes: func(t *testing.T) (any, error) {
   320  				tm := TableOf(&TestModel{}, "t1")
   321  				queryer := NewSelector[int32](db).Select(C("Age")).From(tm)
   322  				return queryer.Get(context.Background())
   323  			},
   324  			mockOrder: func(mock sqlmock.Sqlmock) {
   325  				rows := mock.NewRows([]string{"age"}).AddRow(10)
   326  				mock.ExpectQuery("SELECT `age` FROM `test_model` AS `t1` LIMIT ?;").
   327  					WithArgs(1).
   328  					WillReturnRows(rows)
   329  			},
   330  			wantVal: func() *int32 {
   331  				val := int32(10)
   332  				return &val
   333  			}(),
   334  		},
   335  		{
   336  			name: "res int64",
   337  			queryRes: func(t *testing.T) (any, error) {
   338  				tm := TableOf(&TestModel{}, "t1")
   339  				queryer := NewSelector[int64](db).Select(C("Age")).From(tm)
   340  				return queryer.Get(context.Background())
   341  			},
   342  			mockOrder: func(mock sqlmock.Sqlmock) {
   343  				rows := mock.NewRows([]string{"age"}).AddRow(10)
   344  				mock.ExpectQuery("SELECT `age` FROM `test_model` AS `t1` LIMIT ?;").
   345  					WithArgs(1).
   346  					WillReturnRows(rows)
   347  			},
   348  			wantVal: func() *int64 {
   349  				val := int64(10)
   350  				return &val
   351  			}(),
   352  		},
   353  		{
   354  			name: "avg res float32",
   355  			queryRes: func(t *testing.T) (any, error) {
   356  				tm := TableOf(&TestModel{}, "t1")
   357  				queryer := NewSelector[float32](db).Select(C("Age")).From(tm)
   358  				return queryer.Get(context.Background())
   359  			},
   360  			mockOrder: func(mock sqlmock.Sqlmock) {
   361  				rows := mock.NewRows([]string{"age"}).AddRow(10.2)
   362  				mock.ExpectQuery("SELECT `age` FROM `test_model` AS `t1` LIMIT ?;").
   363  					WithArgs(1).
   364  					WillReturnRows(rows)
   365  			},
   366  			wantVal: func() *float32 {
   367  				val := float32(10.2)
   368  				return &val
   369  			}(),
   370  		},
   371  		{
   372  			name: "avg res float64",
   373  			queryRes: func(t *testing.T) (any, error) {
   374  				tm := TableOf(&TestModel{}, "t1")
   375  				queryer := NewSelector[float64](db).Select(C("Age")).From(tm)
   376  				return queryer.Get(context.Background())
   377  			},
   378  			mockOrder: func(mock sqlmock.Sqlmock) {
   379  				rows := mock.NewRows([]string{"age"}).AddRow(10.02)
   380  				mock.ExpectQuery("SELECT `age` FROM `test_model` AS `t1` LIMIT ?;").
   381  					WithArgs(1).
   382  					WillReturnRows(rows)
   383  			},
   384  			wantVal: func() *float64 {
   385  				val := 10.02
   386  				return &val
   387  			}(),
   388  		},
   389  		{
   390  			name: "res byte",
   391  			queryRes: func(t *testing.T) (any, error) {
   392  				tm := TableOf(&TestModel{}, "t1")
   393  				queryer := NewSelector[byte](db).Select(C("FirstName")).
   394  					From(tm).Where(C("Id").EQ(1))
   395  				return queryer.Get(context.Background())
   396  			},
   397  			mockOrder: func(mock sqlmock.Sqlmock) {
   398  				rows := mock.NewRows([]string{"first_name"}).AddRow('D')
   399  				mock.ExpectQuery("SELECT `first_name` FROM `test_model` AS `t1` WHERE `id`=? LIMIT ?;").
   400  					WithArgs(1, 1).
   401  					WillReturnRows(rows)
   402  			},
   403  			wantVal: func() *byte {
   404  				val := byte('D')
   405  				return &val
   406  			}(),
   407  		},
   408  		{
   409  			name: "res bytes",
   410  			queryRes: func(t *testing.T) (any, error) {
   411  				tm := TableOf(&TestModel{}, "t1")
   412  				queryer := NewSelector[[]byte](db).Select(C("FirstName")).
   413  					From(tm).Where(C("Id").EQ(1))
   414  				return queryer.Get(context.Background())
   415  			},
   416  			mockOrder: func(mock sqlmock.Sqlmock) {
   417  				rows := mock.NewRows([]string{"first_name"}).AddRow([]byte("Li"))
   418  				mock.ExpectQuery("SELECT `first_name` FROM `test_model` AS `t1` WHERE `id`=? LIMIT ?;").
   419  					WithArgs(1, 1).
   420  					WillReturnRows(rows)
   421  			},
   422  			wantVal: func() *[]byte {
   423  				val := []byte("Li")
   424  				return &val
   425  			}(),
   426  		},
   427  		{
   428  			name: "res string",
   429  			queryRes: func(t *testing.T) (any, error) {
   430  				tm := TableOf(&TestModel{}, "t1")
   431  				queryer := NewSelector[string](db).Select(C("FirstName")).
   432  					From(tm).Where(C("Id").EQ(1))
   433  				return queryer.Get(context.Background())
   434  			},
   435  			mockOrder: func(mock sqlmock.Sqlmock) {
   436  				rows := mock.NewRows([]string{"first_name"}).AddRow("Da")
   437  				mock.ExpectQuery("SELECT `first_name` FROM `test_model` AS `t1` WHERE `id`=? LIMIT ?;").
   438  					WithArgs(1, 1).
   439  					WillReturnRows(rows)
   440  			},
   441  			wantVal: func() *string {
   442  				val := "Da"
   443  				return &val
   444  			}(),
   445  		},
   446  		{
   447  			name: "res struct ptr",
   448  			queryRes: func(t *testing.T) (any, error) {
   449  				queryer := NewSelector[TestModel](db).Select(C("FirstName"), C("Age")).
   450  					Where(C("Id").EQ(1))
   451  				return queryer.Get(context.Background())
   452  			},
   453  			mockOrder: func(mock sqlmock.Sqlmock) {
   454  				rows := mock.NewRows([]string{"first_name", "age"}).AddRow("Da", 18)
   455  				mock.ExpectQuery("SELECT `first_name`,`age` FROM `test_model` WHERE `id`=? LIMIT ?;").
   456  					WithArgs(1, 1).
   457  					WillReturnRows(rows)
   458  			},
   459  			wantVal: func() *TestModel {
   460  				return &TestModel{
   461  					FirstName: "Da",
   462  					Age:       18,
   463  				}
   464  			}(),
   465  		},
   466  		{
   467  			name: "res sql.NullString",
   468  			queryRes: func(t *testing.T) (any, error) {
   469  				tm := TableOf(&TestModel{}, "t1")
   470  				queryer := NewSelector[sql.NullString](db).Select(C("LastName")).
   471  					From(tm).Where(C("Id").EQ(1))
   472  				return queryer.Get(context.Background())
   473  			},
   474  			mockOrder: func(mock sqlmock.Sqlmock) {
   475  				rows := mock.NewRows([]string{"last_name"}).AddRow([]byte("ming"))
   476  				mock.ExpectQuery("SELECT `last_name` FROM `test_model` AS `t1` WHERE `id`=? LIMIT ?;").
   477  					WithArgs(1, 1).
   478  					WillReturnRows(rows)
   479  			},
   480  			wantVal: func() *sql.NullString {
   481  				return &sql.NullString{String: "ming", Valid: true}
   482  			}(),
   483  		},
   484  		{
   485  			name: "res *int accept NULL",
   486  			queryRes: func(t *testing.T) (any, error) {
   487  				tm := TableOf(&TestModel{}, "t1")
   488  				queryer := NewSelector[*int](db).Select(C("Age")).
   489  					From(tm).Where(C("Id").EQ(1))
   490  				return queryer.Get(context.Background())
   491  			},
   492  			mockOrder: func(mock sqlmock.Sqlmock) {
   493  				rows := mock.NewRows([]string{"age"}).AddRow(nil)
   494  				mock.ExpectQuery("SELECT `age` FROM `test_model` AS `t1` WHERE `id`=? LIMIT ?;").
   495  					WithArgs(1, 1).
   496  					WillReturnRows(rows)
   497  			},
   498  			wantVal: func() **int {
   499  				return new(*int)
   500  			}(),
   501  		},
   502  		{
   503  			name: "res int accept NULL",
   504  			queryRes: func(t *testing.T) (any, error) {
   505  				tm := TableOf(&TestModel{}, "t1")
   506  				queryer := NewSelector[int](db).Select(C("Age")).
   507  					From(tm).Where(C("Id").EQ(1))
   508  				return queryer.Get(context.Background())
   509  			},
   510  			mockOrder: func(mock sqlmock.Sqlmock) {
   511  				rows := mock.NewRows([]string{"age"}).AddRow(nil)
   512  				mock.ExpectQuery("SELECT `age` FROM `test_model` AS `t1` WHERE `id`=? LIMIT ?;").
   513  					WithArgs(1, 1).
   514  					WillReturnRows(rows)
   515  			},
   516  			wantErr: "sql: Scan error on column index 0, name \"age\": converting NULL to int is unsupported",
   517  		},
   518  	}
   519  
   520  	for _, tc := range testCases {
   521  		t.Run(tc.name, func(t *testing.T) {
   522  			tc.mockOrder(mock)
   523  			res, err := tc.queryRes(t)
   524  			if err != nil {
   525  				assert.EqualError(t, err, tc.wantErr)
   526  				return
   527  			}
   528  			assert.Equal(t, tc.wantVal, res)
   529  		})
   530  	}
   531  }
   532  
   533  func TestSelector_GetMulti_baseType(t *testing.T) {
   534  	mockDB, mock, err := sqlmock.New(
   535  		sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))
   536  	if err != nil {
   537  		t.Fatal(err)
   538  	}
   539  	defer func() { _ = mockDB.Close() }()
   540  	db, err := OpenDS("mysql", single.NewDB(mockDB))
   541  	if err != nil {
   542  		t.Fatal(err)
   543  	}
   544  
   545  	testCases := []struct {
   546  		name      string
   547  		queryRes  func(t *testing.T) any
   548  		mockErr   error
   549  		mockOrder func(mock sqlmock.Sqlmock)
   550  		wantErr   error
   551  		wantVal   any
   552  	}{
   553  		{
   554  			name: "res int",
   555  			queryRes: func(t *testing.T) any {
   556  				queryer := NewSelector[int](db).Select(C("Age")).From(TableOf(&TestModel{}, "t1"))
   557  				result, err := queryer.GetMulti(context.Background())
   558  				require.NoError(t, err)
   559  				return result
   560  			},
   561  			mockOrder: func(mock sqlmock.Sqlmock) {
   562  				rows := mock.NewRows([]string{"age"}).AddRow(10).
   563  					AddRow(18).AddRow(22)
   564  				mock.ExpectQuery("SELECT `age` FROM `test_model` AS `t1`;").
   565  					WillReturnRows(rows)
   566  			},
   567  			wantVal: func() (res []*int) {
   568  				vals := []int{10, 18, 22}
   569  				for i := 0; i < len(vals); i++ {
   570  					res = append(res, &vals[i])
   571  				}
   572  				return
   573  			}(),
   574  		},
   575  		{
   576  			name: "res int32",
   577  			queryRes: func(t *testing.T) any {
   578  				queryer := NewSelector[int32](db).Select(C("Age")).From(TableOf(&TestModel{}, "t1"))
   579  				result, err := queryer.GetMulti(context.Background())
   580  				require.NoError(t, err)
   581  				return result
   582  			},
   583  			mockOrder: func(mock sqlmock.Sqlmock) {
   584  				rows := mock.NewRows([]string{"age"}).AddRow(10).
   585  					AddRow(18).AddRow(22)
   586  				mock.ExpectQuery("SELECT `age` FROM `test_model` AS `t1`;").
   587  					WillReturnRows(rows)
   588  			},
   589  			wantVal: func() (res []*int32) {
   590  				vals := []int32{10, 18, 22}
   591  				for i := 0; i < len(vals); i++ {
   592  					res = append(res, &vals[i])
   593  				}
   594  				return
   595  			}(),
   596  		},
   597  		{
   598  			name: "avg res int64",
   599  			queryRes: func(t *testing.T) any {
   600  				queryer := NewSelector[int64](db).Select(C("Age")).From(TableOf(&TestModel{}, "t1"))
   601  				result, err := queryer.GetMulti(context.Background())
   602  				require.NoError(t, err)
   603  				return result
   604  			},
   605  			mockOrder: func(mock sqlmock.Sqlmock) {
   606  				rows := mock.NewRows([]string{"age"}).AddRow(10).
   607  					AddRow(18).AddRow(22)
   608  				mock.ExpectQuery("SELECT `age` FROM `test_model` AS `t1`;").
   609  					WillReturnRows(rows)
   610  			},
   611  			wantVal: func() (res []*int64) {
   612  				vals := []int64{10, 18, 22}
   613  				for i := 0; i < len(vals); i++ {
   614  					res = append(res, &vals[i])
   615  				}
   616  				return
   617  			}(),
   618  		},
   619  		{
   620  			name: "avg res float32",
   621  			queryRes: func(t *testing.T) any {
   622  				queryer := NewSelector[float32](db).Select(C("Age")).From(TableOf(&TestModel{}, "t1"))
   623  				result, err := queryer.GetMulti(context.Background())
   624  				require.NoError(t, err)
   625  				return result
   626  			},
   627  			mockOrder: func(mock sqlmock.Sqlmock) {
   628  				rows := mock.NewRows([]string{"age"}).AddRow(10.2).AddRow(18.8)
   629  				mock.ExpectQuery("SELECT `age` FROM `test_model` AS `t1`;").
   630  					WillReturnRows(rows)
   631  			},
   632  			wantVal: func() (res []*float32) {
   633  				vals := []float32{10.2, 18.8}
   634  				for i := 0; i < len(vals); i++ {
   635  					res = append(res, &vals[i])
   636  				}
   637  				return
   638  			}(),
   639  		},
   640  		{
   641  			name: "avg res float64",
   642  			queryRes: func(t *testing.T) any {
   643  				queryer := NewSelector[float64](db).Select(C("Age")).From(TableOf(&TestModel{}, "t1"))
   644  				result, err := queryer.GetMulti(context.Background())
   645  				require.NoError(t, err)
   646  				return result
   647  			},
   648  			mockOrder: func(mock sqlmock.Sqlmock) {
   649  				rows := mock.NewRows([]string{"age"}).AddRow(10.2).AddRow(18.8)
   650  				mock.ExpectQuery("SELECT `age` FROM `test_model` AS `t1`;").
   651  					WillReturnRows(rows)
   652  			},
   653  			wantVal: func() (res []*float64) {
   654  				vals := []float64{10.2, 18.8}
   655  				for i := 0; i < len(vals); i++ {
   656  					res = append(res, &vals[i])
   657  				}
   658  				return
   659  			}(),
   660  		},
   661  		{
   662  			name: "res byte",
   663  			queryRes: func(t *testing.T) any {
   664  				queryer := NewSelector[byte](db).Select(C("FirstName")).From(TableOf(&TestModel{}, "t1"))
   665  				result, err := queryer.GetMulti(context.Background())
   666  				require.NoError(t, err)
   667  				return result
   668  			},
   669  			mockOrder: func(mock sqlmock.Sqlmock) {
   670  				rows := mock.NewRows([]string{"first_name"}).AddRow('D').AddRow('a')
   671  				mock.ExpectQuery("SELECT `first_name` FROM `test_model` AS `t1`;").
   672  					WillReturnRows(rows)
   673  			},
   674  			wantVal: func() (res []*byte) {
   675  				vals := []byte{'D', 'a'}
   676  				for i := 0; i < len(vals); i++ {
   677  					res = append(res, &vals[i])
   678  				}
   679  				return
   680  			}(),
   681  		},
   682  		{
   683  			name: "res bytes",
   684  			queryRes: func(t *testing.T) any {
   685  				queryer := NewSelector[[]byte](db).Select(C("FirstName")).From(TableOf(&TestModel{}, "t1"))
   686  				result, err := queryer.GetMulti(context.Background())
   687  				require.NoError(t, err)
   688  				return result
   689  			},
   690  			mockOrder: func(mock sqlmock.Sqlmock) {
   691  				rows := mock.NewRows([]string{"first_name"}).AddRow([]byte("Li")).AddRow([]byte("Liu"))
   692  				mock.ExpectQuery("SELECT `first_name` FROM `test_model` AS `t1`;").
   693  					WillReturnRows(rows)
   694  			},
   695  			wantVal: func() (res []*[]byte) {
   696  				vals := [][]byte{[]byte("Li"), []byte("Liu")}
   697  				for i := 0; i < len(vals); i++ {
   698  					res = append(res, &vals[i])
   699  				}
   700  				return
   701  			}(),
   702  		},
   703  		{
   704  			name: "res string",
   705  			queryRes: func(t *testing.T) any {
   706  				queryer := NewSelector[string](db).Select(C("FirstName")).From(TableOf(&TestModel{}, "t1"))
   707  				result, err := queryer.GetMulti(context.Background())
   708  				require.NoError(t, err)
   709  				return result
   710  			},
   711  			mockOrder: func(mock sqlmock.Sqlmock) {
   712  				rows := mock.NewRows([]string{"first_name"}).AddRow("Da").AddRow("Li")
   713  				mock.ExpectQuery("SELECT `first_name` FROM `test_model` AS `t1`;").
   714  					WillReturnRows(rows)
   715  			},
   716  			wantVal: func() (res []*string) {
   717  				vals := []string{"Da", "Li"}
   718  				for i := 0; i < len(vals); i++ {
   719  					res = append(res, &vals[i])
   720  				}
   721  				return
   722  			}(),
   723  		},
   724  		{
   725  			name: "res struct ptr",
   726  			queryRes: func(t *testing.T) any {
   727  				queryer := NewSelector[TestModel](db).Select(C("FirstName"), C("Age")).From(TableOf(&TestModel{}, "t1"))
   728  				result, err := queryer.GetMulti(context.Background())
   729  				require.NoError(t, err)
   730  				return result
   731  			},
   732  			mockOrder: func(mock sqlmock.Sqlmock) {
   733  				rows := mock.NewRows([]string{"first_name", "age"}).
   734  					AddRow("Da", 18).AddRow("Xiao", 16)
   735  				mock.ExpectQuery("SELECT `first_name`,`age` FROM `test_model` AS `t1`;").
   736  					WillReturnRows(rows)
   737  			},
   738  			wantVal: []*TestModel{
   739  				{
   740  					FirstName: "Da",
   741  					Age:       18,
   742  				},
   743  				{
   744  					FirstName: "Xiao",
   745  					Age:       16,
   746  				},
   747  			},
   748  		},
   749  		{
   750  			name: "res sql.NullString",
   751  			queryRes: func(t *testing.T) any {
   752  				queryer := NewSelector[sql.NullString](db).Select(C("LastName")).From(TableOf(&TestModel{}, "t1"))
   753  				result, err := queryer.GetMulti(context.Background())
   754  				require.NoError(t, err)
   755  				return result
   756  			},
   757  			mockOrder: func(mock sqlmock.Sqlmock) {
   758  				rows := mock.NewRows([]string{"last_name"}).
   759  					AddRow([]byte("ming")).AddRow([]byte("gang"))
   760  				mock.ExpectQuery("SELECT `last_name` FROM `test_model` AS `t1`;").
   761  					WillReturnRows(rows)
   762  			},
   763  			wantVal: []*sql.NullString{
   764  				{
   765  					String: "ming",
   766  					Valid:  true,
   767  				},
   768  				{
   769  					String: "gang",
   770  					Valid:  true,
   771  				},
   772  			},
   773  		},
   774  	}
   775  
   776  	for _, tc := range testCases {
   777  		t.Run(tc.name, func(t *testing.T) {
   778  			tc.mockOrder(mock)
   779  			res := tc.queryRes(t)
   780  			assert.EqualValues(t, tc.wantVal, res)
   781  		})
   782  	}
   783  }
   784  
   785  func TestSelectable(t *testing.T) {
   786  	db := memoryDB()
   787  	testCases := []CommonTestCase{
   788  		{
   789  			name:    "simple",
   790  			builder: NewSelector[TestModel](db),
   791  			wantSql: "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model`;",
   792  		},
   793  		{
   794  			name:    "columns",
   795  			builder: NewSelector[TestModel](db).Select(Columns("Id", "FirstName")),
   796  			wantSql: "SELECT `id`,`first_name` FROM `test_model`;",
   797  		},
   798  		{
   799  			name:    "alias",
   800  			builder: NewSelector[TestModel](db).Select(Columns("Id"), C("FirstName").As("name")),
   801  			wantSql: "SELECT `id`,`first_name` AS `name` FROM `test_model`;",
   802  		},
   803  		{
   804  			name:    "aggregate",
   805  			builder: NewSelector[TestModel](db).Select(Columns("Id"), Avg("Age").As("avg_age")),
   806  			wantSql: "SELECT `id`,AVG(`age`) AS `avg_age` FROM `test_model`;",
   807  		},
   808  		{
   809  			name:    "raw",
   810  			builder: NewSelector[TestModel](db).Select(Columns("Id"), Raw("AVG(DISTINCT `age`)")),
   811  			wantSql: "SELECT `id`,AVG(DISTINCT `age`) FROM `test_model`;",
   812  		},
   813  		{
   814  			name:    "invalid columns",
   815  			builder: NewSelector[TestModel](db).Select(Columns("Invalid"), Raw("AVG(DISTINCT `age`)")),
   816  			wantErr: errs.NewInvalidFieldError("Invalid"),
   817  		},
   818  		{
   819  			name:    "order by",
   820  			builder: NewSelector[TestModel](db).OrderBy(ASC("Age"), DESC("Id")),
   821  			wantSql: "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` ORDER BY `age` ASC,`id` DESC;",
   822  		},
   823  		{
   824  			name:    "order by invalid column",
   825  			builder: NewSelector[TestModel](db).OrderBy(ASC("Invalid"), DESC("Id")),
   826  			wantErr: errs.NewInvalidFieldError("Invalid"),
   827  		},
   828  		{
   829  			name:    "group by",
   830  			builder: NewSelector[TestModel](db).GroupBy("Age", "Id"),
   831  			wantSql: "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` GROUP BY `age`,`id`;",
   832  		},
   833  		{
   834  			name:    "group by invalid column",
   835  			builder: NewSelector[TestModel](db).GroupBy("Invalid", "Id"),
   836  			wantErr: errs.NewInvalidFieldError("Invalid"),
   837  		},
   838  		{
   839  			name:     "offset",
   840  			builder:  NewSelector[TestModel](db).OrderBy(ASC("Age"), DESC("Id")).Offset(10),
   841  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` ORDER BY `age` ASC,`id` DESC OFFSET ?;",
   842  			wantArgs: []interface{}{10},
   843  		},
   844  		{
   845  			name:     "limit",
   846  			builder:  NewSelector[TestModel](db).OrderBy(ASC("Age"), DESC("Id")).Offset(10).Limit(100),
   847  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` ORDER BY `age` ASC,`id` DESC OFFSET ? LIMIT ?;",
   848  			wantArgs: []interface{}{10, 100},
   849  		},
   850  		{
   851  			name:     "where",
   852  			builder:  NewSelector[TestModel](db).Where(C("Id").EQ(10)),
   853  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` WHERE `id`=?;",
   854  			wantArgs: []interface{}{10},
   855  		},
   856  		{
   857  			name:    "no where",
   858  			builder: NewSelector[TestModel](db).Where(),
   859  			wantSql: "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model`;",
   860  		},
   861  		{
   862  			name:     "having",
   863  			builder:  NewSelector[TestModel](db).GroupBy("FirstName").Having(Avg("Age").EQ(18)),
   864  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` GROUP BY `first_name` HAVING AVG(`age`)=?;",
   865  			wantArgs: []interface{}{18},
   866  		},
   867  		{
   868  			name:    "no having",
   869  			builder: NewSelector[TestModel](db).GroupBy("FirstName").Having(),
   870  			wantSql: "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` GROUP BY `first_name`;",
   871  		},
   872  		{
   873  			name:     "alias in having",
   874  			builder:  NewSelector[TestModel](db).Select(Columns("Id"), Columns("FirstName"), Avg("Age").As("avg_age")).GroupBy("FirstName").Having(Avg("Age").LT(20)),
   875  			wantSql:  "SELECT `id`,`first_name`,AVG(`age`) AS `avg_age` FROM `test_model` GROUP BY `first_name` HAVING AVG(`age`)<?;",
   876  			wantArgs: []interface{}{20},
   877  		},
   878  		{
   879  			name:    "invalid alias in having",
   880  			builder: NewSelector[TestModel](db).Select(Columns("Id"), Columns("FirstName"), Avg("Age").As("avg_age")).GroupBy("FirstName").Having(C("Invalid").LT(20)),
   881  			wantErr: errs.NewInvalidFieldError("Invalid"),
   882  		},
   883  		{
   884  			name:     "in",
   885  			builder:  NewSelector[TestModel](db).Select(Columns("Id")).Where(C("Id").In(1, 2, 3)),
   886  			wantSql:  "SELECT `id` FROM `test_model` WHERE `id` IN (?,?,?);",
   887  			wantArgs: []interface{}{1, 2, 3},
   888  		},
   889  		{
   890  			name:     "not in",
   891  			builder:  NewSelector[TestModel](db).Select(Columns("Id")).Where(C("Id").NotIn(1, 2, 3)),
   892  			wantSql:  "SELECT `id` FROM `test_model` WHERE `id` NOT IN (?,?,?);",
   893  			wantArgs: []interface{}{1, 2, 3},
   894  		},
   895  		{
   896  			// 传入的参数为切片
   897  			name:     "slice in",
   898  			builder:  NewSelector[TestModel](db).Select(Columns("Id")).Where(C("Id").In([]int{1, 2, 3})),
   899  			wantSql:  "SELECT `id` FROM `test_model` WHERE `id` IN (?);",
   900  			wantArgs: []interface{}{[]int{1, 2, 3}},
   901  		},
   902  		{
   903  			// in 后面没有值
   904  			name:    "no in",
   905  			builder: NewSelector[TestModel](db).Select(Columns("Id")).Where(C("Id").In()),
   906  			wantSql: "SELECT `id` FROM `test_model` WHERE FALSE;",
   907  		},
   908  		{
   909  			// Notin 后面没有值
   910  			name:    "no in",
   911  			builder: NewSelector[TestModel](db).Select(Columns("Id")).Where(C("Id").NotIn()),
   912  			wantSql: "SELECT `id` FROM `test_model` WHERE FALSE;",
   913  		},
   914  		{
   915  			name:    "in empty slice",
   916  			builder: NewSelector[TestModel](db).Select(Columns("Id")).Where(C("Id").In([]any{}...)),
   917  			wantSql: "SELECT `id` FROM `test_model` WHERE FALSE;",
   918  		},
   919  		{
   920  			name:    "NOT In empty slice",
   921  			builder: NewSelector[TestModel](db).Select(Columns("Id")).Where(C("Id").NotIn([]any{}...)),
   922  			wantSql: "SELECT `id` FROM `test_model` WHERE FALSE;",
   923  		},
   924  		// 模糊查询
   925  		{
   926  			name:    "NOT In empty slice",
   927  			builder: NewSelector[TestModel](db).Select(Columns("Id")).Where(C("Id").NotIn([]any{}...)),
   928  			wantSql: "SELECT `id` FROM `test_model` WHERE FALSE;",
   929  		},
   930  		{
   931  			name:     "where not like %",
   932  			builder:  NewSelector[TestModel](db).Where(C("FirstName").NotLike("%ming")),
   933  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` WHERE `first_name` NOT LIKE ?;",
   934  			wantArgs: []interface{}{"%ming"},
   935  		},
   936  		{
   937  			name:     "where like %",
   938  			builder:  NewSelector[TestModel](db).Where(C("FirstName").Like("zhang%")),
   939  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` WHERE `first_name` LIKE ?;",
   940  			wantArgs: []interface{}{"zhang%"},
   941  		},
   942  		{
   943  			name:     "where not like _",
   944  			builder:  NewSelector[TestModel](db).Where(C("FirstName").NotLike("_三_")),
   945  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` WHERE `first_name` NOT LIKE ?;",
   946  			wantArgs: []interface{}{"_三_"},
   947  		},
   948  		{
   949  			name:     "where like _",
   950  			builder:  NewSelector[TestModel](db).Where(C("FirstName").Like("_三_")),
   951  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` WHERE `first_name` LIKE ?;",
   952  			wantArgs: []interface{}{"_三_"},
   953  		},
   954  		{
   955  			name:     "where not like []",
   956  			builder:  NewSelector[TestModel](db).Where(C("FirstName").NotLike("老[1-9]")),
   957  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` WHERE `first_name` NOT LIKE ?;",
   958  			wantArgs: []interface{}{"老[1-9]"},
   959  		},
   960  		{
   961  			name:     "where like []",
   962  			builder:  NewSelector[TestModel](db).Where(C("FirstName").Like("老[1-9]")),
   963  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` WHERE `first_name` LIKE ?;",
   964  			wantArgs: []interface{}{"老[1-9]"},
   965  		},
   966  		{
   967  			name:     "where not like [^ ]",
   968  			builder:  NewSelector[TestModel](db).Where(C("FirstName").NotLike("老[^1-4]")),
   969  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` WHERE `first_name` NOT LIKE ?;",
   970  			wantArgs: []interface{}{"老[^1-4]"},
   971  		},
   972  		{
   973  			name:     "where like [^ ]",
   974  			builder:  NewSelector[TestModel](db).Where(C("FirstName").Like("老[^1-4]")),
   975  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` WHERE `first_name` LIKE ?;",
   976  			wantArgs: []interface{}{"老[^1-4]"},
   977  		},
   978  
   979  		{
   980  			name:     "where not like int",
   981  			builder:  NewSelector[TestModel](db).Where(C("Age").NotLike(18)),
   982  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` WHERE `age` NOT LIKE ?;",
   983  			wantArgs: []interface{}{18},
   984  		},
   985  		{
   986  			name:     "where like int",
   987  			builder:  NewSelector[TestModel](db).Where(C("Age").Like(22)),
   988  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` WHERE `age` LIKE ?;",
   989  			wantArgs: []interface{}{22},
   990  		},
   991  		{
   992  			name:     "having like %",
   993  			builder:  NewSelector[TestModel](db).GroupBy("FirstName").Having(C("LastName").Like("%li")),
   994  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` GROUP BY `first_name` HAVING `last_name` LIKE ?;",
   995  			wantArgs: []interface{}{"%li"},
   996  		},
   997  		{
   998  			name:     "having no like %",
   999  			builder:  NewSelector[TestModel](db).GroupBy("FirstName").Having(C("LastName").NotLike("%yy%")),
  1000  			wantSql:  "SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` GROUP BY `first_name` HAVING `last_name` NOT LIKE ?;",
  1001  			wantArgs: []interface{}{"%yy%"},
  1002  		},
  1003  		{
  1004  			name:    "distinct single row",
  1005  			builder: NewSelector[TestModel](db).Distinct().Select(C("FirstName")),
  1006  			wantSql: "SELECT DISTINCT `first_name` FROM `test_model`;",
  1007  		},
  1008  		{
  1009  			name:    "count distinct",
  1010  			builder: NewSelector[TestModel](db).Select(CountDistinct("FirstName")),
  1011  			wantSql: "SELECT COUNT(DISTINCT `first_name`) FROM `test_model`;",
  1012  		},
  1013  		{
  1014  			name:     "having count distinct",
  1015  			builder:  NewSelector[TestModel](db).Select(C("FirstName")).GroupBy("FirstName").Having(CountDistinct("FirstName").EQ("jack")),
  1016  			wantSql:  "SELECT `first_name` FROM `test_model` GROUP BY `first_name` HAVING COUNT(DISTINCT `first_name`)=?;",
  1017  			wantArgs: []interface{}{"jack"},
  1018  		},
  1019  	}
  1020  
  1021  	for _, tc := range testCases {
  1022  		c := tc
  1023  		t.Run(c.name, func(t *testing.T) {
  1024  			query, err := c.builder.Build()
  1025  			assert.Equal(t, c.wantErr, err)
  1026  			if err != nil {
  1027  				return
  1028  			}
  1029  			assert.Equal(t, c.wantSql, query.SQL)
  1030  			assert.Equal(t, c.wantArgs, query.Args)
  1031  		})
  1032  	}
  1033  }
  1034  
  1035  func TestSelectableCombination(t *testing.T) {
  1036  	db, err := Open("sqlite3", "file:test.db?cache=shared&mode=memory")
  1037  	if err != nil {
  1038  		t.Error(err)
  1039  	}
  1040  	testCases := []CommonTestCase{
  1041  		{
  1042  			name:    "simple",
  1043  			builder: NewSelector[TestCombinedModel](db),
  1044  			wantSql: "SELECT `create_time`,`update_time`,`id`,`first_name`,`age`,`last_name` FROM `test_combined_model`;",
  1045  		},
  1046  		{
  1047  			name:    "columns",
  1048  			builder: NewSelector[TestCombinedModel](db).Select(Columns("Id", "FirstName", "CreateTime")),
  1049  			wantSql: "SELECT `id`,`first_name`,`create_time` FROM `test_combined_model`;",
  1050  		},
  1051  		{
  1052  			name:    "alias",
  1053  			builder: NewSelector[TestCombinedModel](db).Select(Columns("Id"), C("CreateTime").As("creation")),
  1054  			wantSql: "SELECT `id`,`create_time` AS `creation` FROM `test_combined_model`;",
  1055  		},
  1056  		{
  1057  			name:    "aggregate",
  1058  			builder: NewSelector[TestCombinedModel](db).Select(Columns("Id"), Max("CreateTime").As("max_time")),
  1059  			wantSql: "SELECT `id`,MAX(`create_time`) AS `max_time` FROM `test_combined_model`;",
  1060  		},
  1061  		{
  1062  			name:    "raw",
  1063  			builder: NewSelector[TestCombinedModel](db).Select(Columns("Id"), Raw("AVG(DISTINCT `create_time`)")),
  1064  			wantSql: "SELECT `id`,AVG(DISTINCT `create_time`) FROM `test_combined_model`;",
  1065  		},
  1066  		{
  1067  			name:    "invalid columns",
  1068  			builder: NewSelector[TestCombinedModel](db).Select(Columns("Invalid"), Raw("AVG(DISTINCT `age`)")),
  1069  			wantErr: errs.NewInvalidFieldError("Invalid"),
  1070  		},
  1071  		{
  1072  			name:    "order by",
  1073  			builder: NewSelector[TestCombinedModel](db).OrderBy(ASC("Age"), DESC("CreateTime")),
  1074  			wantSql: "SELECT `create_time`,`update_time`,`id`,`first_name`,`age`,`last_name` FROM `test_combined_model` ORDER BY `age` ASC,`create_time` DESC;",
  1075  		},
  1076  		{
  1077  			name:    "order by invalid column",
  1078  			builder: NewSelector[TestCombinedModel](db).OrderBy(ASC("Invalid"), DESC("Id")),
  1079  			wantErr: errs.NewInvalidFieldError("Invalid"),
  1080  		},
  1081  		{
  1082  			name:    "group by",
  1083  			builder: NewSelector[TestCombinedModel](db).GroupBy("CreateTime", "Id"),
  1084  			wantSql: "SELECT `create_time`,`update_time`,`id`,`first_name`,`age`,`last_name` FROM `test_combined_model` GROUP BY `create_time`,`id`;",
  1085  		},
  1086  		{
  1087  			name:    "group by invalid column",
  1088  			builder: NewSelector[TestCombinedModel](db).GroupBy("Invalid", "Id"),
  1089  			wantErr: errs.NewInvalidFieldError("Invalid"),
  1090  		},
  1091  		{
  1092  			name:     "offset",
  1093  			builder:  NewSelector[TestCombinedModel](db).OrderBy(ASC("Age"), DESC("CreateTime")).Offset(10),
  1094  			wantSql:  "SELECT `create_time`,`update_time`,`id`,`first_name`,`age`,`last_name` FROM `test_combined_model` ORDER BY `age` ASC,`create_time` DESC OFFSET ?;",
  1095  			wantArgs: []interface{}{10},
  1096  		},
  1097  		{
  1098  			name:     "limit",
  1099  			builder:  NewSelector[TestCombinedModel](db).OrderBy(ASC("Age"), DESC("CreateTime")).Offset(10).Limit(100),
  1100  			wantSql:  "SELECT `create_time`,`update_time`,`id`,`first_name`,`age`,`last_name` FROM `test_combined_model` ORDER BY `age` ASC,`create_time` DESC OFFSET ? LIMIT ?;",
  1101  			wantArgs: []interface{}{10, 100},
  1102  		},
  1103  		{
  1104  			name:     "where",
  1105  			builder:  NewSelector[TestCombinedModel](db).Where(C("Id").EQ(10).And(C("CreateTime").EQ(10))),
  1106  			wantSql:  "SELECT `create_time`,`update_time`,`id`,`first_name`,`age`,`last_name` FROM `test_combined_model` WHERE (`id`=?) AND (`create_time`=?);",
  1107  			wantArgs: []interface{}{10, 10},
  1108  		},
  1109  		{
  1110  			name:    "no where",
  1111  			builder: NewSelector[TestCombinedModel](db).Where(),
  1112  			wantSql: "SELECT `create_time`,`update_time`,`id`,`first_name`,`age`,`last_name` FROM `test_combined_model`;",
  1113  		},
  1114  		{
  1115  			name:     "having",
  1116  			builder:  NewSelector[TestCombinedModel](db).GroupBy("FirstName").Having(Max("CreateTime").EQ(18)),
  1117  			wantSql:  "SELECT `create_time`,`update_time`,`id`,`first_name`,`age`,`last_name` FROM `test_combined_model` GROUP BY `first_name` HAVING MAX(`create_time`)=?;",
  1118  			wantArgs: []interface{}{18},
  1119  		},
  1120  		{
  1121  			name:    "no having",
  1122  			builder: NewSelector[TestCombinedModel](db).GroupBy("CreateTime").Having(),
  1123  			wantSql: "SELECT `create_time`,`update_time`,`id`,`first_name`,`age`,`last_name` FROM `test_combined_model` GROUP BY `create_time`;",
  1124  		},
  1125  		{
  1126  			name:     "alias in having",
  1127  			builder:  NewSelector[TestCombinedModel](db).Select(Columns("Id"), Columns("FirstName"), Avg("CreateTime").As("create")).GroupBy("FirstName").Having(Avg("CreateTime").LT(20)),
  1128  			wantSql:  "SELECT `id`,`first_name`,AVG(`create_time`) AS `create` FROM `test_combined_model` GROUP BY `first_name` HAVING AVG(`create_time`)<?;",
  1129  			wantArgs: []interface{}{20},
  1130  		},
  1131  		{
  1132  			name:    "invalid alias in having",
  1133  			builder: NewSelector[TestCombinedModel](db).Select(Columns("Id"), Columns("FirstName"), Avg("Age").As("avg_age")).GroupBy("FirstName").Having(C("Invalid").LT(20)),
  1134  			wantErr: errs.NewInvalidFieldError("Invalid"),
  1135  		},
  1136  	}
  1137  
  1138  	for _, tc := range testCases {
  1139  		c := tc
  1140  		t.Run(c.name, func(t *testing.T) {
  1141  			query, err := c.builder.Build()
  1142  			assert.Equal(t, c.wantErr, err)
  1143  			if err != nil {
  1144  				return
  1145  			}
  1146  			assert.Equal(t, c.wantSql, query.SQL)
  1147  			assert.Equal(t, c.wantArgs, query.Args)
  1148  		})
  1149  	}
  1150  }
  1151  
  1152  type BaseEntity struct {
  1153  	CreateTime uint64
  1154  	UpdateTime uint64
  1155  }
  1156  
  1157  type TestCombinedModel struct {
  1158  	BaseEntity
  1159  	Id        int64 `eorm:"primary_key"`
  1160  	FirstName string
  1161  	Age       int8
  1162  	LastName  *string
  1163  }
  1164  
  1165  func ExampleSelector_OrderBy() {
  1166  	db, _ := Open("sqlite3", "file:test.db?cache=shared&mode=memory")
  1167  	query, _ := NewSelector[TestModel](db).OrderBy(ASC("Age")).Build()
  1168  	fmt.Printf("case1\n%s", query.String())
  1169  	query, _ = NewSelector[TestModel](db).OrderBy(ASC("Age", "Id")).Build()
  1170  	fmt.Printf("case2\n%s", query.String())
  1171  	query, _ = NewSelector[TestModel](db).OrderBy(ASC("Age"), ASC("Id")).Build()
  1172  	fmt.Printf("case3\n%s", query.String())
  1173  	query, _ = NewSelector[TestModel](db).OrderBy(ASC("Age"), DESC("Id")).Build()
  1174  	fmt.Printf("case4\n%s", query.String())
  1175  	// Output:
  1176  	// case1
  1177  	// SQL: SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` ORDER BY `age` ASC;
  1178  	// Args: []interface {}(nil)
  1179  	// case2
  1180  	// SQL: SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` ORDER BY `age``id` ASC;
  1181  	// Args: []interface {}(nil)
  1182  	// case3
  1183  	// SQL: SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` ORDER BY `age` ASC,`id` ASC;
  1184  	// Args: []interface {}(nil)
  1185  	// case4
  1186  	// SQL: SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` ORDER BY `age` ASC,`id` DESC;
  1187  	// Args: []interface {}(nil)
  1188  }
  1189  
  1190  func ExampleSelector_Having() {
  1191  	db := memoryDB()
  1192  	query, _ := NewSelector[TestModel](db).Select(Columns("Id"), Columns("FirstName"), Avg("Age").As("avg_age")).GroupBy("FirstName").Having(Avg("Age").LT(20)).Build()
  1193  	fmt.Printf("case1\n%s", query.String())
  1194  	query, err := NewSelector[TestModel](db).Select(Columns("Id"), Columns("FirstName"), Avg("Age").As("avg_age")).GroupBy("FirstName").Having(C("Invalid").LT(20)).Build()
  1195  	fmt.Printf("case2\n%s", err)
  1196  	// Output:
  1197  	// case1
  1198  	// SQL: SELECT `id`,`first_name`,AVG(`age`) AS `avg_age` FROM `test_model` GROUP BY `first_name` HAVING AVG(`age`)<?;
  1199  	// Args: []interface {}{20}
  1200  	// case2
  1201  	// eorm: 未知字段 Invalid
  1202  }
  1203  
  1204  func ExampleSelector_Select() {
  1205  	db := memoryDB()
  1206  	tm := TableOf(&TestModel{}, "t1")
  1207  	cases := []*Selector[TestModel]{
  1208  		// case0: all columns are included
  1209  		NewSelector[TestModel](db).From(tm),
  1210  		// case1: only query specific columns
  1211  		NewSelector[TestModel](db).Select(Columns("Id", "Age")).From(tm),
  1212  		// case2: using alias
  1213  		NewSelector[TestModel](db).Select(C("Id").As("my_id")).From(tm),
  1214  		// case3: using aggregation function and alias
  1215  		NewSelector[TestModel](db).Select(Avg("Age").As("avg_age")).From(tm),
  1216  		// case4: using raw expression
  1217  		NewSelector[TestModel](db).Select(Raw("COUNT(DISTINCT `age`) AS `age_cnt`")).From(tm),
  1218  	}
  1219  
  1220  	for index, tc := range cases {
  1221  		query, _ := tc.Build()
  1222  		fmt.Printf("case%d:\n%s", index, query.String())
  1223  	}
  1224  	// Output:
  1225  	// case0:
  1226  	// SQL: SELECT `id`,`first_name`,`age`,`last_name` FROM `test_model` AS `t1`;
  1227  	// Args: []interface {}(nil)
  1228  	// case1:
  1229  	// SQL: SELECT `id`,`age` FROM `test_model` AS `t1`;
  1230  	// Args: []interface {}(nil)
  1231  	// case2:
  1232  	// SQL: SELECT `id` AS `my_id` FROM `test_model` AS `t1`;
  1233  	// Args: []interface {}(nil)
  1234  	// case3:
  1235  	// SQL: SELECT AVG(`age`) AS `avg_age` FROM `test_model` AS `t1`;
  1236  	// Args: []interface {}(nil)
  1237  	// case4:
  1238  	// SQL: SELECT COUNT(DISTINCT `age`) AS `age_cnt` FROM `test_model` AS `t1`;
  1239  	// Args: []interface {}(nil)
  1240  }
  1241  
  1242  func ExampleSelector_Distinct() {
  1243  	db := memoryDB()
  1244  	cases := []*Selector[TestModel]{
  1245  		// case0: disinct column
  1246  		NewSelector[TestModel](db).Distinct().Select(C("FirstName")),
  1247  		// case1: aggregation function using distinct
  1248  		NewSelector[TestModel](db).Select(CountDistinct("FirstName")),
  1249  		// case2: having using distinct
  1250  		NewSelector[TestModel](db).Select(C("FirstName")).GroupBy("FirstName").Having(CountDistinct("FirstName").EQ("jack")),
  1251  	}
  1252  
  1253  	for index, tc := range cases {
  1254  		query, _ := tc.Build()
  1255  		fmt.Printf("case%d:\n%s", index, query.String())
  1256  	}
  1257  	// Output:
  1258  	// case0:
  1259  	// SQL: SELECT DISTINCT `first_name` FROM `test_model`;
  1260  	// Args: []interface {}(nil)
  1261  	// case1:
  1262  	// SQL: SELECT COUNT(DISTINCT `first_name`) FROM `test_model`;
  1263  	// Args: []interface {}(nil)
  1264  	// case2:
  1265  	// SQL: SELECT `first_name` FROM `test_model` GROUP BY `first_name` HAVING COUNT(DISTINCT `first_name`)=?;
  1266  	// Args: []interface {}{"jack"}
  1267  }
  1268  
  1269  func TestSelector_Join(t *testing.T) {
  1270  	db := memoryDB()
  1271  	type Order struct {
  1272  		Id        int
  1273  		UsingCol1 string
  1274  		UsingCol2 string
  1275  	}
  1276  
  1277  	type OrderDetail struct {
  1278  		OrderId   int
  1279  		ItemId    int
  1280  		UsingCol1 string
  1281  		UsingCol2 string
  1282  	}
  1283  
  1284  	type Item struct {
  1285  		Id int
  1286  	}
  1287  
  1288  	testCases := []struct {
  1289  		name      string
  1290  		s         QueryBuilder
  1291  		wantQuery Query
  1292  		wantErr   error
  1293  	}{
  1294  		{
  1295  			name: "specify table",
  1296  			s:    NewSelector[Order](db).From(TableOf(&OrderDetail{}, "t1")),
  1297  			wantQuery: Query{
  1298  				SQL: "SELECT `order_id`,`item_id`,`using_col1`,`using_col2` FROM `order_detail` AS `t1`;",
  1299  			},
  1300  		},
  1301  		{
  1302  			name: "specify table with empty alias",
  1303  			s:    NewSelector[Order](db).From(TableOf(&OrderDetail{}, "")),
  1304  			wantQuery: Query{
  1305  				SQL: "SELECT `order_id`,`item_id`,`using_col1`,`using_col2` FROM `order_detail`;",
  1306  			},
  1307  		},
  1308  		{
  1309  			name: "only NewSelector",
  1310  			s:    NewSelector[Order](db),
  1311  			wantQuery: Query{
  1312  				SQL: "SELECT `id`,`using_col1`,`using_col2` FROM `order`;",
  1313  			},
  1314  		},
  1315  		{
  1316  			name: "join-using",
  1317  			s: func() QueryBuilder {
  1318  				t1 := TableOf(&Order{}, "t1")
  1319  				t2 := TableOf(&OrderDetail{}, "t2")
  1320  				t3 := t1.Join(t2).Using("UsingCol1", "UsingCol2")
  1321  				return NewSelector[Order](db).Select(Raw("*")).From(t3)
  1322  			}(),
  1323  			wantQuery: Query{
  1324  				SQL: "SELECT * FROM (`order` AS `t1` JOIN `order_detail` AS `t2` USING (`using_col1`,`using_col2`));",
  1325  			},
  1326  		},
  1327  		{
  1328  			name: "join-using-cols",
  1329  			s: func() QueryBuilder {
  1330  				t1 := TableOf(&Order{}, "t1")
  1331  				t2 := TableOf(&OrderDetail{}, "t2")
  1332  				t3 := t1.Join(t2).Using("UsingCol1", "UsingCol2")
  1333  				return NewSelector[Order](db).From(t3).Select(t1.C("UsingCol1"), t2.C("UsingCol1"))
  1334  			}(),
  1335  			wantQuery: Query{
  1336  				SQL: "SELECT `t1`.`using_col1`,`t2`.`using_col1` FROM (`order` AS `t1` JOIN `order_detail` AS `t2` USING (`using_col1`,`using_col2`));",
  1337  			},
  1338  		},
  1339  		{
  1340  			name: "join-using-cols-invalid",
  1341  			s: func() QueryBuilder {
  1342  				t1 := TableOf(&Order{}, "t1")
  1343  				t2 := TableOf(&OrderDetail{}, "t2")
  1344  				t3 := t1.Join(t2).Using("invalid", "invalid2")
  1345  				return NewSelector[Order](db).From(t3).Select(t1.C("UsingCol2"))
  1346  			}(),
  1347  			wantErr: errs.NewInvalidFieldError("invalid"),
  1348  		},
  1349  		{
  1350  			name: "join-using-cols-Avg",
  1351  			s: func() QueryBuilder {
  1352  				t1 := TableOf(&Order{}, "t1")
  1353  				t2 := TableOf(&OrderDetail{}, "t2")
  1354  				t3 := t1.Join(t2).Using("UsingCol1", "UsingCol2")
  1355  				return NewSelector[Order](db).From(t3).Select(t1.Avg("UsingCol1").As("avg_using_col1"))
  1356  			}(),
  1357  			wantQuery: Query{
  1358  				SQL: "SELECT AVG(`t1`.`using_col1`) AS `avg_using_col1` FROM (`order` AS `t1` JOIN `order_detail` AS `t2` USING (`using_col1`,`using_col2`));",
  1359  			},
  1360  		},
  1361  		{
  1362  			name: "join-using-Avg-invalid",
  1363  			s: func() QueryBuilder {
  1364  				t1 := TableOf(&Order{}, "t1")
  1365  				t2 := TableOf(&OrderDetail{}, "t2")
  1366  				t3 := t1.Join(t2).Using("UsingCol1", "UsingCol2")
  1367  				return NewSelector[Order](db).From(t3).Select(t1.Avg("invalid"))
  1368  			}(),
  1369  			wantErr: errs.NewInvalidFieldError("invalid"),
  1370  		},
  1371  		{
  1372  			name: "join-using-where As",
  1373  			s: func() QueryBuilder {
  1374  				t1 := TableOf(&Order{}, "t1")
  1375  				t2 := TableOf(&OrderDetail{}, "t2")
  1376  				t3 := t1.Join(t2).Using("UsingCol1", "UsingCol2")
  1377  				return NewSelector[Order](db).Select(t1.AllColumns()).From(t3).Where(C("UsingCol1").EQ(10).And(C("UsingCol2").EQ(10)))
  1378  			}(),
  1379  			wantQuery: Query{
  1380  				SQL:  "SELECT `t1`.* FROM (`order` AS `t1` JOIN `order_detail` AS `t2` USING (`using_col1`,`using_col2`)) WHERE (`using_col1`=?) AND (`using_col2`=?);",
  1381  				Args: []interface{}{10, 10},
  1382  			},
  1383  		},
  1384  		{
  1385  			name: "join-on",
  1386  			s: func() QueryBuilder {
  1387  				t1 := TableOf(&Order{}, "t1")
  1388  				t2 := TableOf(&OrderDetail{}, "t2")
  1389  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1390  				return NewSelector[Order](db).Select(t1.AllColumns()).From(t3)
  1391  			}(),
  1392  			wantQuery: Query{
  1393  				SQL: "SELECT `t1`.* FROM (`order` AS `t1` JOIN `order_detail` AS `t2` ON `t1`.`id`=`t2`.`order_id`);",
  1394  			},
  1395  		},
  1396  		{
  1397  			name: "join-on-where As",
  1398  			s: func() QueryBuilder {
  1399  				t1 := TableOf(&Order{}, "t1")
  1400  				t2 := TableOf(&OrderDetail{}, "t2")
  1401  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1402  				return NewSelector[Order](db).Select(t1.AllColumns()).From(t3).Where(C("UsingCol1").EQ(10).And(C("UsingCol2").EQ(10)))
  1403  			}(),
  1404  			wantQuery: Query{
  1405  				SQL:  "SELECT `t1`.* FROM (`order` AS `t1` JOIN `order_detail` AS `t2` ON `t1`.`id`=`t2`.`order_id`) WHERE (`using_col1`=?) AND (`using_col2`=?);",
  1406  				Args: []interface{}{10, 10},
  1407  			},
  1408  		},
  1409  		{
  1410  			name: "join-on-where-invalid-clos",
  1411  			s: func() QueryBuilder {
  1412  				t1 := TableOf(&Order{}, "t1")
  1413  				t2 := TableOf(&OrderDetail{}, "t2")
  1414  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1415  				return NewSelector[Order](db).From(t3).Select(t1.C("invalid")).Where(C("invalid").EQ(10).And(C("UsingCol2").EQ(10)))
  1416  			}(),
  1417  			wantErr: errs.NewInvalidFieldError("invalid"),
  1418  		},
  1419  		{
  1420  			name: "join-on-where-invalid-Min-clos",
  1421  			s: func() QueryBuilder {
  1422  				t1 := TableOf(&Order{}, "t1")
  1423  				t2 := TableOf(&OrderDetail{}, "t2")
  1424  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1425  				return NewSelector[Order](db).From(t3).Select(t1.Min("invalid"), t1.C("invalid")).Where(C("invalid").EQ(10).And(C("UsingCol2").EQ(10)))
  1426  			}(),
  1427  			wantErr: errs.NewInvalidFieldError("invalid"),
  1428  		},
  1429  		{
  1430  			// SELECT MAX(t1.xxx), t2.xxx
  1431  			name: "join-on-where-Max-clos",
  1432  			s: func() QueryBuilder {
  1433  				t1 := TableOf(&Order{}, "t1")
  1434  				t2 := TableOf(&OrderDetail{}, "t2")
  1435  				t3 := t1.LeftJoin(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1436  				return NewSelector[Order](db).From(t3).Select(t1.Max("UsingCol1").As("UsingCol1"), t1.C("UsingCol2")).Where(t1.C("UsingCol2").EQ("UsingCol2_1").And(t1.C("UsingCol2").EQ("UsingCol2_2")))
  1437  			}(),
  1438  			wantQuery: Query{
  1439  				SQL:  "SELECT MAX(`t1`.`using_col1`) AS `UsingCol1`,`t1`.`using_col2` FROM (`order` AS `t1` LEFT JOIN `order_detail` AS `t2` ON `t1`.`id`=`t2`.`order_id`) WHERE (`t1`.`using_col2`=?) AND (`t1`.`using_col2`=?);",
  1440  				Args: []interface{}{"UsingCol2_1", "UsingCol2_2"}},
  1441  		},
  1442  		{
  1443  			name: "join table",
  1444  			s: func() QueryBuilder {
  1445  				t1 := TableOf(&Order{}, "t1")
  1446  				t2 := TableOf(&OrderDetail{}, "t2")
  1447  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1448  				t4 := TableOf(&Item{}, "t4")
  1449  				t5 := t3.Join(t4).On(t2.C("ItemId").EQ(t4.C("Id")))
  1450  				return NewSelector[Order](db).Select(t1.AllColumns()).From(t5)
  1451  			}(),
  1452  			wantQuery: Query{
  1453  				SQL: "SELECT `t1`.* FROM " +
  1454  					"((`order` AS `t1` JOIN `order_detail` AS `t2` ON `t1`.`id`=`t2`.`order_id`) " +
  1455  					"JOIN `item` AS `t4` ON `t2`.`item_id`=`t4`.`id`);",
  1456  			},
  1457  		},
  1458  		{
  1459  			name: "join table-right",
  1460  			s: func() QueryBuilder {
  1461  				t1 := TableOf(&Order{}, "t1")
  1462  				t2 := TableOf(&OrderDetail{}, "t2")
  1463  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1464  				t4 := TableOf(&Item{}, "t4")
  1465  				t5 := t3.RightJoin(t4).On(t2.C("ItemId").EQ(t4.C("Id")))
  1466  				return NewSelector[Order](db).Select(t1.AllColumns()).From(t5)
  1467  			}(),
  1468  			wantQuery: Query{
  1469  				SQL: "SELECT `t1`.* FROM ((`order` AS `t1` JOIN `order_detail` AS `t2` ON `t1`.`id`=`t2`.`order_id`) RIGHT JOIN `item` AS `t4` ON `t2`.`item_id`=`t4`.`id`);",
  1470  			},
  1471  		},
  1472  		{
  1473  			name: "join table-left",
  1474  			s: func() QueryBuilder {
  1475  				t1 := TableOf(&Order{}, "t1")
  1476  				t2 := TableOf(&OrderDetail{}, "t2")
  1477  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1478  				t4 := TableOf(&Item{}, "t4")
  1479  				t5 := t3.LeftJoin(t4).On(t2.C("ItemId").EQ(t4.C("Id")))
  1480  				return NewSelector[Order](db).Select(t1.AllColumns()).From(t5)
  1481  			}(),
  1482  			wantQuery: Query{
  1483  				SQL: "SELECT `t1`.* FROM ((`order` AS `t1` JOIN `order_detail` AS `t2` ON `t1`.`id`=`t2`.`order_id`) LEFT JOIN `item` AS `t4` ON `t2`.`item_id`=`t4`.`id`);",
  1484  			},
  1485  		},
  1486  		{
  1487  			// SELECT AVG(t1.xxx), AVG(t2.xxx)
  1488  			name: "join table AVG-AVG ",
  1489  			s: func() QueryBuilder {
  1490  				t1 := TableOf(&Order{}, "t1")
  1491  				t2 := TableOf(&OrderDetail{}, "t2")
  1492  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1493  				t4 := TableOf(&Item{}, "t4")
  1494  				t5 := t3.Join(t4).On(t2.C("ItemId").EQ(t4.C("Id")))
  1495  				return NewSelector[Order](db).From(t5).Select(t1.Avg("UsingCol1").As("UsingCol1"), t1.Avg("UsingCol2").As("UsingCol2"))
  1496  			}(),
  1497  			wantQuery: Query{
  1498  				SQL: "SELECT AVG(`t1`.`using_col1`) AS `UsingCol1`,AVG(`t1`.`using_col2`) AS `UsingCol2` FROM ((`order` AS `t1` JOIN `order_detail` AS `t2` ON `t1`.`id`=`t2`.`order_id`) JOIN `item` AS `t4` ON `t2`.`item_id`=`t4`.`id`);",
  1499  			},
  1500  		},
  1501  		{
  1502  			// SELECT AVG(t1.xxx), AVG(t2.xxx)
  1503  			name: "join table AVG-AVG invalid ",
  1504  			s: func() QueryBuilder {
  1505  				t1 := TableOf(&Order{}, "t1")
  1506  				t2 := TableOf(&OrderDetail{}, "t2")
  1507  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1508  				t4 := TableOf(&Item{}, "t4")
  1509  				t5 := t3.Join(t4).On(t2.C("ItemId").EQ(t4.C("Id")))
  1510  				return NewSelector[Order](db).From(t5).Select(t1.Avg("invalid"), t1.Avg("invalid"))
  1511  			}(),
  1512  			wantErr: errs.NewInvalidFieldError("invalid"),
  1513  		},
  1514  		{
  1515  			// SELECT t1.xxx, t2.xxx
  1516  			name: "join table C-C ",
  1517  			s: func() QueryBuilder {
  1518  				t1 := TableOf(&Order{}, "t1")
  1519  				t2 := TableOf(&OrderDetail{}, "t2")
  1520  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1521  				t4 := TableOf(&Item{}, "t4")
  1522  				t5 := t3.Join(t4).On(t2.C("ItemId").EQ(t4.C("Id")))
  1523  				return NewSelector[Order](db).From(t5).Select(t1.C("UsingCol1"), t1.C("UsingCol2"))
  1524  			}(),
  1525  			wantQuery: Query{
  1526  				SQL: "SELECT `t1`.`using_col1`,`t1`.`using_col2` FROM " +
  1527  					"((`order` AS `t1` JOIN `order_detail` AS `t2` ON `t1`.`id`=`t2`.`order_id`) " +
  1528  					"JOIN `item` AS `t4` ON `t2`.`item_id`=`t4`.`id`);",
  1529  			},
  1530  		},
  1531  		{
  1532  			// SELECT t1.xxx, t2.xxx
  1533  			name: "join table C-C invalid",
  1534  			s: func() QueryBuilder {
  1535  				t1 := TableOf(&Order{}, "t1")
  1536  				t2 := TableOf(&OrderDetail{}, "t2")
  1537  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1538  				t4 := TableOf(&Item{}, "t4")
  1539  				t5 := t3.Join(t4).On(t2.C("ItemId").EQ(t4.C("Id")))
  1540  				return NewSelector[Order](db).From(t5).Select(t1.C("invalid"), t1.C("invalid"))
  1541  			}(),
  1542  			wantErr: errs.NewInvalidFieldError("invalid"),
  1543  		},
  1544  		{
  1545  			name: "table join",
  1546  			s: func() QueryBuilder {
  1547  				t1 := TableOf(&Order{}, "t1")
  1548  				t2 := TableOf(&OrderDetail{}, "t2")
  1549  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1550  				t4 := TableOf(&Item{}, "t4")
  1551  				t5 := t4.Join(t3).On(t2.C("ItemId").EQ(t4.C("Id")))
  1552  				return NewSelector[Order](db).Select(t4.AllColumns()).From(t5)
  1553  			}(),
  1554  			wantQuery: Query{
  1555  				SQL: "SELECT `t4`.* FROM (`item` AS `t4` JOIN (`order` AS `t1` JOIN `order_detail` AS `t2` ON `t1`.`id`=`t2`.`order_id`) ON `t2`.`item_id`=`t4`.`id`);",
  1556  			},
  1557  		},
  1558  		{
  1559  			name: "table join on Sum",
  1560  			s: func() QueryBuilder {
  1561  				t1 := TableOf(&Order{}, "t1")
  1562  				t2 := TableOf(&OrderDetail{}, "t2")
  1563  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1564  				t4 := TableOf(&Item{}, "t4")
  1565  				t5 := t4.Join(t3).On(t2.C("ItemId").EQ(t4.C("Id")))
  1566  				return NewSelector[Order](db).From(t5).Select(t4.Sum("Id").As("sum_id"), t4.Min("Id").As("min_id"), t4.Max("Id").As("max_id"), t4.Sum("Id").As("t4_sum_id"), t4.Count("Id").As("t4_cnt_id"))
  1567  			}(),
  1568  			wantQuery: Query{
  1569  				SQL: "SELECT SUM(`t4`.`id`) AS `sum_id`,MIN(`t4`.`id`) AS `min_id`,MAX(`t4`.`id`) AS `max_id`,SUM(`t4`.`id`) AS `t4_sum_id`,COUNT(`t4`.`id`) AS `t4_cnt_id` FROM (`item` AS `t4` JOIN (`order` AS `t1` JOIN `order_detail` AS `t2` ON `t1`.`id`=`t2`.`order_id`) ON `t2`.`item_id`=`t4`.`id`);",
  1570  			},
  1571  		},
  1572  		{
  1573  			name: "table join col",
  1574  			s: func() QueryBuilder {
  1575  				t1 := TableOf(&test.Order{}, "t1")
  1576  				t2 := TableOf(&test.OrderDetail{}, "t2")
  1577  				t3 := t1.Join(t2).On(t1.C("Id").EQ(t2.C("OrderId")))
  1578  				return NewSelector[test.Order](db).From(t3).Select(t1.Avg("UsingCol1").As("UsingCol1"))
  1579  			}(),
  1580  			wantQuery: Query{
  1581  				SQL: "SELECT AVG(`t1`.`using_col1`) AS `UsingCol1` FROM (`order` AS `t1` JOIN `order_detail` AS `t2` ON `t1`.`id`=`t2`.`order_id`);",
  1582  			},
  1583  		},
  1584  	}
  1585  
  1586  	for _, tc := range testCases {
  1587  		t.Run(tc.name, func(t *testing.T) {
  1588  			q, err := tc.s.Build()
  1589  			assert.Equal(t, tc.wantErr, err)
  1590  			if err != nil {
  1591  				return
  1592  			}
  1593  			assert.Equal(t, tc.wantQuery, q)
  1594  		})
  1595  	}
  1596  }
  1597  
  1598  func TestSelector_Subquery(t *testing.T) {
  1599  	db := memoryDB()
  1600  	type Order struct {
  1601  		Id        int
  1602  		UsingCol1 string
  1603  		UsingCol2 string
  1604  	}
  1605  
  1606  	type OrderDetail struct {
  1607  		OrderId   int
  1608  		ItemId    int
  1609  		UsingCol1 string
  1610  		UsingCol2 string
  1611  	}
  1612  
  1613  	testCases := []struct {
  1614  		name      string
  1615  		s         QueryBuilder
  1616  		wantQuery Query
  1617  		wantErr   error
  1618  	}{
  1619  		// 子查詢
  1620  		{
  1621  			name: "from",
  1622  			s: func() QueryBuilder {
  1623  				sub := NewSelector[OrderDetail](db).AsSubquery("sub")
  1624  				return NewSelector[Order](db).Select(Raw("*")).From(sub)
  1625  			}(),
  1626  			wantQuery: Query{
  1627  				SQL: "SELECT * FROM (SELECT `order_id`,`item_id`,`using_col1`,`using_col2` FROM `order_detail`) AS `sub`;"},
  1628  		},
  1629  		{
  1630  			name: "from & where",
  1631  			s: func() QueryBuilder {
  1632  				o1 := TableOf(&OrderDetail{}, "o1")
  1633  				sub := NewSelector[OrderDetail](db).From(o1).Where(o1.C("OrderId").GT(18)).AsSubquery("sub")
  1634  				return NewSelector[Order](db).Select(Raw("*")).From(sub)
  1635  			}(),
  1636  
  1637  			wantQuery: Query{
  1638  				SQL:  "SELECT * FROM (SELECT `order_id`,`item_id`,`using_col1`,`using_col2` FROM `order_detail` AS `o1` WHERE `o1`.`order_id`>?) AS `sub`;",
  1639  				Args: []any{18},
  1640  			},
  1641  		},
  1642  		{
  1643  			name: "in",
  1644  			s: func() QueryBuilder {
  1645  				o1 := TableOf(&Order{}, "o1")
  1646  				sub := NewSelector[OrderDetail](db).Select(C("OrderId")).AsSubquery("sub")
  1647  				return NewSelector[Order](db).Select(o1.C("Id")).From(o1).Where(o1.C("Id").In(sub))
  1648  			}(),
  1649  			wantQuery: Query{
  1650  				SQL: "SELECT `o1`.`id` FROM `order` AS `o1` WHERE `o1`.`id` IN (SELECT `order_id` FROM `order_detail`);"},
  1651  		},
  1652  		{
  1653  			name: "all",
  1654  			s: func() QueryBuilder {
  1655  				o1 := TableOf(&Order{}, "o1")
  1656  				sub := NewSelector[OrderDetail](db).Select(C("OrderId")).AsSubquery("sub")
  1657  				return NewSelector[Order](db).Select(o1.C("Id"), o1.C("UsingCol1"), o1.C("UsingCol2")).From(o1).Where(o1.C("Id").GT(All(sub)))
  1658  			}(),
  1659  			wantQuery: Query{
  1660  				SQL: "SELECT `o1`.`id`,`o1`.`using_col1`,`o1`.`using_col2` FROM `order` AS `o1` WHERE `o1`.`id`>ALL (SELECT `order_id` FROM `order_detail`);"},
  1661  		},
  1662  		{
  1663  			name: "some and any",
  1664  			s: func() QueryBuilder {
  1665  				o1 := TableOf(&Order{}, "o1")
  1666  				sub := NewSelector[OrderDetail](db).Select(C("OrderId")).AsSubquery("sub")
  1667  				return NewSelector[Order](db).From(o1).Where(o1.C("Id").GT(Some(sub)), o1.C("Id").LT(Any(sub)))
  1668  			}(),
  1669  			wantQuery: Query{
  1670  				SQL: "SELECT `id`,`using_col1`,`using_col2` FROM `order` AS `o1` WHERE (`o1`.`id`>SOME (SELECT `order_id` FROM `order_detail`)) AND (`o1`.`id`<ANY (SELECT `order_id` FROM `order_detail`));"},
  1671  		},
  1672  		{
  1673  			name: "exist",
  1674  			s: func() QueryBuilder {
  1675  				sub := NewSelector[OrderDetail](db).Select(C("OrderId")).AsSubquery("sub")
  1676  				return NewSelector[Order](db).Where(Exist(sub))
  1677  			}(),
  1678  			wantQuery: Query{
  1679  				SQL: "SELECT `id`,`using_col1`,`using_col2` FROM `order` WHERE EXIST (SELECT `order_id` FROM `order_detail`);"},
  1680  		},
  1681  		{
  1682  			name: "not exist",
  1683  			s: func() QueryBuilder {
  1684  				sub := NewSelector[OrderDetail](db).Select(C("OrderId")).AsSubquery("sub")
  1685  				return NewSelector[Order](db).Where(Not(Exist(sub)))
  1686  			}(),
  1687  			wantQuery: Query{
  1688  				SQL: "SELECT `id`,`using_col1`,`using_col2` FROM `order` WHERE NOT (EXIST (SELECT `order_id` FROM `order_detail`));"},
  1689  		},
  1690  		{
  1691  			name: "aggregate",
  1692  			s: func() QueryBuilder {
  1693  				sub := NewSelector[OrderDetail](db).Select(C("OrderId")).AsSubquery("sub")
  1694  				return NewSelector[Order](db).Select(Max("Id")).Where(Exist(sub))
  1695  			}(),
  1696  			wantQuery: Query{
  1697  				SQL: "SELECT MAX(`id`) FROM `order` WHERE EXIST (SELECT `order_id` FROM `order_detail`);"},
  1698  		},
  1699  		{
  1700  			name: "invalid column",
  1701  			s: func() QueryBuilder {
  1702  				sub := NewSelector[OrderDetail](db).Select(C("OrderId")).AsSubquery("sub")
  1703  				return NewSelector[Order](db).Select(Max("invalid")).Where(Exist(sub))
  1704  			}(),
  1705  			wantErr: errs.NewInvalidFieldError("invalid"),
  1706  		},
  1707  		// Join 與 Subquery 一起使用測試
  1708  		{
  1709  			name: "join & subquery",
  1710  			s: func() QueryBuilder {
  1711  				sub1 := NewSelector[Order](db).AsSubquery("sub1")
  1712  				sub := NewSelector[OrderDetail](db).AsSubquery("sub")
  1713  				return NewSelector[Order](db).Select(sub.C("OrderId")).From(sub1.Join(sub).On(sub1.C("Id").EQ(sub.C("OrderId")))).Where()
  1714  			}(),
  1715  			wantQuery: Query{
  1716  				SQL: "SELECT `sub`.`order_id` FROM ((SELECT `id`,`using_col1`,`using_col2` FROM `order`) AS `sub1` JOIN (SELECT `order_id`,`item_id`,`using_col1`,`using_col2` FROM `order_detail`) AS `sub` ON `sub1`.`id`=`sub`.`order_id`);"},
  1717  		},
  1718  		{
  1719  			name: "left join & subquery",
  1720  			s: func() QueryBuilder {
  1721  				sub1 := NewSelector[Order](db).AsSubquery("sub1")
  1722  				sub := NewSelector[OrderDetail](db).AsSubquery("sub")
  1723  				return NewSelector[Order](db).Select(sub.C("OrderId")).From(sub1.LeftJoin(sub).On(sub1.C("Id").EQ(sub.C("OrderId")))).Where()
  1724  			}(),
  1725  			wantQuery: Query{
  1726  				SQL: "SELECT `sub`.`order_id` FROM ((SELECT `id`,`using_col1`,`using_col2` FROM `order`) AS `sub1` LEFT JOIN (SELECT `order_id`,`item_id`,`using_col1`,`using_col2` FROM `order_detail`) AS `sub` ON `sub1`.`id`=`sub`.`order_id`);"},
  1727  		},
  1728  		{
  1729  			name: "right join & subquery",
  1730  			s: func() QueryBuilder {
  1731  				sub1 := NewSelector[Order](db).AsSubquery("sub1")
  1732  				sub := NewSelector[OrderDetail](db).AsSubquery("sub")
  1733  				return NewSelector[Order](db).Select(sub.C("OrderId")).From(sub1.RightJoin(sub).On(sub1.C("Id").EQ(sub.C("OrderId")))).Where()
  1734  			}(),
  1735  			wantQuery: Query{
  1736  				SQL: "SELECT `sub`.`order_id` FROM ((SELECT `id`,`using_col1`,`using_col2` FROM `order`) AS `sub1` RIGHT JOIN (SELECT `order_id`,`item_id`,`using_col1`,`using_col2` FROM `order_detail`) AS `sub` ON `sub1`.`id`=`sub`.`order_id`);"},
  1737  		},
  1738  		{
  1739  			name: "right join & subquery & using",
  1740  			s: func() QueryBuilder {
  1741  				sub1 := NewSelector[OrderDetail](db).AsSubquery("sub1")
  1742  				sub2 := NewSelector[OrderDetail](db).AsSubquery("sub2")
  1743  				return NewSelector[Order](db).Select(sub1.C("OrderId")).From(sub1.RightJoin(sub2).Using("Id")).Where()
  1744  			}(),
  1745  			wantQuery: Query{
  1746  				SQL: "SELECT `sub1`.`order_id` FROM ((SELECT `order_id`,`item_id`,`using_col1`,`using_col2` FROM `order_detail`) AS `sub1` RIGHT JOIN (SELECT `order_id`,`item_id`,`using_col1`,`using_col2` FROM `order_detail`) AS `sub2` USING (`id`));"},
  1747  		},
  1748  		{
  1749  			name: "join & subquery & using",
  1750  			s: func() QueryBuilder {
  1751  				sub1 := NewSelector[OrderDetail](db).AsSubquery("sub1")
  1752  				sub2 := NewSelector[OrderDetail](db).Select(sub1.C("OrderId")).From(sub1).AsSubquery("sub2")
  1753  				t1 := TableOf(&Order{}, "")
  1754  				return NewSelector[Order](db).Select(t1.C("Id")).From(sub2.Join(sub1).Using("Id")).Where()
  1755  			}(),
  1756  			wantQuery: Query{
  1757  				SQL: "SELECT `id` FROM ((SELECT `sub1`.`order_id` FROM (SELECT `order_id`,`item_id`,`using_col1`,`using_col2` FROM `order_detail`) AS `sub1`) AS `sub2` JOIN (SELECT `order_id`,`item_id`,`using_col1`,`using_col2` FROM `order_detail`) AS `sub1` USING (`id`));"},
  1758  		},
  1759  		{
  1760  			name: "invalid field",
  1761  			s: func() QueryBuilder {
  1762  				t1 := TableOf(&Order{}, "")
  1763  				sub := NewSelector[OrderDetail](db).AsSubquery("sub")
  1764  				return NewSelector[Order](db).Select(sub.C("Invalid")).From(t1.Join(sub).On(t1.C("Id").EQ(sub.C("OrderId")))).Where()
  1765  			}(),
  1766  			wantErr: errs.NewInvalidFieldError("Invalid"),
  1767  		},
  1768  		{
  1769  			name: "invalid field in predicates",
  1770  			s: func() QueryBuilder {
  1771  				t1 := TableOf(&Order{}, "")
  1772  				sub := NewSelector[OrderDetail](db).AsSubquery("sub")
  1773  				return NewSelector[Order](db).Select(sub.C("OrderId")).From(t1.Join(sub).On(t1.C("Id").EQ(sub.C("Invalid")))).Where()
  1774  			}(),
  1775  			wantErr: errs.NewInvalidFieldError("Invalid"),
  1776  		},
  1777  		{
  1778  			name: "invalid field in predicates with columns",
  1779  			s: func() QueryBuilder {
  1780  				t1 := TableOf(&Order{}, "")
  1781  				sub := NewSelector[OrderDetail](db).Select(C("OrderId")).AsSubquery("sub")
  1782  				return NewSelector[Order](db).Select(sub.C("Invalid")).From(t1.Join(sub).On(t1.C("Id").EQ(sub.C("OrderId")))).Where()
  1783  			}(),
  1784  			wantErr: errs.NewInvalidFieldError("Invalid"),
  1785  		},
  1786  		{
  1787  			name: "invalid field in aggregate function",
  1788  			s: func() QueryBuilder {
  1789  				t1 := TableOf(&Order{}, "")
  1790  				sub := NewSelector[OrderDetail](db).AsSubquery("sub")
  1791  				return NewSelector[Order](db).Select(Max("Invalid")).From(t1.Join(sub).On(t1.C("Id").EQ(sub.C("OrderId")))).Where()
  1792  			}(),
  1793  			wantErr: errs.NewInvalidFieldError("Invalid"),
  1794  		},
  1795  	}
  1796  
  1797  	for _, tc := range testCases {
  1798  		t.Run(tc.name, func(t *testing.T) {
  1799  			q, err := tc.s.Build()
  1800  			assert.Equal(t, tc.wantErr, err)
  1801  			if err != nil {
  1802  				return
  1803  			}
  1804  			assert.Equal(t, tc.wantQuery, q)
  1805  		})
  1806  	}
  1807  }