BUG: cmsb 3.59: order by

4 posts by 3 authors in: Forums > CMS Builder
Last Post: July 5, 2023   (RSS)

By kitsguru - June 4, 2023

I am testing cmsb 3.59 under php 8.1 with MariaDB v10.11.2 and getting the following error. This is happening whenever I have a left join.

Notice: MySQL Error: Column 'dragSortOrder' in order clause is ambiguous - in viewer_functions.php on line 343 by mysql_get_query in /Volumes/J/@projects/yaaws5/html/www/cmsb/lib/common.php on line 304

Here a sample getRecords call:

list($theRecords, $theMetaData) = getRecords(array(
    'tableName'   => 'bios',
    'where'       => $pageWhere,
    'loadUploads' => true,
    'allowSearch' => true,
    'leftJoin'    => array(
        'companies' => 'company'),
    'limit'       => '1',
    'debugSql'    => false,
));

It appears to picking up the orderBy clause from the editor sorting field, both tables have the dragSortOrder specified. 

WORK AROUND

Add a specific orderBy clause to the getRecords call with a fully qualified name 'orderBy' => "bios.dragSortOrder'.

NOTE:

This does happen with cmsb 3.57 and php 8.0

Jeff Shields

By mark99 - July 5, 2023

I get something similar after going from 3.57 to 3.59, also focused on line 304 of common.php:

E_USER_NOTICE: MySQL Error: Unknown column 'totalReviewsCount' in 'order clause' - in viewer_functions.php on line 543 by mysql_get_query()

Url: https://www....co.uk/../../?orderBy=totalReviewsCount&num=17

File Path: /../system/lib/common.php

By Dave - July 5, 2023

Hi Mark,

Are you using a leftJoin option?  And if so can you try adding a fully qualified name?

'orderBy' => " yourtable.dragSortOrder DESC ",

Just make sure the table and fieldname match what you're using.

Let me know if that works for you.

Dave Edis - Senior Developer
interactivetools.com