dpbz14739 2019-02-27 09:38
浏览 847
已采纳

使用gorm golang在多个表中进行动态列搜索

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.

User Screen

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!

  • 写回答

1条回答 默认 最新

  • doumeng4400 2019-02-27 18:17
    关注

    Even though you are preloading, you are still required to explicitly use joins when filtering and ordering on columns on other tables. Preloading is used to eagerly load the data to map into your models, not to join tables.

    Chain on something like this:

    .Joins("LEFT JOIN rfm.robot_models AS robot_models ON robot_models.id = robots.robot_model_id")
    

    I'm not positive if you can use the AS keyword using this technique, but if not, it should be easy enough to adjust your query accordingly.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 apk可以在模拟器上正常安装使用,但是手机上无法正常安装
  • ¥15 office打开卡退(新电脑重装office系统后)
  • ¥300 FLUENT 火箭发动机燃烧EDC仿真
  • ¥15 【Hadoop 问题】Hadoop编译所遇问题hadoop-common: make failed with error code 2
  • ¥15 vb6.0+webbrowser无法加载某个网页求解
  • ¥15 RPA财务机器人采购付款流程
  • ¥15 计算机图形多边形及三次样条曲线绘制
  • ¥15 根据protues画的图用keil写程序
  • ¥200 如何使用postGis实现最短领规划?
  • ¥15 pyinstaller打包错误