Golang, PostgreSQL and array_agg

• 02, 01, 2019
•  bullgare
•  Databases, golang
• No Comments.

If you have SQL like this:

SELECT c.id,
	c.description,
	c.status,
	array_agg(u.id ORDER BY u.id)::BIGINT[] AS user_ids,
	array_agg(u.type_id ORDER BY u.id)::BIGINT[] AS user_type_ids,
	array_agg(u.name ORDER BY u.id)::TEXT[] AS user_names
FROM chat_users AS cu
INNER JOIN (
	SELECT
		cu.chat_id
	FROM chat_users AS cu
	WHERE cu.user_id = $1
	ORDER BY chat_id DESC
	LIMIT $2 OFFSET $3
) AS cu1
	ON cu.chat_id = cu1.chat_id
INNER JOIN users AS u
	ON u.id = cu.user_id
INNER JOIN chats AS c
	ON c.id = cu.chat_id
GROUP BY c.id
ORDER BY c.id DESC

Then you can query DB like this:

rows, err := master.Query(ctx, query, id, limit, offset)

And scan results like this (you should not omit errors in real code):

var chats []*model.Chat
defer rows.Close()
for rows.Next() {
	var (
		chat            model.Chat
		userIDs         []sql.NullInt64
		userTypes       []sql.NullInt64
		userExternalIDs []sql.NullString
	)

	_ = rows.Scan(&chat.ID, &chat.GUID, &chat.Meta, &chat.Status, pq.Array(&userIDs), pq.Array(&userTypes), pq.Array(&userExternalIDs))

	if len(userIDs) != len(userTypes) || len(userIDs) != len(userExternalIDs) {
		return errors.New("WAAT?!")
	}

	chat.Users = make([]*model.User, 0, len(userIDs))
	for i := range userIDs {
		var (
			tmp            interface{}
			ok             bool
			userID         int64
			userType       int64
			userExternalID string
		)

		tmp, _ = userIDs[i].Value()
		userID, _ = tmp.(int64)

		tmp, _ = userTypes[i].Value()
		userType, _ = tmp.(int64)

		tmp, _ = userExternalIDs[i].Value()
		userExternalID, _ = tmp.(string)

		user := &model.User{
			ID:             uint64(userID),
			TypeID:         uint64(userType),
			ExternalUserID: userExternalID,
		}
		chat.Users = append(chat.Users, user)
	}

	chats = append(chats, &chat)
}

Now you have a list of chats with all it’s users.

You can read more here — https://www.opsdash.com/blog/postgres-arrays-golang.html