Subpanel pagination

SuiteCRM Forum - English Language Developer Collaboration
1

Hello.

I have a problem with my subpanel pagination. When I click on the “next” button, an error appears : “Database failure. Please refer to suitecrm.log for details.”
In my ‘suitecrm.log’ file, I have the following error :

Error retrieving Campaign list:  Query Failed:DECLARE @topCount INT SET @topCount = 10 SELECT TOP (@topCount) * FROM
           ((SELECT ROW_NUMBER() OVER (ORDER BY .id) AS row_number,  campaign_log.*  FROM campaign_log WHERE campaign_log.campaign_id = N'795402bc-ba1f-b716-04b6-5bd1c2787ed5' AND campaign_log.deleted=0 AND activity_type=N'targeted' AND archived=0 )) AS a WHERE row_number > 10::: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The multi-part identifier ".id" could not be bound.

After searching, i found the problem, but I don’t know how resolve it :

In file “include/php-sql-parser.php”, the function “process_sql” use parenthesis to split query.
In my case, the problem is my query have only one pair of parenthesis, and the function “process_sql” can’t works as usual.

i found a temporary solution : in file "data/SugarBean.php’, function “get_union_related_list”, I added a counter to count how many times the “foreach” runs. If the “foreach” runs only one time, I take off parenthesis :


$cpt = 0;
        foreach ($subpanel_list as $this_subpanel) {
            if ($this_subpanel->isDatasourceFunction()
                && empty($this_subpanel->_instance_properties['generate_select'])) {
                $shortcut_function_name = $this_subpanel->get_data_source_name();
                $parameters = $this_subpanel->get_function_parameters();
                if (!empty($parameters)) {
                    //if the import file function is set, then import the file to call the custom function from
                    if (is_array($parameters) && isset($parameters['import_function_file'])) {
                        //this call may happen multiple times, so only require if function does not exist
                        if (!function_exists($shortcut_function_name)) {
                            require_once($parameters['import_function_file']);
                        }
                        //call function from required file
                        $tmp_final_query = $shortcut_function_name($parameters);
                    } else {
                        //call function from parent bean
                        $tmp_final_query = $parentbean->$shortcut_function_name($parameters);
                    }
                } else {
                    $tmp_final_query = $parentbean->$shortcut_function_name();
                }
                if (!$first) {
                    $final_query_rows .= ' UNION ALL ( '
                        . $parentbean->create_list_count_query($tmp_final_query, $parameters) . ' )';
                    $final_query .= ' UNION ALL ( ' . $tmp_final_query . ' )';
                } else {
                    $final_query_rows = '(' . $parentbean->create_list_count_query($tmp_final_query, $parameters) . ')';
                    $final_query = '(' . $tmp_final_query . ')';
                    $first = false;
                }
				        $cpt++;
            }
        }

    		if($cpt===1){
    			if($final_query[0]=='('){
    				$final_query = substr($final_query,1);
    			}
        
    			if($final_query[strlen($final_query)-1]==')'){
    				$final_query = substr($final_query,0,-1);
    			}
    		}

I’m not sure this code is the best way to do, further I custom a “core” file …

Maybe someone konws a best solution ?