AnsweredAssumed Answered

[closed] Custom Module - automatic generated SQL Query issue - sugarfavorites record_id

Question asked by swaldinger on Nov 21, 2018

UPDATE: closed

I have updated my vardefs.php and the class file, and got i running.

 

 

 

Hi all,

 

I have build a custom module with the Module Builder to have a list of all jobs from job_queue. (Didn't find an existing Module for that). 

After that I included it in my project.

 

But now I have an issue with an automatic generated SQL Query on the list view:

SugarCRM builds a SELECT query to get all favorite marked records. 

On this query it makes a LEFT JOIN on the sugarfavorites table and the issue is located in the ON condition:

 

SELECT sf_job_queue.id                   my_favorite,
       job_queue.execute_time,
       job_queue.data,
       job_queue.scheduler_id,
       job_queue.status,
       job_queue.resolution,
       job_queue.failure_count,
       job_queue.retry_count,
       job_queue.requeue,
       jt1_assigned_user_link.first_name rel_assigned_user_name_first_name,
       jt1_assigned_user_link.last_name  rel_assigned_user_name_last_name,
       jt1_assigned_user_link.created_by assigned_user_name_owner,
       job_queue.rerun,
       job_queue.percent_complete,
       job_queue.assigned_user_id,
       job_queue.date_modified
FROM job_queue
       LEFT JOIN sugarfavorites as sf_job_queue
         ON (sf_job_queue.module = ?) AND (sf_job_queue.record_id = ) AND (sf_job_queue.assigned_user_id = ?) AND
            (sf_job_queue.deleted = ?)
       LEFT JOIN users jt1_assigned_user_link
         ON (job_queue.assigned_user_id = jt1_assigned_user_link.id) AND (jt1_assigned_user_link.deleted = ?)
WHERE sf_job_queue.id IS NOT NULL
ORDER BY job_queue.date_modified DESC
LIMIT 31 OFFSET 0

 

The

(sf_job_queue.record_id = )

is the problem. It should be like this:

 

(sf_job_queue.record_id = job_queue.id)

 

 This condition is build in the SugarQuery_Compiler_Doctrine::compileCondition()

 (sugarcrm/include/SugarQuery/Compiler/Doctrine.php)  caused by the Field  "$field->isNonDb()".

 

This property is set in SugarQuery_Builder_Field::setupField()

(sugarcrm/include/SugarQuery/Builder/Field.php)  where the $this->markNonDb(); function is called because the conditions (empty($this->def) && $this->field != 'id_c' && $this->field != '*') are valid.

 

But how to define the id field within my module correctly that this don't happen?

Do I have to list it in the vardefs.php in the fields array? (btw. this results in a SQL query with empty WHERE () ... )

It would be also ok to disable the favorite feature for this module for us.

 

Many thanks

 

Sven

Outcomes