My scenario is i have a grid with search option where user can select the column and can do the search, the grid data is coming from various tables. I have attached a sample screen of grid.
So i'm trying to create a dynamic query for search but the problem is i can able to search only in main table (schema.Robot) not in Preload tables. whenever i trying to search data data from Preload tables let say from RobotModel table that time getting below error
pq: missing FROM-clause entry for table "robot_models"
Here is my go code
func (r *RobotsRepository) GetRobotsSummary(listParams viewmodel.ListParams, companyID uint) ([]*schema.Robot, int, error) {
mrobots := []*schema.Robot{}
var count int
var order string
if listParams.SortColumn == "" {
listParams.SortColumn = "id"
listParams.SortOrder = 1
} else {
listParams.SortColumn = util.Underscore(listParams.SortColumn)
}
if listParams.SortOrder == 0 {
order = "ASC"
} else {
order = "DESC"
}
var searchQuery string
if listParams.SearchText != "" {
switch listParams.SearchColumn {
case "Robot":
listParams.SearchColumn = "name"
case "Model":
listParams.SearchColumn = "robot_models.name"
}
searchQuery = listParams.SearchColumn +" LIKE '%"+ listParams.SearchText +"%' and Company_ID = " + fmt.Sprint(companyID)
}else{
searchQuery = "Company_ID = " + fmt.Sprint(companyID)
}
orderBy := fmt.Sprintf("%s %s", listParams.SortColumn, order)
err := r.Conn.
Preload("RobotModel", func(db *gorm.DB) *gorm.DB {
return db.Select("ID,Name")
}).
Preload("Task", func(db *gorm.DB) *gorm.DB {
return db.Where("Task_Status in ('In-Progress','Pending')").Select("ID, Task_Status")
}).
Preload("CreatedUser", func(db *gorm.DB) *gorm.DB {
return db.Select("ID,Display_Name")
}).
Preload("UpdatedUser", func(db *gorm.DB) *gorm.DB {
return db.Select("ID,Display_Name")
}).
Where(searchQuery).
Order(orderBy).
Offset(listParams.PageSize * (listParams.PageNo - 1)).
Limit(listParams.PageSize).
Find(&mrobots).Error
r.Conn.Model(&schema.Robot{}).Where(searchQuery).Count(&count)
return mrobots, count, err
}
In searchQuery variable i'm storing my dynamic query. My question is how can i search data for preload table columns
Here is the sql query which i'm trying to achieve using gorm
SELECT robots.id,robots.name,robot_models.name as
model_name,count(tasks.task_status) as task_on_hand,
robots.updated_at,users.user_name as updated_by
FROM rfm.robots as robots
left join rfm.tasks as tasks on tasks.robot_id = robots.id and
tasks.task_status in ('In-Progress','Pending')
left join rfm.robot_models as robot_models on robot_models.id =
robots.robot_model_id
left join rfm.users as users on users.id = robots.updated_by
WHERE robot_models.name::varchar like '%RNR%' and robots.deleted_at is null
GROUP BY robots.id,robot_models.name,users.user_name
ORDER BY task_on_hand DESC LIMIT 2 OFFSET 0
and sorry for bad English!