How to SELECT DISTINCT on a single column?

[ninjamast3r]

I'm trying to create the following query in using goqu with the postgres dialect:

SELECT DISTINCT on (gates.id) "gates".*, array(SELECT "tag" FROM "gate_tags" WHERE ("gate_tags"."gates_id" = "gates"."id")) AS "tags"
FROM "gates" INNER JOIN "gate_tags" ON ("gate_tags"."gates_id" = "gates"."id")
WHERE (("gate_tags"."tag" IN ('build', 'test')) AND (("encoding" = 'UTF-8') AND ("maintainer" = 'bob')));

I can get pretty close, but I can't figure out how to get the SELECT DISTINCT on (gates.id) part to parse properly. This is what I've got so far:

var expressions []exp.Expression
whereConditions := goqu.Ex{}
tagTable := goqu.T("gate_tags")
gateTable := goqu.T("gates")

if gt.Maintainer != "" {
    whereConditions["maintainer"] = gt.Maintainer
}
if len(gt.Tags) > 0 {
    expressions = append(expressions, goqu.And(tagTable.Col("tag").In(gt.Tags)))
}
if gt.Encoding != "" {
    whereConditions["encoding"] = gt.Encoding
}
expressions = append(expressions, whereConditions)

//goqu doesn't appear to support the postgres array operation, so we have to wrap our query inside of it.
tagSql, _, err := goqu.From(tagTable).Where(
    goqu.Ex{
        "gate_tags.gates_id": goqu.I("gates.id"),
    }).Select("tag").ToSQL()
if err != nil {
    return nil, err
}
arrayQuery := fmt.Sprintf("array(%s)", tagSql)

sql, _, err := goqu.From("gates").Join(
    tagTable,
    goqu.On(goqu.Ex{
        "gate_tags.gates_id": goqu.I("gates.id"),
    }),
).Select(
    goqu.DISTINCT(gateTable.Col("id")),
    gateTable.Col(goqu.Star()),
    goqu.L(arrayQuery).As("tags"),
).Where(
    expressions...,
).ToSQL()
if err != nil {
    return nil, err
}

fmt.Println(sql)
// outputs:
// SELECT DISTINCT("gates"."id"), "gates".*, array(SELECT "tag" FROM "gate_tags" WHERE ("gate_tags"."gates_id" = "gates"."id")) AS "tags" FROM "gates" INNER JOIN "gate_tags" ON ("gate_tags"."gates_id" = "gates"."id") WHERE (("gate_tags"."tag" IN ('build', 'test')) AND (("encoding" = 'UTF-8') AND ("maintainer" = 'bob')))

How, if possible, would I use goqu to formulate SELECT DISTINCT on (gates.id)? I'd be happy to contribute this use case to the otherwise excellent docs.

[doug-martin]

There isnt currently a way to do this, with the SelectDistinct method but you could probably do something like...

// by using the literal you can add what ever you want to the beginning of the select.
ds.Select(goqu.L("DISTINCT on (?)", gateTable.Col("id")), gateTable.Col(goqu.Star()))

I havent tried this yet, so it may not work first try but should be close.

[ninjamast3r]

Thanks. I tried several variations of what you suggested, but there's an extra , at the end of ("gates"."id"), in SELECT DISTINCT on ("gates"."id"), "gates".*, ....
I was able to get around the problem by doing this instead:

Select(
    goqu.L(`DISTINCT on (gates.id) "gates".*`),
    goqu.L(arrayQuery).As("tags"),
)

[doug-martin]

I added a new Distinct method to allow creating select statements with a distinct on clause for dialects that support it.

You example with the new distinct method would like like

// this could be pulled out to a helper
array := func(elements ...interface{}) exp.SQLFunctionExpression {
	return goqu.Func("array", elements...)
}

tags := goqu.From(goqu.T("gate_tags")).Where(goqu.Ex{
	"gate_tags.gates_id": gateTable.Col("id"),
}).Select("tag")

sql, _, _ := goqu.From("test").
	Select(gateTable.All(), array(tags).As("tags")).
	Distinct(gateTable.Col("id")).
	ToSQL()
fmt.Println(sql)

Output:

SELECT DISTINCT ON ("gates"."id") "gates".*, array((SELECT "tag" FROM "gate_tags" WHERE ("gate_tags"."gates_id" = "gates"."id"))) AS "tags" FROM "test"