Golang, PostgreSQL and array_agg

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

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

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

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

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)
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

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)

}

Golang, PostgreSQL and array_agg

Similar Posts

LEAVE A COMMENT Отменить ответ