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 }