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
|
import json
from typing import Collection, List, Optional, Tuple
from generalresearch.models.pollfish.question import PollfishQuestion
from generalresearch.sql_helper import SqlHelper
def get_profiling_library(
sql_helper: SqlHelper,
country_iso: Optional[str] = None,
language_iso: Optional[str] = None,
question_ids: Optional[Collection[str]] = None,
max_options: Optional[int] = None,
is_live: Optional[bool] = None,
pks: Optional[Collection[Tuple[str, str, str]]] = None,
) -> List[PollfishQuestion]:
"""
Accepts lots of optional filters.
:param country_iso: filters on country_iso field
:param language_iso: filters on language_iso field
:param question_ids: filters on question_id field, accepts multiple values
:param max_options: filters on max_options field
:param is_live: filters on is_live field
:param pks: The pk is (question_id, country_iso, language_iso). pks accepts a collection of
len(3) tuples. e.g. [('123', 'us', 'eng'), ('123', 'us', 'spa')]
:return:
"""
filters = []
params = {}
if country_iso:
params["country_iso"] = country_iso
filters.append("`country_iso` = %(country_iso)s")
if language_iso:
params["language_iso"] = language_iso
filters.append("`language_iso` = %(language_iso)s")
if question_ids:
params["question_ids"] = question_ids
filters.append("question_id IN %(question_ids)s")
if max_options is not None:
params["max_options"] = max_options
filters.append("COALESCE(num_options, 0) <= %(max_options)s")
if is_live is not None:
params["is_live"] = is_live
filters.append("is_live = %(is_live)s")
if pks:
params["pks"] = pks
filters.append("(question_id, country_iso, language_iso) IN %(pks)s")
filter_str = " AND ".join(filters)
filter_str = "WHERE " + filter_str if filter_str else ""
res = sql_helper.execute_sql_query(
f"""
SELECT *
FROM `thl-pollfish`.`pollfish_question` q
{filter_str}
""",
params,
)
for x in res:
x["options"] = json.loads(x["options"]) if x["options"] else None
qs = [PollfishQuestion.from_db(x) for x in res]
return qs
|
