Sql to cake quey

Need Help
1

SELECT * FROM rooms WHERE ‘2019-11-12’ BETWEEN check_in AND check_out OR ‘2019-11-14’ BETWEEN check_in AND check_out AND payment_status=3 AND location=‘India’

How can i convert this sql query to cakephp query ??
Here i want to check certain date present between check_in AND check_out column.

2

Query builder docs have everything you need.

https://book.cakephp.org/3/en/orm/query-builder.html

Tip: set the Datasource log value in config/app.php to true so you can see the generated SQL in the logs.

3

Cakephp documentation nothing found like what I want.

4

You could use the cakephp pdo instance and use the query as is, but bind parameters.

But there are plenty examples in the reference that deanoj gave.

5

The page @deanoj referenced has examples of between, and and or queries. Try some things; if you still have problems, show us what you’ve got, explain what’s not working about it, and we’ll try to help fix that.

6

The CakePHP ORM doesn’t currently support using a bound value as the first part of between, but you can get around that (https://book.cakephp.org/3/en/orm/query-builder.html#binding-values)

$rooms = $this->Rooms->find()
  ->where([
    'or' => [
      ':date1 BETWEEN check_in AND check_out',
      ':date2 BETWEEN check_in AND check_out'
    ],
    'payment_status' => 3,
    'location' => 'india'
  ])
  ->bind(':date1', Date::parse('2019-11-12'), 'datetime')
  ->bind(':date2', Date::parse('2019-11-14'), 'datetime');
1 Like