Announcement

Collapse
No announcement yet.

where clause using LIKE and IN

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • where clause using LIKE and IN

    I'm trying to query for entities using relationships other than equals. I looked through the code to find the relationships (I couldn't find any in the documentation) and I got it to work with, for example ">=", but I can't get LIKE or IN to work.

    I'm trying to get event objects between a certain date and a specific type, which is a multi-enum. I can't figure out the syntax the where array. Here's what I have so far:

    PHP Code:

     78     $events 
    $this->getEntityManager()->getRepository('Event')
     
    79       ->where(array(
     
    80         'date >=' => $timesheet['dateStart'],
     
    81         'date <=' => $timesheet['dateEnd']
     
    82       ))->find(); 
    That works fine, but it finds too much. Here's what's in the database between '2017-11-16' and '2017-11-30'.
    2017-11-23 ["Holiday"]
    2017-11-24 ["Holiday"]
    2017-11-21 ["Company Lunch"]
    2017-11-22 ["Company Lunch","Holiday"]
    I need to select 11/22, 11/23, and 11/24. 11/21 should not be included in the results of the query. I tried all of the following:

    PHP Code:

    'type *' => 'Holiday'
    'type' 
    => '*Holiday*'
    'type *Holiday*' 
    => null 
    None of that works. I would also like to understand how to use IN, which is supposed to be '=s'. If you have an array of integers and you want to find every project with `some_value` IN (1,2,3), how do you write that in the where array?
    Last edited by bandtank; 11-16-2017, 04:33 AM.

  • #2
    After a ton of trial and error, I figured it out. The operators in the code were misleading until I started looking at how the GUI does it. For anyone who needs to see how to do this, look at Espo/Core/SelectManagers/Base.php starting at line 988. I figured out how to do 'in' and 'contains' (which is really LIKE with wildcards on both sides in MySQL) by looking at the code in the aforementioned file.

    Here are my solutions:

    PHP Code:

     62     $projects 
    $this->getEntityManager()->getRepository('Project')
     
    63         ->where(array( 'id =' => array_keys($projectIds)))->find();

     
    74     $events $this->getEntityManager()->getRepository('Event')
     
    75       ->where(array(
     
    76         'date >=' => $timesheet['dateStart'],
     
    77         'date <=' => $timesheet['dateEnd'],
     
    78         'type *'  => '%Holiday%'
     
    79       ))->find(); 

    Comment

    Working...
    X