Date comparison when using bean->get_full_list

👨‍💻 Development Help
1

Good afternoon,

I am trying to get all the beans from a given date_entered onwards. I am trying to use get_full list with ordered by date_external_created (a custom datetime field) with the filter:
'protocol_number IS NOT NULL AND date_entered > "' . $syncTime . '"' ($syncTime = ‘2020-04-12 16:55:03’)

If I try to run this on the database, with the query:

SELECT *
FROM cases
WHERE protocol_number IS NOT NULL 
   AND date_entered > '2020-04-12 16:55:03'
ORDER BY date_external_created

everything goes fine and it finds the entries that it should.

However, when I run:

$caseBean = \BeanFactory::getBean('Cases');
$filter = 'protocol_number IS NOT NULL AND date_entered > "' . $syncTime . '"';
$cases = $caseBean->get_full_list(
            'date_external_created',
            $filter
        );

it finds no beans.

When I try to debug, the query it shows it is running is:

 SELECT  cases.* ,cases_cstm.jjwg_maps_address_c,cases_cstm.jjwg_maps_lat_c,cases_cstm.jjwg_maps_lng_c,cases_cstm.jjwg_maps_geocode_status_c , jt0.user_name modified_by_name , jt0.created_by modified_by_name_owner  , 'Users' modified_by_name_mod , jt1.user_name created_by_name , jt1.created_by created_by_name_owner  , 'Users' created_by_name_mod , jt2.user_name assigned_user_name , jt2.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod , accounts.name account_name , accounts.assigned_user_id account_name_owner  , 'Accounts' account_name_mod FROM cases  LEFT JOIN cases_cstm ON cases.id = cases_cstm.id_c   LEFT JOIN  users jt0 ON cases.modified_user_id=jt0.id AND jt0.deleted=0

 AND jt0.deleted=0  LEFT JOIN  users jt1 ON cases.created_by=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0  LEFT JOIN  users jt2 ON cases.assigned_user_id=jt2.id AND jt2.deleted=0

 AND jt2.deleted=0  LEFT JOIN  accounts accounts ON cases.account_id=accounts.id AND accounts.deleted=0

 AND accounts.deleted=0 where (protocol_number IS NOT NULL AND date_entered > "2020-04-12 16:55:03") AND cases.deleted=0 ORDER BY cases.date_external_created

which is returns an error if I try to run it.

When I remove the date comparison, everything works just fine. How should I do the date comparisons and what am I doing wrong?

Thanks in advance.

More info: WSL Ubuntu Linux 22.04.2, SuiteCRM 8.2.3, and MariaDB Ver 15.1 Distrib 10.6.12-MariaDB

2

Check your logs, you probably have a FATAL there

3

I was able to solve this problem.
I found in the log in public/legacy/suitecrm.log.
The error was:

Thu Jun 22 14:51:38 2023 [160370][-none-][FATAL] Mysqli_query failed.
Thu Jun 22 14:51:38 2023 [160370][-none-][FATAL]  Query Failed:  SELECT  cases.* ,cases_cstm.jjwg_maps_address_c,cases_cstm.jjwg_maps_lat_c,cases_cstm.jjwg_maps_lng_c,cases_cstm.jjwg_maps_geocode_status_c , jt0.user_name modified_by_name , jt0.created_by modified_by_name_owner  , 'Users' modified_by_name_mod , jt1.user_name created_by_name , jt1.created_by created_by_name_owner  , 'Users' created_by_name_mod , jt2.user_name assigned_user_name , jt2.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod , accounts.name account_name , accounts.assigned_user_id account_name_owner  , 'Accounts' account_name_mod FROM cases  LEFT JOIN cases_cstm ON cases.id = cases_cstm.id_c   LEFT JOIN  users jt0 ON cases.modified_user_id=jt0.id AND jt0.deleted=0

 AND jt0.deleted=0  LEFT JOIN  users jt1 ON cases.created_by=jt1.id AND jt1.deleted=0

 AND jt1.deleted=0  LEFT JOIN  users jt2 ON cases.assigned_user_id=jt2.id AND jt2.deleted=0

 AND jt2.deleted=0  LEFT JOIN  accounts accounts ON cases.account_id=accounts.id AND accounts.deleted=0

 AND accounts.deleted=0 where (protocol_number IS NOT NULL AND date_entered > "2020-04-12 16:55:03") AND cases.deleted=0 ORDER BY cases.date_external_created: MySQL error 1052: Column 'date_entered' in where clause is ambiguous

It turns out that as it was joining multiple tables, each one with its ‘date_entered’ field, the query was unable to run, and so I needed to specify the table which I was trying to get the field from

1 Like