2

i have a custom collection which has database column start_at and end_at in Y-m-d format , i try to do some validation before inserting to database, one of them is make sure the period between start_at and end_at is not exist in database or the new start_at and end_at is not overlapping with existing start_at and end_at period. for example:

      Database Records
+-------------+------------+
|  start_at   |   end_at   |
+-------------+------------+
| 2018-10-10  | 2018-10-15 |
| 2018-10-17  | 2018-10-21 |
| 2018-10-22  | 2018-10-25 |
| 2018-11-02  | 2018-11-09 |
+--------------------------+

//wrong validation list
$startAt[1] = '2018-10-13';$endAt[1] = '2018-10-18';
$startAt[2] = '2018-10-24':$endAt[2] = '2018-10-26';
$startAt[3] = '2018-10-27';$endAt[3] = '2018-11-06';

//right validation list
$startAt[1] = '2018-10-16';$endAt[1] = '2018-10-16';
$startAt[2] = '2018-10-26':$endAt[2] = '2018-10-28';
$startAt[3] = '2018-10-30';$endAt[3] = '2018-11-01';

how can i filter the data above using collection filtering, so i can achieve the right filter method, what i've done so far but not working:

using and filter:

$now = new \DateTime();

//always return 0
$alreadyExistStart = $this->promo->create()->getCollection() 
  ->addFieldToFilter('start_at', ['start_at' => $now->format($data['start_at'])])
  ->addFieldToFilter('end_at', ['end_at' => $now->format($data['start_at'])])
  ->count();

//always return 0
$alreadyExistEnd = $this->promo->create()->getCollection() 
  ->addFieldToFilter('start_at', ['start_at' => $now->format($data['end_at'])])
  ->addFieldToFilter('end_at', ['end_at' => $now->format($data['end_at'])])
  ->count();

using or filter:

  //always return 0
  $alreadyExistEnd = $this->promo->create()->getCollection()
  ->addFieldToFilter(
        ['start_at', 'end_at'],
        [['gteq' => $data['end_at']], ['lteq' => $data['end_at']]]
  )->count();

  //always return 0
  $alreadyExistEnd = $this->promo->create()->getCollection()
  ->addFieldToFilter(
        ['start_at', 'end_at'],
        [['gteq' => $data['end_at']], ['lteq' => $data['end_at']]]
  )->count();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.