github.com/gogf/gf@v1.16.9/database/gdb/gdb_z_mysql_subquery_test.go (about)

     1  // Copyright GoFrame Author(https://goframe.org). All Rights Reserved.
     2  //
     3  // This Source Code Form is subject to the terms of the MIT License.
     4  // If a copy of the MIT was not distributed with this file,
     5  // You can obtain one at https://github.com/gogf/gf.
     6  
     7  package gdb_test
     8  
     9  import (
    10  	"github.com/gogf/gf/frame/g"
    11  	"testing"
    12  
    13  	"github.com/gogf/gf/test/gtest"
    14  )
    15  
    16  func Test_Model_SubQuery_Where(t *testing.T) {
    17  	table := createInitTable()
    18  	defer dropTable(table)
    19  
    20  	gtest.C(t, func(t *gtest.T) {
    21  		r, err := db.Model(table).Where(
    22  			"id in ?",
    23  			db.Model(table).Fields("id").Where("id", g.Slice{1, 3, 5}),
    24  		).OrderAsc("id").All()
    25  		t.AssertNil(err)
    26  
    27  		t.Assert(len(r), 3)
    28  		t.Assert(r[0]["id"], 1)
    29  		t.Assert(r[1]["id"], 3)
    30  		t.Assert(r[2]["id"], 5)
    31  	})
    32  }
    33  
    34  func Test_Model_SubQuery_Having(t *testing.T) {
    35  	table := createInitTable()
    36  	defer dropTable(table)
    37  
    38  	gtest.C(t, func(t *gtest.T) {
    39  		r, err := db.Model(table).Where(
    40  			"id in ?",
    41  			db.Model(table).Fields("id").Where("id", g.Slice{1, 3, 5}),
    42  		).Having(
    43  			"id > ?",
    44  			db.Model(table).Fields("MAX(id)").Where("id", g.Slice{1, 3}),
    45  		).OrderAsc("id").All()
    46  		t.AssertNil(err)
    47  
    48  		t.Assert(len(r), 1)
    49  		t.Assert(r[0]["id"], 5)
    50  	})
    51  }
    52  
    53  func Test_Model_SubQuery_Model(t *testing.T) {
    54  	table := createInitTable()
    55  	defer dropTable(table)
    56  
    57  	gtest.C(t, func(t *gtest.T) {
    58  		subQuery1 := db.Model(table).Where("id", g.Slice{1, 3, 5})
    59  		subQuery2 := db.Model(table).Where("id", g.Slice{5, 7, 9})
    60  		r, err := db.Model("? AS a, ? AS b", subQuery1, subQuery2).Fields("a.id").Where("a.id=b.id").OrderAsc("id").All()
    61  		t.AssertNil(err)
    62  
    63  		t.Assert(len(r), 1)
    64  		t.Assert(r[0]["id"], 5)
    65  	})
    66  }