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 https://github.com/specify/specify7/blob/7b3cbc9091270f6903ac3d17ae61ec21c58a21ec/specifyweb/stored_queries/format.py#L168 and https://github.com/specify/specify7/blob/7b3cbc9091270f6903ac3d17ae61ec21c58a21ec/specifyweb/stored_queries/group_concat.py#L12-L26

[benanhalt]

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

[maxpatiiuk]

https://github.com/specify/specify7/pull/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)