Table aggregation limit and suffix settings not respected

[grantfitzsimmons]

Describe the bug
The schema configuration's customized aggregation does not effect how the report runner works.

To Reproduce
Steps to reproduce the behavior:

1. Customize the schema to display some limit:

2. Print labels using the aggregated output of that field. See error.

Expected behavior
In Specify 6, the schema configuration is respected when running a report. In Specify 7, reports ignore this configuration but should do the same.

Screenshots

Desktop (please complete the following information):

• OS: Windows 10, macOS
• Browser: Edge
• Specify 7 Version: 7.6.1 & Edge

Reported By
Grant on behalf of LSUMZ

[benanhalt]

Specify 7 currently ignores the the count and ending settings of aggregators. This applies to queries as well.

[benanhalt]

Because Specify 7 does all the formatting and aggregation at the database level, tt would be best to figure out how to express the logic in SQL. MariaDB supports a limit clause in the GROUP_CONCAT function which fits the bill nicely:

> select concat(group_concat(lastname separator '; ' limit 3), if(count(*) > 3, '...', '')) as agents from agent where firstname = 'James'\G
*************************** 1. row ***************************
agents: Smith; Triplett; Deacon...

> select concat(group_concat(lastname separator '; ' limit 3), if(count(*) > 3, '...', '')) as agents from agent where firstname = 'Bill'\G
*************************** 1. row ***************************
agents: Cole; Ludt

To support it in MySQL it would probably be necessary to use SUBSTRING_INDEX on the separator after suppressing any occurrence of the separator in the values using REPLACE.

I'm thinking it wouldn't be too hard to add either of these approaches, but the MariaDB one is better. It might be possible to check if the server is MySQL or MariaDB and generate the corresponding SQL.

The relevant code is at

specify7/specifyweb/stored_queries/format.py

Line 168 in 7b3cbc9

aggregated = blank_nulls(group_concat(formatted, separator, *order_by))

and

specify7/specifyweb/stored_queries/group_concat.py

Lines 12 to 26 in 7b3cbc9

	def _group_concat_mysql(element, compiler, **kwargs):
	expr = compiler.process(element.clauses.clauses[0])
	def process_clause(idx):
	return compiler.process(element.clauses.clauses[idx])
	separator = process_clause(1) if len(element.clauses) > 1 else None
	order_by = process_clause(2) if len(element.clauses) > 2 else None
	inner = expr
	if order_by is not None:
	inner += " ORDER BY %s" % order_by
	if separator is not None:
	inner += " SEPARATOR %s" % separator
	return 'GROUP_CONCAT(%s)' % inner

[benanhalt]

>>> from django.db import connection
>>> connection.mysql_is_mariadb
True
>>> 

[maxpatiiuk]

#2796 adds front-end support for this. Back-end support is still pending

[grantfitzsimmons]

See custom format:

See newly defined report with the specified format:

Relevant XML in the DataObjFormatters file:

<aggregator name="Determination" title="Determination" class="edu.ku.brc.specify.datamodel.Determination" default="true" separator=" | " ending="..." count="3" format="Determination" orderfieldname="isCurrent"/>

2024-06-11 still not having any luck on v7.9.5

[emenslin]

Can recreate in edge (7.9.6)