⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

setRelation Where Condition (Input Statement) doesn't work at v.2.7.9



larasmith
  • profile picture
  • Member

Posted 12 September 2019 - 06:08 AM

Dear @web-johnny,

 

 
Hi there! I observed that the setRelation Where Condition (Input Statement) doesn't work anymore at v.2.7.9.
My code goes something like this:
$crud->setRelation('el_CourseId', 'set_course', '{course_Title} - [{course_RefNo}]', ["set_course.course_Id = '1522' OR set_course.course_Id = '1519'"]);

The old versions support this type of where condition. I need this feature because I need  an "OR" operator on one field that I am selecting data from.

 

The error displayed was:

 

 upload.png

I also tried putting parenthesis in my code so it becomes like this:

$crud->setRelation('el_CourseId', 'set_course', '{course_Title} - [{course_RefNo}]', ["(set_course.course_Id = '1522' OR set_course.course_Id = '1519')"]);

but the same error occurs. This is a major issue for the system that I am currently working now.

Is this a bug? If it is, then I hope it will be fixed soon. If there's a workaround, kindly teach me on how to do it.

 

Thank you very much and more power! 

:)

 

 


larasmith
  • profile picture
  • Member

Posted 13 September 2019 - 00:30 AM

Hello everyone!

 

Up on this issue.

 

I have tried another solution which is using the operator "IN":

$crud->setRelation('el_CourseId', 'set_course', '{course_Title} - [{course_RefNo}]', ['course_Id IN ? '=>"(1522, 1519)"]);

Unfortunately it still doesn't work. The error was:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''1522' ORDER BY `course_Title` ASC' at line 1

 

Has anyone experience this? Please send a suggestion on how to solve this.

 

Thank you!

 

:)


larasmith
  • profile picture
  • Member

Posted 23 September 2019 - 02:19 AM

Hello Everyone!

 

With @web-johnny's help, I was able to pinpoint where was the error was coming from.

There was no need to use square braces in the where condition of the setRelation. 

Removing square braces made both WHERE IN and  OR condition.

.

 

So instead of: 

$crud->setRelation('salesRepEmployeeNumber','employees','lastName', ["employeeNumber IN ('1102', '1702')"]); 

It should be:

$crud->setRelation('salesRepEmployeeNumber','employees','lastName', "employeeNumber IN ('1102', '1702')"); 

and for the OR WHERE:

$crud->setRelation('el_CourseId', 'set_course', '{course_Title} - [{course_RefNo}]', ["(course_Id = '1522' OR course_Id='1688')"]);

it should be:

$crud->setRelation('el_CourseId', 'set_course', '{course_Title} - [{course_RefNo}]', "(course_Id = '1522' OR course_Id='1688')"); 

I hope this helps others too!

 

:)