# sql-gen

**Repository Path**: AZcoding/sql-gen

## Basic Information

- **Project Name**: sql-gen
- **Description**: SQL构造器
辅助生成SQL，兼容查询Doris的场景
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: main
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2024-12-20
- **Last Updated**: 2025-07-17

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# SQL构造器
辅助生成SQL，兼容查询Doris的场景

本项目背景：
使用GORM查询Doris数据库时，目前使用原生的Find()方法做查询，会出现报错（Error 1047: Unsupported command(COM_STMT_PREPARE)），Find()方法底层执行了mysql的COM_STMT_PREPARE方法，Doris并不支持该命令，会出现报错。因此，需要使用SQL构造器来生成SQL语句，然后使用原生Execute()方法执行。

```
    gen := utils.NewSqlGen()
	query1, _ := gen.Select("name", "user_id", "class_id").Where(gen.Ge("age", 20)).From("t_user").Limit(10).ToSql()
	query2, _ := gen.Select("id", "name", "class_type", "created_at").Where(gen.Equal("class_id", 1)).From("t_class").ToSql()

	query, err := gen.CTE(
		utils.CTE{Alias: "class_info", Sql: query2},
		utils.CTE{Alias: "user_info", Sql: query1},
	).Select("class_info.name", "user_info.name", "learn_progress").
		From("t_user_learn_record").
		Where(gen.In("class_info.class_type", []int{1, 2}), gen.Ge("class_info.created_at", time.Now().Format("2006-01-02"))).
		Join("JOIN user_info ON class_info.id = user_info.class_id",
			"JOIN t_user_learn_record ON t_user_learn_record.user_id = userInfo.user_id").
		GroupBy("t_user_learn_record.user_id,t_user_learn_record.learn_progress").
		OrderBy("t_user_learn_record.learn_progress DESC").
		Limit(10).
		ToSql()
	if err != nil {
		t.Error(err)
		return
	}
	fmt.Println("query3:", query)
```

```
WITH class_info AS (SELECT id, name, class_type, created_at FROM t_class WHERE class_id = 1),
     user_info AS (SELECT name, user_id, class_id FROM t_user WHERE age >= 20 LIMIT 10)

SELECT class_info.name, user_info.name, learn_progress
FROM t_user_learn_record
         JOIN user_info ON class_info.id = user_info.class_id
         JOIN t_user_learn_record ON t_user_learn_record.user_id = userInfo.user_id
WHERE class_info.class_type IN (1, 2)
  AND class_info.created_at >= '2024-12-20'
GROUP BY t_user_learn_record.user_id, t_user_learn_record.learn_progress
LIMIT 10;
```