GoFrame ORM component supports Union/UnionAll operations, where the Union/UnionAll operator is used to combine the results of two or more SELECT statements into a single result set. For more information on Union/UnionAll composite queries, refer to the MySQL official documentation https://dev.mysql.com/doc/refman/8.0/en/union.html. We can achieve Union/UnionAll operations through chaining operations or method operations.

Method Definition

  1. // Union does "(SELECT xxx FROM xxx) UNION (SELECT xxx FROM xxx) ..." statement.
  2. func (c *Core) Union(unions ...*Model) *Model
  4. // UnionAll does "(SELECT xxx FROM xxx) UNION ALL (SELECT xxx FROM xxx) ..." statement.
  5. func (c *Core) UnionAll(unions ...*Model) *Model

Union

Using the Union operator, multiple SELECT statements will remove duplicate data.

  1. // Get the default configured database object (configuration name is "default")
  2. db := g.DB()
  4. db.Union(
  5.     db.Model("user").Where("id", 1),
  6.     db.Model("user").Where("id", 2),
  7.     db.Model("user").WhereIn("id", g.Slice{1, 2, 3}),
  8. ).OrderDesc("id").All()
  9. // (SELECT * FROM `user` WHERE `id`=1)
  10. // UNION
  11. // (SELECT * FROM `user` WHERE `id`=2)
  12. // UNION
  13. // (SELECT * FROM `user` WHERE `id` IN (1,2,3)
  14. // ORDER BY `id` DESC) ORDER BY `id` DESC

It can also be implemented through dao chaining operations:

  1. dao.User.Union(
  2.     dao.User.Where(dao.User.Columns.Id, 1),
  3.     dao.User.Where(dao.User.Columns.Id, 2),
  4.     dao.User.WhereIn(dao.User.Columns.Id, g.Slice{1, 2, 3}),
  5. ).OrderDesc(dao.User.Columns.Id).All()
  6. // (SELECT * FROM `user` WHERE `id`=1)
  7. // UNION
  8. // (SELECT * FROM `user` WHERE `id`=2)
  9. // UNION
  10. // (SELECT * FROM `user` WHERE `id` IN (1,2,3)
  11. // ORDER BY `id` DESC) ORDER BY `id` DESC

UnionAll

Using the UnionAll operator, multiple SELECT statements will not remove duplicate data.

  1. db.UnionAll(
  2.     db.Model("user").Where("id", 1),
  3.     db.Model("user").Where("id", 2),
  4.     db.Model(table).WhereIn("id", g.Slice{1, 2, 3}),
  5. ).OrderDesc("id").All()
  6. // (SELECT * FROM `user` WHERE `id`=1)
  7. // UNION ALL
  8. // (SELECT * FROM `user` WHERE `id`=2)
  9. // UNION ALL
  10. // (SELECT * FROM `user` WHERE `id` IN (1,2,3)
  11. // ORDER BY `id` DESC) ORDER BY `id` DESC

It can also be implemented through dao chaining operations:

  1. dao.User.UnionAll(
  2.     dao.User.Where(dao.User.Columns.Id, 1),
  3.     dao.User.Where(dao.User.Columns.Id, 2),
  4.     dao.User.WhereIn(dao.User.Columns.Id, g.Slice{1, 2, 3}),
  5. ).OrderDesc(dao.User.Columns.Id).All()
  6. // (SELECT * FROM `user` WHERE `id`=1)
  7. // UNION ALL
  8. // (SELECT * FROM `user` WHERE `id`=2)
  9. // UNION ALL
  10. // (SELECT * FROM `user` WHERE `id` IN (1,2,3)
  11. // ORDER BY `id` DESC) ORDER BY `id` DESC