gorm How can I use `ForceIndex` with `Join`

Your Question

When using ForceIndexhint with Join table, there is a syntx error in SQL.

go code like:

d.db.Debug().Clauses(hints.ForceIndex("idx")).Model(model.Model{}).
		Joins("join table2 on ...").
		Where(...)

sql:

SELECT ... FROM `table1` join table2 on ... FORCE INDEX (`idx`) WHERE ...

The "FORCE INDEX" place in the sql is wrong, it should be right after "FROM table1".

So how should I use ForceIndex with Join ?

The document you expected this should be explained

https://gorm.io/docs/hints.html

Expected answer

Sep 28 '21 08:09 chenlujjj

@jinzhu Hi jinzhu, Is this a wrong way of using hints or potential bug?

Sep 29 '21 03:09 chenlujjj

@jinzhu Hi jinzhu, Is this a wrong way of using hints or potential bug?

@chenlujjj hello, actually it is bug,we will solve it in the near future.

Sep 29 '21 07:09 ghost

@longlihale Thank you for the confirmation. Actually I have been trying to debug, but it's a bit hard to find out how the clauses/joins turn to SQL. Any blog/doc/resources about this? Chinese or English is OK

Sep 29 '21 12:09 chenlujjj

@longlihale Thank you for the confirmation. Actually I have been trying to debug, but it's a bit hard to find out how the clauses/joins turn to SQL. Any blog/doc/resources about this? Chinese or English is OK

@chenlujjj Hello, you can look here.

execute-fcuntion.

https://github.com/go-gorm/gorm/blob/851fea0221ff6ab53e3b9ce2d127c2126bd9a6f0/callbacks.go#L75

execute callback function.

https://github.com/go-gorm/gorm/blob/851fea0221ff6ab53e3b9ce2d127c2126bd9a6f0/callbacks.go#L129

execute query callback function

https://github.com/go-gorm/gorm/blob/851fea0221ff6ab53e3b9ce2d127c2126bd9a6f0/callbacks/query.go#L13

join process

https://github.com/go-gorm/gorm/blob/851fea0221ff6ab53e3b9ce2d127c2126bd9a6f0/callbacks/query.go#L98

you can try to follow this process to breakpoint debugging.

Sep 30 '21 07:09 ghost

@longlihale Thank you, I will try on this.

Sep 30 '21 11:09 chenlujjj

Did this fixed?

May 11 '22 11:05 Shellbye

@Shellbye it hasn't been fixed

The hint was set on AfterExpression from clause.From, but clause.From contains Tables and Joins, we cannot set hint before or after them, we need to set it in the middle, which is currently not supported.

This is the failing test case

func TestJoinIndexHint(t *testing.T) {
	result := DB.Clauses(hints.ForceIndex("user_name")).Joins("Company").Find(&User{})

	AssertSQL(t, result, "SELECT `users`.`id`,`users`.`name`,`users`.`company_id`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` FORCE INDEX (`user_name`) LEFT JOIN `companies` `Company` ON `users`.`company_id` = `Company`.`id`")
}

https://github.com/go-gorm/hints/blob/master/index_hint.go#L17