How to build this query?

[kolkov]

Hi! Is it possible to build?

SELECT COUNT(*) FROM (SELECT COUNT(contractor.id) 
FROM contractor 
LEFT JOIN contract ON contractor.id=contract.contractor_id
WHERE contract.type='доход'
GROUP BY contractor.id) as myquery

[kolkov]

I tryed to build with whis hack, but got a error level=error msg="Named parameter not found: p0" UserId=1 if filter:

if filter.Type == "client" {
		q.AndWhere(dbx.HashExp{"contract.type": "доход"})
		sorting = "asc"
	}
	if filter.Type == "carrier" {
		q.AndWhere(dbx.HashExp{"contract.type": "расход"})
		sorting = "asc"
	}

If filter emty this workes.

func (dao *ContractorDAO) Count(rs app.RequestScope, filter models.ContractorFilter) (int, error) {
	var count int
	q0 := rs.Tx().Select("COUNT(*)").From(dbx.GetTableName(models.Contractor{})).
		LeftJoin("contract", dbx.NewExp("contractor.id=contract.contractor_id")).
		GroupBy("contractor.id")
	q0, _ = dao.getFilter(q0, filter, "")
	sql := q0.Build().SQL()
	q := rs.Tx().Select("COUNT(*)").From("(" + sql + ") as myQuery")
	err := q.Row(&count)
	return count, err
}

[kolkov]

This worked, but very difficult...

func (dao *ContractorDAO) Count(rs app.RequestScope, filter models.ContractorFilter) (int, error) {
	var count int

	q0 := rs.Tx().Select("COUNT(*)").From(dbx.GetTableName(models.Contractor{})).
		LeftJoin("contract", dbx.NewExp("contractor.id=contract.contractor_id")).
		GroupBy("contractor.id")

	q0, _ = dao.getFilter(q0, filter, "")

	query := q0.Build()

	qPrepared := query.Prepare()

	sql := qPrepared.SQL()

	q := rs.Tx().Select("COUNT(*)").From("(" + sql + ") as myQuery").Bind(query.Params())

	err := q.Row(&count)

	return count, err
}

[qiangxue]

For queries like this it's better to write plain SQL. Query builder is meant to simplify creating common queries.

[kolkov]

I think we can add another one method, like SelectFrom(query *dbx.Query) or string.