LeftJoin/RightJoin/InnerJoin

  1. LeftJoin 左关联查询；
  2. RightJoin 右关联查询；
  3. InnerJoin 内关联查询；

其实我们并不推荐使用Join进行联表查询，特别是在数据量比较大、并发请求量比较高的场景中，容易产生性能问题，也容易提高维护的复杂度。建议您在确定有此必要的场景下使用。此外，您也可以参考 ORM链式操作-模型关联 章节，数据库只负责存储数据和简单的单表操作，通过ORM提供的功能在代码层面实现数据聚合。

使用示例：

  1. // 查询符合条件的单条记录(第一条)
  2. // SELECT u.*,ud.site FROM user u LEFT JOIN user_detail ud ON u.uid=ud.uid WHERE u.uid=1 LIMIT 1
  3. g.Model("user u").LeftJoin("user_detail ud", "u.uid=ud.uid").Fields("u.*,ud.site").Where("u.uid", 1).One()
  5. // 查询指定字段值
  6. // SELECT ud.site FROM user u RIGHT JOIN user_detail ud ON u.uid=ud.uid WHERE u.uid=1 LIMIT 1
  7. g.Model("user u").RightJoin("user_detail ud", "u.uid=ud.uid").Fields("ud.site").Where("u.uid", 1).Value()
  9. // 分组及排序
  10. // SELECT u.*,ud.city FROM user u INNER JOIN user_detail ud ON u.uid=ud.uid GROUP BY city ORDER BY register_time asc
  11. g.Model("user u").InnerJoin("user_detail ud", "u.uid=ud.uid").Fields("u.*,ud.city").Group("city").Order("register_time asc").All()
  13. // 不使用join的联表查询
  14. // SELECT u.*,ud.city FROM user u,user_detail ud WHERE u.uid=ud.uid
  15. g.Model("user u,user_detail ud").Where("u.uid=ud.uid").Fields("u.*,ud.city").All()

自定义数据表别名

  1. // SELECT * FROM `user` AS u LEFT JOIN `user_detail` as ud ON(ud.id=u.id) WHERE u.id=1 LIMIT 1
  2. g.Model("user", "u").LeftJoin("user_detail", "ud", "ud.id=u.id").Where("u.id", 1).One()
  3. g.Model("user").As("u").LeftJoin("user_detail", "ud", "ud.id=u.id").Where("u.id", 1).One()

LeftJoinOnFields/RightJoinOnFields/InnerJoinOnFields

LeftJoinOnFields/RightJoinOnFields/InnerJoinOnFields 这三个方法可以指定字段和操作符进行 join 查询，使用示例：

  1. // 查询符合条件的单条记录(第一条)
  2. // SELECT user.*,user_detail.address FROM user LEFT JOIN user_detail ON (user.id = user_detail.uid) WHERE user.id=1 LIMIT 1
  3. g.Model("user").LeftJoinOnFields("user_detail", "id", "=", "uid").Fields("user.*,user_detail.address").Where("id", 1).One()
  5. // 查询多条记录
  6. // SELECT user.*,user_detail.address FROM user RIGHT JOIN user_detail ON (user.id = user_detail.uid)
  7. g.Model("user").RightJoinOnFields("user_detail", "id", "=", "uid").Fields("user.*,user_detail.address").All()

结合dao使用示例

  1. // SELECT resource_task_schedule.id,...,time_window.time_window 
  2. // FROM `resource_task_schedule` 
  3. // LEFT JOIN `time_window` ON (`resource_task_schedule`.`resource_id`=`time_window`.`resource_id`) 
  4. // WHERE (time_window.`status`="valid") AND (`time_window`.`start_time` <= 3600)
  5. var (
  6.     orm                = dao.ResourceTaskSchedule.Ctx(ctx)
  7.     tsTable            = dao.ResourceTaskSchedule.Table()
  8.     tsCls              = dao.ResourceTaskSchedule.Columns()
  9.     twTable            = dao.TimeWindow.Table()
  10.     twCls              = dao.TimeWindow.Columns()
  11.     scheduleItems      []scheduleItem
  12. )
  13. orm = orm.FieldsPrefix(tsTable, tsCls)
  14. orm = orm.FieldsPrefix(twTable, twCls.TimeWindow)
  15. orm = orm.LeftJoinOnField(twTable, twCls.ResourceId)
  16. orm = orm.WherePrefix(twTable, twCls.Status, "valid")
  17. orm = orm.WherePrefixLTE(twTable, twCls.StartTime, 3600)
  18. err = orm.Scan(&scheduleItems)
  1. // SELECT DISTINCT resource_info.* FROM `resource_info` 
  2. // LEFT JOIN `resource_network` ON (`resource_info`.`resource_id`=`resource_network`.`resource_id`) 
  3. // WHERE (`resource_info`.`resource_id` like '%10.0.1.3%') 
  4. // or (`resource_info`.`resource_name` like '%10.0.1.3%') 
  5. // or (`resource_network`.`vip`like '%10.0.1.3%')  
  6. // ORDER BY `id` Desc LIMIT 0,2
  7. var (
  8.     orm    = dao.ResourceInfo.Ctx(ctx).OmitEmpty()
  9.     rTable = dao.ResourceInfo.Table()
  10.     rCls   = dao.ResourceInfo.Columns()
  11.     nTable = dao.ResourceNetwork.Table()
  12.     nCls   = dao.ResourceNetwork.Columns()
  13. )
  14. orm = orm.LeftJoinOnField(nTable, rCls.ResourceId)
  15. orm = orm.WherePrefix(rTable, do.ResourceInfo{
  16.     AppId:        req.AppIds,
  17.     ResourceId:   req.ResourceIds,
  18.     Region:       req.Regions,
  19.     Zone:         req.Zones,
  20.     ResourceName: req.ResourceNames,
  21.     Status:       req.Statuses,
  22.     BusinessType: req.Products,
  23.     Engine:       req.Engines,
  24.     Version:      req.Versions,
  25. })
  26. orm = orm.WherePrefix(nTable, do.ResourceNetwork{
  27.     Vip:      req.Vips,
  28.     VpcId:    req.VpcIds,
  29.     SubnetId: req.SubnetIds,
  30. })
  31. // Fuzzy like querying.
  32. if req.Key != "" {
  33.     var (
  34.         keyLike = "%" + req.Key + "%"
  35.     )
  36.     whereFormat := fmt.Sprintf(
  37.         "(`%s`.`%s` like ?) or (`%s`.`%s` like ?) or (`%s`.`%s`like ?) ",
  38.         rTable, rCls.ResourceId,
  39.         rTable, rCls.ResourceName,
  40.         nTable, nCls.Vip,
  41.     )
  42.     orm = orm.Where(whereFormat, keyLike, keyLike, keyLike)
  43. }
  44. // Resource items.
  45. err = orm.Distinct().FieldsPrefix(rTable, "*").Order(req.Order, req.OrderDirection).Limit(req.Offset, req.Limit).Scan(&res.Items)