Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-21794

Replace ANY_VALUE with GROUP_CONCAT and avoid using disableFullGroupByMode

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 4.7.29
    • Fix Version/s: 5.0.0
    • Component/s: None
    • Labels:
      None
    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      None
    • Funding Source:
      Contributed Code
    • Verified?:
      No

      Description

      Currently on basis of CRM_Utils_SQL::disableFullGroupByMode() we are deciding if the mysql has FULL_GROUP_BY_MODE enabled, on basis of which, we are rendering ANY_VALUE aggregate function on SELECT columns which is a kinda hackish fix to bypass the GROUP BY errors. In order to make queries more generic i.e. to make it work against any MySQL version these are the steps to do so:

      1. Change aggregate fn name to more appropriate e.g. appendAnyValueToSelect to useGroupConcatForSelectColumns

      2. Remove CRM_Utils_SQL::disableFullGroupByMode() check and use GROUP_CONCAT(DISTINCT selectColumn, $seperatorOfChoice) instead of ANY_VALUE(selectColumn)

      3. Move the aggregate functions to CRM_Utils_SQL

      4. Make changes in CRM files to rename the fn call

      5. Unit test fixes in tests/* (if any)

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              monish.deb Monish Deb
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated: