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 errorlevel=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.