Uploaded image for project: 'PresideCMS'
  1. PRESIDECMS-1215

Broken SQL Query when creating an own LabelRenderer

    Details

    • Type: Bug
    • Status: Done
    • Priority: Highest
    • Resolution: Fixed
    • Affects versions: 10.9.0
    • Fix versions: 10.9.0
    • Labels:
      None
    • Sprint:
    • Accepted:
      Yes

      Description

      When programming an own label renderer a SQL Query is generated which fails.

      The generated query in my case (for website users) was:

      select display_name as label, website_user.id as id from `psys_website_user` `website_user` where display_name as label like '%a%' order by `website_user`.`display_name` limit 0, 1000
      

      The problem is this piece of generated code: where display_name as label like
      This should only be: where display_name like

      Solution:
      I already found a solution for this:
      The /preside/system/services/admin/DataManagerService.cfc, function getRecordsForAjaxSelect() need the following change:

      	if ( arguments.ids.len() ) {
      			args.filter = { "#idField#" = arguments.ids };
      		} else if ( Len( Trim( arguments.searchQuery ) ) ) {
      			var searchFields = [ labelField ];
      			if ( len( arguments.labelRenderer ) ) {
      
      
      				// LabelRenderer Fix for "where display_name as label like '%a%' " issue
      				searchFields = _getLabelRendererService().getSelectFieldsForLabel( arguments.labelRenderer );
      				for(i=1;i<=searchFields.len();i++){
      					searchFields[i] = ListFirst(searchFields[i]," ");
      				}
      				// End of LabelRenderer Fix
      
      
      			}
      			args.filter       = _buildSearchFilter(
      				  q            = arguments.searchQuery
      				, objectName   = arguments.objectName
      				, gridFields   = args.selectFields
      				, labelfield   = labelfield
      				, searchFields = searchFields
      			);
      			args.filterParams = { q = { type="varchar", value="%" & arguments.searchQuery & "%" } };
      		}
      

      the selectfields already maybe contain a 'as' string. So selecting the first word (which is only the fieldname) is a solution.

      PS: I have to apologize, that I still haven't installed my system to do a pull request. And I also don't know if the solution I found is completely valid and I think it can be done much more elegant. But hey, it works.

      I'd be happy if someone do a pull request for this. He can have all the kudos.

        Attachments

          Activity

            People

            • Assignee:
              seb.duggan Seb Duggan
              Reporter:
              madmikede Michael Hnat
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: