Paginator复杂的子查询分页构建

先来看需求：

需要设计一个列表，首先列出所有的人员，然后统计对应的演讲信息，并且要按照活动场次优先，活动场次相同的情况下，参与人数优先

我们来看下程序法要怎么写（提示这样写会导致相同的活动场次被覆盖）

Controller.go

	aid, ok := Input.PostInt64("aid", c)
	if !ok {
		return
	}
	datas := InstructorModel.Api_select_byStatus(aid, 1)
	for i, data := range datas {
		data["area_count"] = LectureModel.Api_count_iid_groupByLocation_byIid(data["id"])
		data["lecture_count"] = LectureModel.Api_count_iid_groupByIid_byIid(data["id"])
		data["sum_visitor"] = LectureModel.Api_sum_visitor_groupByIid_byIid(data["id"])
		datas[i] = data
	}
	arr := map[int64]interface{}{}
	for _, data := range datas {
		arr[data["sum_visitor"].(int64)] = data
	}
	arr2 := []interface{}{}
	for _, a := range arr {
		arr2 = append(arr2, a)
	}
	RET.Success(c, 0, datas, nil)

程序法写起来简单，但是数据量大的情况下或者需要做分页的情况下就抓瞎了，如果是数据量少的情况下可以使用这个方法，那么要使用数据库方法的情况下要怎么写呢？

先来看下原生语句

SELECT
	* 
FROM
	fra_instructor
//先连表查分区域的讲座信息
	LEFT JOIN (
	SELECT
		count(*) AS area_count,
		iid 
	FROM
		( SELECT * FROM `fra_lecture` WHERE is_del = 0 GROUP BY iid, province, city, district ) AS b 
	GROUP BY
		iid 
	) AS c ON c.iid = id
//再次连表查对应演讲者的受众信息
	LEFT JOIN ( SELECT SUM( visitor ) AS sum_visitor, count( 0 ) AS host_time, iid AS iid2 FROM fra_lecture WHERE is_del = 0 GROUP BY iid ) AS d ON d.iid2 = id 
WHERE
//加一个筛选条件
	aid = 1 
ORDER BY
//加一个排序条件
	host_time DESC,
	sum_visitor DESC 
//加一个限制
	LIMIT 10;

使用GorosePro的Query方法就能轻松的将语句变成查询

func Api_joinLecture_select_AreaCountandSumVisitorandHostTime_byAid(aid interface{}) {
	db := tuuz.Db()
	ret,err:=db.Query(`
	SELECT
		* 
	FROM
		fra_instructor
		LEFT JOIN (
		SELECT
			count(*) AS area_count,
			iid 
		FROM
			(
			SELECT
				* 
			FROM
				fra_lecture
			WHERE
				is_del = 0 
			GROUP BY
				iid,
				province,
				city,
				district 
			) AS b 
		GROUP BY
			iid 
		) AS c ON c.iid = id
		LEFT JOIN (
		SELECT
			SUM( visitor ) AS sum_visitor,
			count( 0 ) AS host_time,
			iid AS iid2 
		FROM
			fra_lecture 
		WHERE
			is_del = 0 
		GROUP BY
			iid 
		) AS d ON d.iid2 = id 
	WHERE
		aid = ?
	ORDER BY
		host_time DESC,
		sum_visitor DESC;
`, aid)
	if err != nil {
		Log.Dbrr(err, tuuz.FUNCTION_ALL())
		return nil
	} else {
		return ret
	}
}

这种复杂的SQL当然可以使用Query方法完成，但是未来的维护压力就大起来了，那么就必须将它变为GorosePro的构建语句，

func Api_joinLecture_select_AreaCountandSumVisitorandHostTime_byAid(aid interface{}, limit, page int) []gorose.Data {
	db := tuuz.Db()
	db.Table(Table)
	db.LeftJoin(`
		(SELECT
			count(*) AS area_count,
			iid 
		FROM
			( SELECT * FROM fra_lecture WHERE is_del = 0 GROUP BY iid, province, city, district ) AS b 
		GROUP BY
			iid ) as c
`, "c.iid=id")
	db.LeftJoin("(SELECT SUM( visitor ) AS sum_visitor, count( 0 ) AS host_time, iid AS iid2 FROM fra_lecture WHERE is_del = 0 GROUP BY iid ) AS d", "d.iid2 = id ")
//使用了构件法，未来如果需要改变参数只需要多加一个where即可，非常简单
	db.Where("aid", aid)
	db.Limit(limit)
	db.Page(page)
	db.OrderBy("host_time desc,sum_visitor desc")
	ret, err := db.Get()
	if err != nil {
		Log.Dbrr(err, tuuz.FUNCTION_ALL())
		return nil
	} else {
		return ret
	}
}

看明白了吗？就是这么简单!

你甚至可以使用paginator方法来获得更加方便的分页体验！

func Api_joinLecture_select_paginate_AreaCountandSumVisitorandHostTime_byAid(aid interface{}, limit, page int) gorose.Paginate {
	db := tuuz.Db()
	db.Table(Table)
	db.LeftJoin(`
		(SELECT
			count(*) AS area_count,
			iid 
		FROM
			( SELECT * FROM fra_lecture WHERE is_del = 0 GROUP BY iid, province, city, district ) AS b 
		GROUP BY
			iid ) as c
`, "c.iid=id")
	db.LeftJoin("(SELECT SUM( visitor ) AS sum_visitor, count( 0 ) AS host_time, iid AS iid2 FROM fra_lecture WHERE is_del = 0 GROUP BY iid ) AS d", "d.iid2 = id ")
	db.Where("aid", aid)
	db.Limit(limit)
	db.Page(page)
	db.OrderBy("host_time desc,sum_visitor desc")
//使用paginator方法，让分页更加简单，方便前后端
	ret, err := db.Paginator()
	if err != nil {
		Log.Dbrr(err, tuuz.FUNCTION_ALL())
		return gorose.Paginate{}
	} else {
		return ret
	}
}