#7076·gorm

Scopes are not processed when evaluating Join conditions

Author: systemmonkey42Created Jun 20, 2024Updated Aug 5, 2026

Labelstype:with reproduction stepsstatus:stale

Playground Pull Request Link

https://github.com/go-gorm/playground/pull/743

Description

When using Joins/InnerJoins, there is an inconsistency about how the join clause is processed.

My application uses a number of preprepared filters, including some generated dynamically. When used as Scopes() for normal queries, they work fine, but when they are used as a clause in a Join() or InnerJoin() they are ignored

db.Find(&User{})

sql

SELECT
    *
FROM
    `users`
WHERE
    `users`.`deleted_at` IS NULL

db.Joins("Group").Find(&User{})

sql

SELECT
    `users`.`id`,
    `users`.`created_at`,
    `users`.`updated_at`,
    `users`.`deleted_at`,
    `users`.`name`,
    `users`.`group_id`,
    `Group`.`id` AS `Group__id`,
    `Group`.`created_at` AS `Group__created_at`,
    `Group`.`updated_at` AS `Group__updated_at`,
    `Group`.`deleted_at` AS `Group__deleted_at`,
    `Group`.`name` AS `Group__name`
FROM
    `users`
    LEFT JOIN `groups` `Group` ON `users`.`group_id` = `Group`.`id`
    AND `Group`.`deleted_at` IS NULL
WHERE
    `users`.`deleted_at` IS NULL

db.Joins("Group", db.Where(&Group{Model: &gorm.Model{ID: 2}})).Find(&User{})

Note the "AND Group.id = 2" - this is as expected.

sql

SELECT
    `users`.`id`,
    `users`.`created_at`,
    `users`.`updated_at`,
    `users`.`deleted_at`,
    `users`.`name`,
    `users`.`group_id`,
    `Group`.`id` AS `Group__id`,
    `Group`.`created_at` AS `Group__created_at`,
    `Group`.`updated_at` AS `Group__updated_at`,
    `Group`.`deleted_at` AS `Group__deleted_at`,
    `Group`.`name` AS `Group__name`
FROM
    `users`
    LEFT JOIN `groups` `Group` ON `users`.`group_id` = `Group`.`id`
    AND (
        `Group`.`deleted_at` IS NULL
        AND `Group`.`id` = 2
    )
WHERE
    `users`.`deleted_at` IS NULL

GroupFilter := func(id uint) func(*gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		return db.Where(&Group{Model: &gorm.Model{ID: id}})
	}
}
db.Joins("Group", db.Scopes(GroupFilter(2))).Find(&User{})

In this case the parameter is checked, and isnt a simple "Where" clause, so it is ignored.

sql

SELECT
    `users`.`id`,
    `users`.`created_at`,
    `users`.`updated_at`,
    `users`.`deleted_at`,
    `users`.`name`,
    `users`.`group_id`,
    `Group`.`id` AS `Group__id`,
    `Group`.`created_at` AS `Group__created_at`,
    `Group`.`updated_at` AS `Group__updated_at`,
    `Group`.`deleted_at` AS `Group__deleted_at`,
    `Group`.`name` AS `Group__name`
FROM
    `users`
    LEFT JOIN `groups` `Group` ON `users`.`group_id` = `Group`.`id`
    AND `Group`.`deleted_at` IS NULL
WHERE
    `users`.`deleted_at` IS NULL

If "chainable_api.go" included a call to db.executeScopes() before checking

	if where, ok := db.Statement.Clauses["WHERE"].Expression.(clause.Where); ok {
		j.On = &where
	}

This would work.

(Ps, I'm new to gorm playground, and I couldn't get past the gorm.io/gen/examples/dal: cannot find module providing package gorm.io/gen/examples/dal error)

Go playground test output

bash

git clone --depth 1 -b master https://github.com/go-gorm/gorm.git
Cloning into 'gorm'...
remote: Enumerating objects: 180, done.
remote: Counting objects: 100% (180/180), done.
remote: Compressing objects: 100% (172/172), done.
remote: Total 180 (delta 13), reused 52 (delta 6), pack-reused 0
Receiving objects: 100% (180/180), 233.63 KiB | 3.49 MiB/s, done.
Resolving deltas: 100% (13/13), done.
gorm.io/playground imports
        gorm.io/gen/examples/dal: cannot find module providing package gorm.io/gen/examples/dal

Sample Code

package main

import (
	"log"
	"os"
	"time"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

type User struct {
	*gorm.Model
	Name    string
	GroupID uint

	Group Group
}

type Group struct {
	*gorm.Model
	Name string
}

func main() {
	dbDriver := sqlite.Open("test.db")

	db, _ := gorm.Open(dbDriver, &gorm.Config{})

	err := db.AutoMigrate(
		&User{},
		&Group{},
	)

	db = db.Debug()
	if err != nil {
		panic(err)
	}

	db.Find(&User{})
	db.Joins("Group").Find(&User{})
	db.Joins("Group", db.Where(&Group{Model: &gorm.Model{ID: 2}})).Find(&User{})

	GroupFilter := func(id uint) func(*gorm.DB) *gorm.DB {
		return func(db *gorm.DB) *gorm.DB {
			return db.Where(&Group{Model: &gorm.Model{ID: id}})
		}
	}
	db.Joins("Group", db.Scopes(GroupFilter(2))).Find(&User{})
}

Edit: formatting

Source: go-gorm/gorm

View original on GitHub View discussion on GitHub