Contain complex query date_format and between

Question

Code Update 2017/07/20

I followed the recommendations of @Arcesilas (here) with the use of date_format of MySQL.

I upgraded my data structure by merging start_month and start_day (ex 2017-01-16 = 0116)

[...]
->contain([
    'Bookings', 
    'Amenities',
    'Periodicities' =>  function ($q) use ($check_in, $check_out) {
        $start = $q->func()->date_format([
            'Periodicities.start' => $check_in->format('Ymd'),
            "'%m%d'" => 'literal'
        ]);

        $end = $q->func()->date_format([
            'Periodicities.end' => $check_out->format('Ymd'),
            "'%m%d'" => 'literal'
        ]);

       return $q
            ->where(function ($exp) use ($start) {
                $exp->between($start, 'Periodicities.start', 'Periodicities.end');
                return $exp;
            })
            ->orWhere(function ($exp) use ($end) {
                $exp->between($end, 'Periodicities.start', 'Periodicities.end');
                return $exp;
            });
    }

])
[...]

Cakephp SQL Log

SELECT 
      PeriodicitiesRooms.id AS `Periodicities_CJoin__id`, 
      PeriodicitiesRooms.periodicity_id AS `Periodicities_CJoin__periodicity_id`, 
      PeriodicitiesRooms.room_id AS `Periodicities_CJoin__room_id`, 
      PeriodicitiesRooms.price AS `Periodicities_CJoin__price`, 
      Periodicities.id AS `Periodicities__id`, 
      Periodicities.name AS `Periodicities__name`, 
      Periodicities.start AS `Periodicities__start`, 
      Periodicities.end AS `Periodicities__end`, 
      Periodicities.price AS `Periodicities__price`, 
      Periodicities.modified AS `Periodicities__modified` 
    FROM 
      periodicities Periodicities 
      INNER JOIN periodicities_rooms PeriodicitiesRooms ON Periodicities.id = (
        PeriodicitiesRooms.periodicity_id
      ) 
    WHERE 
  (
    date_format('20170726', '%m%d') BETWEEN 'Periodicities.start' 
    AND 'Periodicities.end' 
    OR (
      PeriodicitiesRooms.room_id in ('1', '2', '3') 
      AND date_format('20170720', '%m%d') BETWEEN 'Periodicities.start' 
      AND 'Periodicities.end'
    )
  )

This does not give me any more result because ORM of CakePHP adds apostrophes (') to the end of BETWEEN' Periodicities.start 'AND' Periodicities.end'

If in my database I paste the CakePHP query without the apostrophes (') it works. Do you have any idea what changes to make to the end of my query so that it looks like this: BETWEEN 'Periodicities.start' AND 'Periodicities.end' to BETWEEN Periodicities.start AND Periodicities.end ?

'SELECT * FROM periodicities WHERE date_format(' . $check_in->format('Ymd') . ', '%m%d') BETWEEN start AND end'

"_not found_" is basically like "_doesn't work_", it's not very informative. Please be more specific as to what _exactly_ happens, and what _exactly_ you'd expected to happen instead. Also please show your debugging attempts and possible errors. — ndm, Jul 18 '17 at 11:00
@ndm, hi and thank you, my `Periodicities` is empty `'periodicities' => [],` after this request. but i have many Periodicities in my database. — eclaude, Jul 18 '17 at 11:06
I see. What's the value of `$check_in` and `$check_out`, and what does the generated SQL for the `Periodicities` association look like (check [**Debug Kit**](https://book.cakephp.org/3.0/en/debug-kit.html))? — ndm, Jul 18 '17 at 11:17
The value is `$check_in` = `2017-08-09` and `$check_out` = `2017-09-27` for the SQL log : https://codeshare.io/GkpNxx — eclaude, Jul 18 '17 at 11:28
Seeing the `room_id` condition in the `OR` section is weird, that looks like a possible bug. Try wrapping the conditions in a single `where()` call. Besides that, the conditions do not seem to match any of the records that you're showing here!? If there are more records (what do the ones you expect to match look like?), does running the generated SQL return any results? — ndm, Jul 18 '17 at 11:52
The IDs `1,2,3` are real IDs, i have rooms but no Periodicities (i have update the codeshare after merge in same where) — eclaude, Jul 18 '17 at 12:03
I know they are existing IDs, as that condition is being generated by CakePHP based on query results. I was referring to your `BETWEEN` conditions (forgot to mention that specifically, sorry), they do not match any of the records that you're showing. — ndm, Jul 18 '17 at 12:20
In fact yes, for that I have to change the ways to store my dates (start_day, end_day & start_month, end_month) in order to have valid MySQL date without the years is possibe? — eclaude, Jul 18 '17 at 12:41
There is no MySQL date-ish type that would store values without a year, no, `DATE/TIME` columns however can store the year as `0000`. That's not the problem however, I'm not sure you understood me correctly. What I'm saying is that none of the records that you're showing matches your `BETWEEN` day/month criterias, and that is irrespective of the storage format, hence the question why you do not receive any `periodicities` records would kinda be solved!? — ndm, Jul 18 '17 at 13:21
@ndm If I understand correctly, my data is not valid. I also came here to have a correct approach to the treatment of periodicities. What change do I need to make to have price seasons set by the "manager"? Thank you for the time you spend answering me. — eclaude, Jul 19 '17 at 03:32
What changes do I need to make to manage seasonal room rates? My data structure is not good? For if I post `$check_in = 2017-12-15` and `$check_out = 2018-04-30` (ex : `$check_in->format('j') == 15` & `$check_out->format('j') == 30`) why it does not get me out of result? — eclaude, Jul 19 '17 at 04:06

styks · Answer 1 · 2017-07-21T12:48:18.110

EDIT

From your comment I see you don't want to store years because the year is not relevant.

In that case, you can still store all your dates as a specific year and then hard code that year into the query. I have modified the query to use year 2000.

ORIGINAL

The conversation is unclear to me but I will attempt to answer the question I believe you are asking.

It appears you want to know how to find Periodicities between the checkin time or Periodicities between the checkout time.

It also appears you want to know the best way to store the dates.

Just store the dates as dates. No need to make it complicated
Between is also overly complicated.

This will do what you need it to do without being confusing.

[...]
->contain([
    'Bookings', 
    'Amenities',
    'Periodicities' =>  function ($q) use ($check_in, $check_out) {
        $q->where([
            'Periodicities.start >' => $check_in->format('2000-m-d'),
            'Periodicities.end <' => $check_in->format('2000-m-d')
        ]);
        $q->orWhere([
            'Periodicities.start >' => $check_out->format('2000-m-d'),
            'Periodicities.end <' => $check_out->format('2000-m-d')
        ]);

        return $q;
    }

])
[...]

Hi and tank you for your answer, i do not want to store dates for the years because the periodicities are repeated every year without exeption. — eclaude, Jul 21 '17 at 03:50
@eclaude I have updated with a way to handle that. Here is another similar answer. https://stackoverflow.com/a/4998534/711401 — styks, Jul 21 '17 at 12:50

score 1 · Accepted Answer · answered Jul 24 '17 at 05:27

I solved my problem with this query :

->contain([
    'Bookings', 
    'Amenities',
    'Periodicities' =>  function ($q) use ($check_in, $check_out) {
        $q->where(function ($exp) use ($check_in, $check_out) {
            $exp->gte('Periodicities.start', $check_in->format('md'));
            $exp->lte('Periodicities.end', $check_out->format('md'));
        });
        return $q;
    }
])

I keep storing in database dates with the month and day md format Ex: 0901 < 1030 01 September < 30 October

Contain complex query date_format and between

2 Answers2