def test_count_id(self):
query = Query().from_table(table='test_table',
fields=[CountField('id')])
query_str = query.get_sql()
expected_query = 'SELECT COUNT(test_table.id) AS "id_count" FROM test_table'
self.assertEqual(query_str, expected_query,
get_comparison_str(query_str, expected_query))
def test_count_distinct(self):
query = Query().from_table(table='test_table',
fields=[CountField('name', distinct=True)])
query_str = query.get_sql()
expected_query = 'SELECT COUNT(DISTINCT test_table.name) AS "name_count" FROM test_table'
self.assertEqual(query_str, expected_query,
get_comparison_str(query_str, expected_query))
def test_cast(self):
query = Query().from_table(
table=Account,
fields=[
CountField(
'id',
alias='count',
cast='float'
)
]
)
query_str = query.get_sql()
expected_query = (
'SELECT CAST(COUNT(querybuilder_tests_account.id) AS FLOAT) AS "count" FROM querybuilder_tests_account'
)
self.assertEqual(query_str, expected_query, get_comparison_str(query_str, expected_query))
received = query.select()[0]['count']
expected = float(len(User.objects.all()))
self.assertEqual(
received,
expected,
'Expected {0} but received {1}'.format(
expected,
received
)
)
def test_group_by_id(self):
query = Query().from_table(table='test_table',
fields=[{
'num': CountField('id')
}]).group_by(field='id')
query_str = query.get_sql()
expected_query = 'SELECT COUNT(test_table.id) AS "num" FROM test_table GROUP BY id'
self.assertEqual(query_str, expected_query,
get_comparison_str(query_str, expected_query))
def contact_count(contact_type):
return (Query().from_table(
{
"cc_family": CustomerContact
},
fields=["customer_id", {
"contact_count": CountField("id")
}],
).where(
**{
"is_basic": False,
"attributes->>'contact_type'": contact_type
}).group_by("customer_id"))
def get_list(
page_num: int = 1,
per_page: int = 10,
pre_per_page: Union[int, None] = None,
order_by: Union[Iterator, None] = None,
filters: Union[Iterator, None] = None,
for_csv: bool = False,
):
if per_page is None:
offset = None
else:
offset = (pre_per_page or per_page) * (page_num - 1)
if not order_by:
order_by = []
fields = [
"id",
"internal_id",
"business_id",
{
"account_designator": RawSQLField("banking->>'account_designator'")
},
{
"bank_name": RawSQLField("banking->>'bank_name'")
},
{
"bank_branch_name": RawSQLField("banking->>'branch_name'")
},
{
"bank_account_type": RawSQLField("banking->>'account_type'")
},
{
"bank_account_number": RawSQLField("banking->>'account_number'")
},
{
"bank_account_name": RawSQLField("banking->>'account_name'")
},
"tags",
{
"tags_repr":
RawSQLField("""
(select string_agg(tags_repr, ',') tags_repr
from (
select concat_ws(':', key, value) as tags_repr
from jsonb_each_text(tags) as x
where value is not null
) as ss)::text
""")
},
]
query = Query().from_table({"c": Customer}, fields=fields)
if for_csv:
def contact_count(contact_type):
return (Query().from_table(
{
"cc_family": CustomerContact
},
fields=["customer_id", {
"contact_count": CountField("id")
}],
).where(
**{
"is_basic": False,
"attributes->>'contact_type'": contact_type
}).group_by("customer_id"))
family_contact_count = contact_count(ContactType.family)
other_contact_count = contact_count(ContactType.others)
contact_fc = (Query().from_table(
{
"contact_fc": ForestCustomer
},
fields=["customer_id", {
"forest_count": CountField("id")
}],
).group_by("customer_id"))
customers_forests = (Query(
).from_table({
"fc": ForestCustomer
}, fields=["customer_id"]).join(
{
"forest": Forest
},
condition="fc.forest_id = forest.id",
fields=[{
"forests_json":
RawSQLField("json_agg(json_build_object("
"'id', forest.id, "
"'cadastral', forest.cadastral, "
"'land_attributes', forest.land_attributes))")
}],
).join(
{
"fcc": ForestCustomerContact
},
condition="fc.id = fcc.forestcustomer_id",
join_type="LEFT JOIN",
).join(
{
"cc": CustomerContact
},
condition="cc.id = fcc.customercontact_id and cc.is_basic = false",
join_type="LEFT JOIN",
).join(
{
"contact_customer": CustomerContact
},
condition="cc.contact_id = contact_customer.contact_id "
"and contact_customer.is_basic = true",
join_type="LEFT JOIN",
).join(
"contact_fc",
condition="contact_customer.customer_id = contact_fc.customer_id",
join_type="LEFT JOIN",
).join(
{
"contact": Contact
},
condition="cc.contact_id = contact.id",
join_type="LEFT JOIN",
fields=[{
"contacts_json":
RawSQLField("json_agg(json_build_object("
"'id', contact.id, "
"'name_kanji', contact.name_kanji, "
"'mobilephone', contact.mobilephone, "
"'telephone', contact.telephone, "
"'email', contact.email, "
"'forest_count', contact_fc.forest_count))"),
}],
).group_by("fc.customer_id"))
query = (query.join(
"customers_forests",
condition="customers_forests.customer_id = c.id",
join_type="LEFT JOIN",
fields=["forests_json", "contacts_json"],
).join(
"family_contact_count",
condition="family_contact_count.customer_id = c.id",
join_type="LEFT JOIN",
fields=[{
"family_contact_count": "contact_count"
}],
).join(
"other_contact_count",
condition="other_contact_count.customer_id = c.id",
join_type="LEFT JOIN",
fields=[{
"other_contact_count": "contact_count"
}],
))
query = query.join(
{
"self_contact_rel": CustomerContact
},
condition="c.id=self_contact_rel.customer_id "
"and self_contact_rel.is_basic is true",
).join(
{"self_contact": Contact},
condition="self_contact_rel.contact_id=self_contact.id",
fields=[
{
"fullname_kana":
RawSQLField(
"concat(self_contact.name_kana->>'last_name', '\u3000', "
"self_contact.name_kana->>'first_name')")
},
{
"fullname_kanji":
RawSQLField(
"concat(self_contact.name_kanji->>'last_name', '\u3000', "
"self_contact.name_kanji->>'first_name')")
},
"mobilephone",
"telephone",
"email",
"postal_code",
{
"sector": "address->>'sector'"
},
{
"prefecture": "address->>'prefecture'"
},
{
"municipality": "address->>'municipality'"
},
],
)
if for_csv:
query = (Query().with_query(query, alias="T0").with_query(
customers_forests, alias="customers_forests").with_query(
contact_fc, alias="contact_fc").with_query(
family_contact_count,
alias="family_contact_count").with_query(
other_contact_count,
alias="other_contact_count").from_table("T0"))
else:
forest_tags_nested = (Query().from_table(
{
"fc": ForestCustomer
}, fields=["customer_id"]).join(
{"forest": Forest},
condition="fc.forest_id = forest.id",
fields=[{
"forest_tags":
RawSQLField("(select array_agg(fulltag) tags_arr "
"from ( "
"select concat_ws(':', key, value) as fulltag "
"from jsonb_each_text(forest.tags) as x "
"where value is not null "
") as ss)")
}],
))
forest_tags_unnest = (Query().from_table(
{
"A0": forest_tags_nested
},
fields=[
"customer_id",
{
"forest_tags": RawSQLField("unnest(A0.forest_tags)")
},
],
).distinct())
forest_tags = (Query().from_table(
{
"A1": forest_tags_unnest
},
fields=[
"customer_id",
{
"forest_tags": RawSQLField("array_agg(A1.forest_tags)")
},
],
).group_by("customer_id"))
query = query.join(
"forest_tags",
condition="forest_tags.customer_id = c.id",
join_type="LEFT JOIN",
fields=[{
"forest_tags_repr":
RawSQLField("array_to_string(forest_tags, ',')")
}],
)
query = (Query().with_query(query, alias="T0").with_query(
forest_tags, alias="forest_tags").with_query(
forest_tags_unnest,
alias="A1").with_query(forest_tags_nested,
alias="A0").from_table("T0"))
if filters:
query.where(filters)
total = query.copy().count()
for order_field in order_by:
query.order_by(order_field)
query.limit(per_page, offset)
return query.select(), total
def query_data(cls, widget):
# get the columns needed
column = widget.column
group_by = widget.group_by if widget.group_by else None
if isinstance(widget.content_object, XForm):
xform = widget.content_object
elif isinstance(widget.content_object, DataView):
xform = widget.content_object.xform
field = get_field_from_field_xpath(column, xform)
if isinstance(field, basestring) and field == SUBMISSION_TIME:
field_label = 'Submission Time'
field_xpath = '_submission_time'
field_type = 'datetime'
data_type = DATA_TYPE_MAP.get(field_type, 'categorized')
else:
field_type = field.type
data_type = DATA_TYPE_MAP.get(field.type, 'categorized')
field_xpath = field.get_abbreviated_xpath()
field_label = get_field_label(field)
columns = [
SimpleField(field="json->>'%s'" % unicode(column),
alias='"{}"'.format(column)),
CountField(field="json->>'%s'" % unicode(column), alias='"count"')
]
if group_by:
if field_type in NUMERIC_LIST:
column_field = SimpleField(field="json->>'%s'" %
unicode(column),
cast="float",
alias=column)
else:
column_field = SimpleField(field="json->>'%s'" %
unicode(column),
alias=column)
# build inner query
inner_query_columns = \
[column_field,
SimpleField(field="json->>'%s'" % unicode(group_by),
alias=group_by),
SimpleField(field="xform_id"),
SimpleField(field="deleted_at")]
inner_query = Query().from_table(Instance, inner_query_columns)
# build group-by query
if field_type in NUMERIC_LIST:
columns = [
SimpleField(field=group_by, alias='"%s"' % group_by),
SumField(field=column, alias="sum"),
AvgField(field=column, alias="mean")
]
elif field_type == SELECT_ONE:
columns = [
SimpleField(field=column, alias='"%s"' % column),
SimpleField(field=group_by, alias='"%s"' % group_by),
CountField(field="*", alias='"count"')
]
query = Query().from_table({'inner_query': inner_query}, columns).\
where(xform_id=xform.pk, deleted_at=None)
if field_type == SELECT_ONE:
query.group_by(column).group_by(group_by)
else:
query.group_by(group_by)
else:
query = Query().from_table(Instance, columns).\
where(xform_id=xform.pk, deleted_at=None)
query.group_by("json->>'%s'" % unicode(column))
# run query
records = query.select()
# flatten multiple dict if select one with group by
if field_type == SELECT_ONE and group_by:
records = _flatten_multiple_dict_into_one(column, group_by,
records)
# use labels if group by
if group_by:
group_by_field = get_field_from_field_xpath(group_by, xform)
choices = get_field_choices(group_by, xform)
records = _use_labels_from_group_by_name(group_by,
group_by_field,
data_type,
records,
choices=choices)
return {
"field_type": field_type,
"data_type": data_type,
"field_xpath": field_xpath,
"field_label": field_label,
"grouped_by": group_by,
"data": records
}