In my last article, I shared some of my own experiences and extensions using GORM in new and updated scenarios. In this article, I’m going to share some insights on queries.

First, I split the query into the number of tables involved:

Single table query
Multi-table query

According to the scope of query, it can be divided into:

Query a
Range queries
- Query a set of
- Orderly query
- Query the first few
- Paging query

In daily use, single table query occupies most of the scenario, this part of the code according to the scope of the query to do some encapsulation, can greatly reduce the redundant code.

Single table query

So, I did the following wrapper in the style of the GORM API:

Ps: The following examples assume that the User object has been defined

Query a

func (dw *DBExtension) GetOne(result interface{}, query interface{}, args ... interface{}) (found bool, err error) { var ( tableNameAble TableNameAble ok bool )iftableNameAble, ok = query.(TableNameAble); ! ok {iftableNameAble, ok = result.(TableNameAble); ! ok {return false, errors.New("neither the query nor result implement TableNameAble") } } err = dw.Table(tableNameAble.TableName()).Where(query, args...) .First(result).Errorif err == gorm.ErrRecordNotFound {
		dw.logger.LogInfoc("mysql", fmt.Sprintf("record not found for query %s, the query is %+v, args are %+v", tableNameAble.TableName(), query, args))
		return false, nil
	}

	iferr ! = nil { dw.logger.LogErrorc("mysql", err, fmt.Sprintf("failed to query %s, the query is %+v, args are %+v", tableNameAble.TableName(), query, args))
		return false, err
	}

	return true, nil
}
Copy the code

Error: Gorm.ErrRecordNotFound error: Gorm.ErrRecordNotFound Boolean error: Gorm.ErrRecordNotFound Boolean

The call code is as follows:

condition := User{Id:1}
result := User{}

iffound, err := dw.GetOne(&result, condition); ! found { //not foundiferr ! = nil { // has errorreturn err
    }
    
}
Copy the code

You can also write this to specify more flexible query conditions:

result := User{}

if  found, err := dw.GetOne(&result, "Id =?", 1); ! found { //not foundiferr ! = nil { // has errorreturn err
    }
    
}
Copy the code

Either way, the statement executes:

select * from test.user where id = 1
Copy the code

Range queries

For the four kinds of fan country query, I made the following encapsulation:

func (dw *DBExtension) GetList(result interface{}, query interface{}, args ... interface{}) error {return dw.getListCore(result, "", 0, 0, query, args) } func (dw *DBExtension) GetOrderedList(result interface{}, order string, query interface{}, args ... interface{}) error {return dw.getListCore(result, order, 0, 0, query, args)
}

func (dw *DBExtension) GetFirstNRecords(result interface{}, order string, limitint, query interface{}, args ... interface{}) error {return dw.getListCore(result, order, limit, 0, query, args)
}

func (dw *DBExtension) GetPageRangeList(result interface{}, order string, limit, offset int, query interface{}, args ... interface{}) error {return dw.getListCore(result, order, limit, offset, query, args)
}

func (dw *DBExtension) getListCore(result interface{}, order string, limit, offset int, query interface{}, args []interface{}) error {
	var (
		tableNameAble TableNameAble
		ok            bool
	)

	iftableNameAble, ok = query.(TableNameAble); ! ok { //type Result []*Item{}
		// result := &Result{}
		resultType := reflect.TypeOf(result)
		ifresultType.Kind() ! = reflect.Ptr {return errors.New("result is not a pointer")
		}

		sliceType := resultType.Elem()
		ifsliceType.Kind() ! = reflect.Slice {return errors.New("result doesn't point to a slice")
		}
		// *Item
		itemPtrType := sliceType.Elem()
		// Item
		itemType := itemPtrType.Elem()

		elemValue := reflect.New(itemType)
		elemValueType := reflect.TypeOf(elemValue)
		tableNameAbleType := reflect.TypeOf((*TableNameAble)(nil)).Elem()

		if elemValueType.Implements(tableNameAbleType) {
			return errors.New("neither the query nor result implement TableNameAble")
		}

		tableNameAble = elemValue.Interface().(TableNameAble)
	}

	db := dw.Table(tableNameAble.TableName()).Where(query, args...)
	iflen(order) ! = 0 { db = db.Order(order) }if offset > 0 {
		db = db.Offset(offset)
	}

	if limit > 0 {
		db = db.Limit(limit)}iferr := db.Find(result).Error; err ! = nil { dw.logger.LogErrorc("mysql", err, fmt.Sprintf("failed to query %s, query is %+v, args are %+v, order is %s, limit is %d", tableNameAble.TableName(), query, args, order, limit))
		return err
	}

	return nil
}
Copy the code

To reduce redundant code, the general logic is written in the getListCore function, which uses some knowledge of Golang reflection.

But remember that the biggest difference between Golang’s reflections and those of other languages is that Golang’s reflections are base values rather than types.

