Announcement

Collapse
No announcement yet.

How to use IN, NOT IN, etc. operators

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

  • How to use IN, NOT IN, etc. operators

    I can't figure out how to use these operators:

    PHP Code:

      59     
    protected static $sqlOperators = array(
      
    60         'OR',
      
    61         'AND',
      
    62     );
      
    63
      64     
    protected static $comparisonOperators = array(
      
    65         '!=s' => 'NOT IN',
      
    66         '=s' => 'IN',
      
    67         '!=' => '<>',
      
    68         '!*' => 'NOT LIKE',
      
    69         '*' => 'LIKE',
      
    70         '>=' => '>=',
      
    71         '<=' => '<=',
      
    72         '>' => '>',
      
    73         '<' => '<',
      
    74         '=' => '='
      
    75     ); 


    which can be found in:
    Code:
    application/Espo/ORM/DB/Query/Base.php


    Nothing I've tried has worked and I think writing raw SQL to get around that is a bad idea. There are no examples in the documentation and I can't find any other files using these operators. Could you please provide examples in the documentation of all of the sqlOperators and comparisonOperators?

    For example, I want to use 'IN', which requires a field and an array of values. However, this doesn't work:

    PHP Code:

     25     $timesheets 
    $this->getEntityManager()->getRepository('Timesheet')
     
    26       ->select(['assignedUserId','dateEnd','status','dateStart'])
     
    27       ->where(array('status =s' => array('Approved','Submitted')))
     
    28       ->order('dateEnd'false)
     
    29       ->find(); 


    I also tried using 'OR' in a customWhere string, but that didn't work either. This is the query I am trying to build:

    Code:
    SELECT
        `id`,`name`,`status`,`date_end`,`assigned_user_id`
    FROM
        timesheet
    WHERE
        `date_end` < NOW() AND
        `status` NOT IN ('Approved','Submitted');
    Last edited by bandtank; 12-31-2017, 09:58 PM.
Working...
X