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

Comment From: chenlujjj

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

Comment From: ghost

@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.

Comment From: 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

Comment From: 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

@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.

Comment From: chenlujjj

@longlihale Thank you, I will try on this.

Comment From: Shellbye

Did this fixed?

Comment From: a631807682

@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

Comment From: a631807682

related to https://github.com/go-gorm/hints/issues/73 https://github.com/go-gorm/gorm/issues/5808

Comment From: yiranzai

Did this fixed?

Comment From: fsmytsai

還沒修好，都這麼久了...