How to insert comment or fragment in sql?

[canuran]

Such as select name from user /* shard_id=1 */ where name like 'can%', the shard_id comment is to tell the database proxy how to route to the sharded database.

Suggest: SELECT(User.Name).FROM(User).FRAG(FRAG("/* shard_id=1 */").BeforeWhere()).WHERE(...)

[go-jet]

Hi @canuran
Can you use optimizer hints instead?

[canuran]

Hi @canuran Can you use optimizer hints instead?

It seems impossible, optimizer_hints can only be used for mysql and the format is fixed, not suitable for prompting db proxy.

[houtn11]

What database proxy are you using? Does a comment have to appear at a specific position inside the statement?

[dragondgold]

Is there any way to do this with the latest version?

[houtn11]

No, it is not possible.

[go-jet]

@dragondgold Is your use case the same as the OP? Comment before WHERE or after FROM? What proxy is this?

[dragondgold]

@dragondgold Is your use case the same as the OP? Comment before WHERE or after FROM? What proxy is this?

My usecase is actually for tracing. I would like to add comment just before the query with a trace ID

[go-jet]

Workaround would be to modify sql and call qrm manually.

In @dragondgold case:

func QueryWithTrace(ctx context.Context, traceID int, db qrm.Queryable, stmt Statement, dest any) error {
	sql, args := stmt.Sql()

	tracedSQL := fmt.Sprintf("-- Trace: %d", traceID) + sql

	_, err := qrm.Query(ctx, db, tracedSQL, args, dest)

	return err
}

Now instead stmt.Query(db, &dest), the query call would be:

var dest model.Users

err := QueryWithTrace(ctx, 11, db, stmt, &dest)

Similarly in @canuran case:

func QueryWithShardID(ctx context.Context, shardID int, db qrm.Queryable, stmt Statement, dest any) error {
	sql, args := stmt.Sql()

	shardedSQL := sql

	if idx := strings.Index(sql, "WHERE"); idx != -1 {
		shardedSQL = sql[:idx] + fmt.Sprintf("/* shard_id=%d */ ", shardID) + sql[idx:]
	}

	_, err := qrm.Query(ctx, db, shardedSQL, args, dest)

	return err
}

Also note that prepared statement caching should not be used with this modified queries, since new prepared statements will be created and cached for every distinct comment.