/
retrieve_list_suggestions.go
127 lines (115 loc) · 2.36 KB
/
retrieve_list_suggestions.go
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
package character
import (
"errors"
"strconv"
"github.com/jmoiron/sqlx"
"github.com/kaikourok/lunchtote-backend/entity/model"
)
func (db *CharacterRepository) RetrieveListSuggestions(characterId int, searchText string, listId int) (suggestions *model.CharacterSuggestionsData, err error) {
row := db.QueryRowx(`
SELECT
master
FROM
lists
WHERE
id = $1;
`, listId)
var listMaster int
err = row.Scan(&listMaster)
if err != nil {
return nil, err
}
if characterId != listMaster {
err := errors.New("リストの管理者ではありません")
return nil, err
}
number, err := strconv.Atoi(searchText)
var rows *sqlx.Rows
if err != nil {
rows, err = db.Queryx(`
WITH dismiss_list AS (
SELECT
blocker AS dismiss
FROM
blocks
WHERE
blocked = $1
UNION ALL
SELECT
blocked AS dismiss
FROM
blocks
WHERE
blocker = $1
)
SELECT
id,
name
FROM
characters
WHERE
(
name LIKE likequery($2) OR
nickname LIKE likequery($2)
) AND
NOT EXISTS (SELECT * FROM lists_characters WHERE list = $3 AND character = characters.id) AND
deleted_at IS NULL AND
administrator = false AND
NOT EXISTS (SELECT * FROM dismiss_list WHERE id = dismiss)
LIMIT
20;
`, characterId, searchText, listId)
} else {
rows, err = db.Queryx(`
WITH dismiss_list AS (
SELECT
blocker AS dismiss
FROM
blocks
WHERE
blocked = $1
UNION ALL
SELECT
blocked AS dismiss
FROM
blocks
WHERE
blocker = $1
)
SELECT
id,
name
FROM
characters
WHERE
(
id = $2 OR
name LIKE likequery($3) OR
nickname LIKE likequery($3)
) AND
NOT EXISTS (SELECT * FROM lists_characters WHERE list = $4 AND character = characters.id) AND
deleted_at IS NULL AND
administrator = false AND
NOT EXISTS (SELECT * FROM dismiss_list WHERE id = dismiss)
LIMIT
20;
`, characterId, number, searchText, listId)
}
if err != nil {
return nil, err
}
defer rows.Close()
suggestionsData := make(model.CharacterSuggestionsData, 0, 20)
for rows.Next() {
var character model.CharacterSuggestionData
err = rows.Scan(
&character.Id,
&character.Name,
)
if err != nil {
return nil, err
}
suggestionsData = append(suggestionsData, character)
}
return &suggestionsData, nil
}