One of the tricks is how to tell if a type implements an interface, using a pointer to nil.

	elemValue := reflect.New(itemType)
	elemValueType := reflect.TypeOf(elemValue)
	tableNameAbleType := reflect.TypeOf((*TableNameAble)(nil)).Elem()

	if elemValueType.Implements(tableNameAbleType) {
		return errors.New("neither the query nor result implement TableNameAble")}Copy the code

On the specific use, there is no one example, familiar with the GORM API students can see at a glance.

Multi-table query

With respect to multi-table queries, encapsulation is not done because it is difficult to extract differences between scenarios, but my experience is to use GORM methods in preference to composing SQL myself. You can do whatever you want with GORM.

Here, I’ve taken the liberty of Posting the most complex piece of code I’ve ever written on a project for your amusement.

A complicated example

This code is from the middle table of buried data, in order to use the general code to achieve different display scenarios of the query, the code design is more flexible, which involves the association of multi-table query, dynamic filtering and aggregation according to the query conditions, as well as the logic of paging query.

func buildCommonStatisticQuery(tableName, startDate, endDate string) *gorm.DB {
	query := models.DB().Table(tableName)

	if startDate == endDate || endDate == "" {
		query = query.Where("date = ?", startDate)
	} else {
		query = query.Where("date >= ? and date <= ?", startDate, endDate)
	}

	return query
}

func buildElementsStatisticQuery(startDate, endDate,  elemId string,  elemType int32) *gorm.DB {
	query := buildCommonStatisticQuery("spotanalysis.element_statistics", startDate, endDate)

	ifelemId ! =""&& elemType ! = 0 { query = query.Where("element_id = ? and element_type = ?", elemId, elemType)
	}

	return query
}

func CountElementsStatistics(count *int32, startDate, endDate, instId, appId, elemId string, elemType int32, groupFields []string ) error {
	query := buildElementsStatisticQuery(startDate, endDate,  elemId, elemType)

	query = whereInstAndApp(query, instId, appId)

	iflen(groupFields) ! = 0 { query = query.Select(fmt.Sprintf("count(distinct(concat(%s)))", strings.Join(groupFields, ",")))}else {
		query = query.Select("count(id)")
	}

	query = query.Count(count)
	return query.Error
}


func GetElementsStatistics(result interface{}, startDate, endDate, instId, appId, elemId string, elemType int32, groupFields []string, orderBy string, ascOrder bool, limit, offset int32) error {
	query := buildElementsStatisticQuery(startDate, endDate, elemId, elemType)
	iflen(groupFields) ! = 0 { groupBy := strings.Join(groupFields,"` `,")
		groupBy = "`" + groupBy + "`"
		query = query.Group(groupBy)
		query = havingInstAndApp(query, instId, appId)

		sumFields := strings.Join([]string{
			"SUM(`element_statistics`.`mp_count`) AS `mp_count`"."SUM(`element_statistics`.`h5_count`) AS `h5_count`"."SUM(`element_statistics`.`total_count`) AS `total_count`"."SUM(`element_statistics`.`collection_count`) AS `collection_count`"."SUM(`element_statistics`.`mp_share_count`) AS `mp_share_count`"."SUM(`element_statistics`.`h5_share_count`) AS `h5_share_count`"."SUM(`element_statistics`.`poster_share_count`) AS `poster_share_count`"."SUM(`element_statistics`.`total_share_count`) AS `total_share_count`",},",")

		query = query.Select(groupBy + "," + sumFields)
	} else {
		query = whereInstAndApp(query, instId, appId)
	}

	query = getPagedList(query, orderBy, ascOrder, limit, offset)

	return query.Find(result).Error
}

func getPagedList(query *gorm.DB, orderBy string, ascOrder bool, limit , offset int32) *gorm.DB {
	iforderBy ! ="" {
		if ascOrder {
			orderBy += " asc"
		} else {
			orderBy += " desc"
		}
		query = query.Order(orderBy)
	}

	ifoffset ! = 0 { query = query.Offset(offset) }if limit! = 0 { query = query.Limit(limit)}return query
}

func whereInstAndApp(query *gorm.DB, instId string, appId string) *gorm.DB {
	query = query.Where("inst_id = ?", instId)
	ifappId ! ="" {
		query = query.Where("app_id = ?", appId)
	}
	return query
}

func havingInstAndApp(query *gorm.DB, instId string, appId string) *gorm.DB {
	query = query.Having("inst_id = ?", instId)
	ifappId ! ="" {
		query = query.Having("app_id = ?", appId)
	}
	return query
}

Copy the code

Thank you for your patience. If this article is useful to you, please give it a thumbs up

Github:Ksloveyuan/ Gorm-ex will take a look at the code from outside.

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Gorm Usage experience and Some Common Extensions (2)

Single table query

Query a

Range queries

Multi-table query

A complicated example

Gorm Usage experience and Some Common Extensions (2)

Single table query

Query a

Range queries

Multi-table query

A complicated example

Related Posts

Why do floating-point operations lose precision?

Build API interface with Node.js+ Express + Cloud MongoDB Atlas

Linux Namespace Introduction Series: Namespace API