CODEWITHSUNDEEP
gogo-gorm
LordBoBCUP
LordBoBCUP

Reputation: 103

Go GORM Preload & Select only items matching on preload table condition

Im trying to use GORM to select only items from the parent table that have a matching condition in a related table.

type Table1 struct {
    gorm.Model
    Name string
    Email string
    Items Table2
}

type Table2 struct {
    gorm.Model
    Product string
    otherfield string
}

I want to return all Table1 items that have Product in Table2 set to a specific value. So far I am getting mssql: The multi-part identifier "visits.sign_out_time" could not be bound. a lot.

My command is 

var items []Table2
db.Debug().Preload("Table2").Where("table2.product = ?", "xxx").Find(&items).GetErrors()

Not entirely sure where I'm going wrong but for whatever reason the .Where() cannot access the second, preloaded table. How do I go about using GORM to achieve what I am trying to do?

Thanks, Alex

Upvotes: 0

Views: 3690

Answers (2)

Eklavya
Eklavya

Reputation: 18450

If you only want only Items, you can directly query on Table2 no need to preload Table1

var items []Table2
db.Where("product = ?", "xxx").Find(&items).GetErrors()

Or you need all data of Table1 then Join with table2 then use where clause 

db.Debug().Joins("JOIN table2 ON table1.id = table2.table1_id")
          .Where("table2.product = ?", "xxxx").Find(&table1data)

Here I don't see any foreign key in Table2 for join.You can add one.

type Table2 struct {
    gorm.Model
    Product  string
    Table1ID uint
}

Upvotes: 0

Anton
Anton

Reputation: 1966

The Where("table2.product = ?", "xxx") cannot access the second (preloaded) table because Preload is not a JOINS, it's a separate SELECT query. Your code creates two separate queries, something like this:

// first query
SELECT * FROM table1 WHERE table2.product = 'xxx';
// second query
SELECT * FROM table2;

In order to return all Table1 records that have Product in Table2 set to a specific value you have to do the following:

var t1 []Table1
err = db.
Where(`EXISTS(SELECT 1 FROM table2 t2 WHERE t2.product = ? AND table1.id = t2.table1_id)`, productValue).
Find(&t1).Error

Note that AND table1.id = t2.table1_id part is just an example how the two tables might be related, you may have a different relation and you'll need to modify the query accordingly.

If you want GORM to populate the t1.Items with the Table2 data, you prepend Preload("Items") to the above query.

Upvotes: 1

Related Questions