回答編集履歴

1

querybuilderを使うことに

2017/07/03 03:20

投稿

m0a
m0a

スコア708

test CHANGED
@@ -22,4 +22,88 @@
22
22
 
23
23
 
24
24
 
25
- ただ方法として正しいのかわからないので引き続きよろくお願いします。
25
+ 実際にはqueryBuilder(squirrel)を使うのが一番のかなとわかりま
26
+
27
+
28
+
29
+ ```go
30
+
31
+ modifiedBefore := time.Now()
32
+
33
+ tags := []string{"go", "rust", "typescript"}
34
+
35
+ url := "//"
36
+
37
+ n := 100
38
+
39
+ tagsStmt := sq.Expr("CONCAT(DISTINCT tags.name SEPARATOR ',')")
40
+
41
+ q := sq.Select("id",
42
+
43
+ "account_id",
44
+
45
+ "pairs",
46
+
47
+ "complete_rate",
48
+
49
+ "point",
50
+
51
+ "title",
52
+
53
+ "description",
54
+
55
+ "created",
56
+
57
+ "modified").
58
+
59
+ Column("CONCAT(DISTINCT urls.url SEPARATOR ',') as urls").
60
+
61
+ // Column("(DISTINCT tags.name SEPARATOR ',') as tags").
62
+
63
+ Column(sq.Alias(tagsStmt, "tags")).
64
+
65
+ From("contents").
66
+
67
+ LeftJoin("contents_urls ON contents_urls.content_id = contents.id").
68
+
69
+ LeftJoin("contents_tags ON contents_tags.content_id = contents.id").
70
+
71
+ LeftJoin("tags ON tags.id = contents_tags.tag_id").
72
+
73
+ Where(sq.Lt{"modified": modifiedBefore}).
74
+
75
+ Where(sq.Eq{"tags.name": tags}).
76
+
77
+ Where("urls.url like ?", fmt.Sprint("%", url, "%")).
78
+
79
+ GroupBy("contents.id").
80
+
81
+ Limit(uint64(n))
82
+
83
+
84
+
85
+ fmt.Println(q.ToSql())
86
+
87
+ ```
88
+
89
+
90
+
91
+ 実行結果
92
+
93
+
94
+
95
+ ```
96
+
97
+ SELECT id, account_id, pairs, complete_rate, point, title, description, created, modified, CONCAT(DISTINCT urls.url SEPARATOR ',') as urls, (CONCAT(DISTINCT tags.name SEPARATOR ',')) AS tags FROM contents LEFT JOIN contents_urls ON contents_urls.content_id = contents.id LEFT JOIN contents_tags ON contents_tags.content_id = contents.id LEFT JOIN tags ON tags.id = contents_tags.tag_id WHERE modified < ? AND tags.name IN (?,?,?) AND urls.url like ? GROUP BY contents.id LIMIT 100
98
+
99
+
100
+
101
+ [go rust typescript %//%] <nil>
102
+
103
+ ```
104
+
105
+
106
+
107
+ プレースホルダと引数の調整を自動化しますし。
108
+
109
+