The following code gives empty result but if the query which is generated by the query builder is executed in mysql console than it returns value.
Code:
$query = $this->Vehicles->find()
->where([
'Vehicles.first_enlisting_date IS NOT' => null,
'Vehicles.second_enlisting_date IS NOT' => null,
'Vehicles.third_enlisting_date IS' => null,
'DATE(Vehicles.second_enlisting_date) <=' => 'CURDATE()-INTERVAL 7 DAY'
]);
It gives the following empty array if print_r
Cake\ORM\ResultSet Object
(
[items] => Array
(
)
)
The query generated by query builder is as follows:
Cake\ORM\Query Object
(
[(help)] => This is a Query object, to get the results execute or iterate it.
[sql] => SELECT Vehicles.id AS `Vehicles__id`, Vehicles.title AS `Vehicles__title`, Vehicles.user_id AS `Vehicles__user_id`, Vehicles.dealer_id AS `Vehicles__dealer_id`, Vehicles.model_id AS `Vehicles__model_id`, Vehicles.brand_id AS `Vehicles__brand_id`, Vehicles.body_id AS `Vehicles__body_id`, Vehicles.year AS `Vehicles__year`, Vehicles.price AS `Vehicles__price`, Vehicles.nada_price AS `Vehicles__nada_price`, Vehicles.description AS `Vehicles__description`, Vehicles.latitude AS `Vehicles__latitude`, Vehicles.longitude AS `Vehicles__longitude`, Vehicles.zipcode AS `Vehicles__zipcode`, Vehicles.mileage AS `Vehicles__mileage`, Vehicles.vin_number AS `Vehicles__vin_number`, Vehicles.status AS `Vehicles__status`, Vehicles.vehicle_condition AS `Vehicles__vehicle_condition`, Vehicles.vin_image AS `Vehicles__vin_image`, Vehicles.image AS `Vehicles__image`, Vehicles.first_enlisting_date AS `Vehicles__first_enlisting_date`, Vehicles.second_enlisting_date AS `Vehicles__second_enlisting_date`, Vehicles.third_enlisting_date AS `Vehicles__third_enlisting_date`, Vehicles.listing_expired AS `Vehicles__listing_expired`, Vehicles.deleted_from_listing AS `Vehicles__deleted_from_listing`, Vehicles.created AS `Vehicles__created`, Vehicles.modified AS `Vehicles__modified` FROM vehicles Vehicles WHERE ((Vehicles.first_enlisting_date) IS NOT NULL AND (Vehicles.second_enlisting_date) IS NULL AND (Vehicles.third_enlisting_date) IS NULL AND Vehicles.first_enlisting_date < :c0 AND Vehicles.deleted_from_listing = :c1 AND Vehicles.listing_expired = :c2 AND Vehicles.status = :c3)
[params] => Array
(
[:c0] => Array
(
[value] => DATE(CURDATE() - INTERVAL 7 DAY)
[type] => date
[placeholder] => c0
)
[:c1] => Array
(
[value] => 0
[type] => boolean
[placeholder] => c1
)
[:c2] => Array
(
[value] => 0
[type] => boolean
[placeholder] => c2
)
[:c3] => Array
(
[value] => 1
[type] => integer
[placeholder] => c3
)
)
[defaultTypes] => Array
(
[Vehicles__id] => integer
[Vehicles.id] => integer
[id] => integer
[Vehicles__title] => string
[Vehicles.title] => string
[title] => string
[Vehicles__user_id] => integer
[Vehicles.user_id] => integer
[user_id] => integer
[Vehicles__dealer_id] => integer
[Vehicles.dealer_id] => integer
[dealer_id] => integer
[Vehicles__model_id] => string
[Vehicles.model_id] => string
[model_id] => string
[Vehicles__brand_id] => string
[Vehicles.brand_id] => string
[brand_id] => string
[Vehicles__body_id] => string
[Vehicles.body_id] => string
[body_id] => string
[Vehicles__year] => string
[Vehicles.year] => string
[year] => string
[Vehicles__price] => float
[Vehicles.price] => float
[price] => float
[Vehicles__nada_price] => float
[Vehicles.nada_price] => float
[nada_price] => float
[Vehicles__description] => text
[Vehicles.description] => text
[description] => text
[Vehicles__latitude] => string
[Vehicles.latitude] => string
[latitude] => string
[Vehicles__longitude] => string
[Vehicles.longitude] => string
[longitude] => string
[Vehicles__zipcode] => integer
[Vehicles.zipcode] => integer
[zipcode] => integer
[Vehicles__mileage] => string
[Vehicles.mileage] => string
[mileage] => string
[Vehicles__vin_number] => string
[Vehicles.vin_number] => string
[vin_number] => string
[Vehicles__status] => integer
[Vehicles.status] => integer
[status] => integer
[Vehicles__vehicle_condition] => tinyinteger
[Vehicles.vehicle_condition] => tinyinteger
[vehicle_condition] => tinyinteger
[Vehicles__vin_image] => string
[Vehicles.vin_image] => string
[vin_image] => string
[Vehicles__image] => string
[Vehicles.image] => string
[image] => string
[Vehicles__first_enlisting_date] => date
[Vehicles.first_enlisting_date] => date
[first_enlisting_date] => date
[Vehicles__second_enlisting_date] => date
[Vehicles.second_enlisting_date] => date
[second_enlisting_date] => date
[Vehicles__third_enlisting_date] => date
[Vehicles.third_enlisting_date] => date
[third_enlisting_date] => date
[Vehicles__listing_expired] => boolean
[Vehicles.listing_expired] => boolean
[listing_expired] => boolean
[Vehicles__deleted_from_listing] => boolean
[Vehicles.deleted_from_listing] => boolean
[deleted_from_listing] => boolean
[Vehicles__created] => datetime
[Vehicles.created] => datetime
[created] => datetime
[Vehicles__modified] => datetime
[Vehicles.modified] => datetime
[modified] => datetime
)
[decorators] => 0
[executed] =>
[hydrate] => 1
[buffered] => 1
[formatters] => 0
[mapReducers] => 0
[contain] => Array
(
)
[matching] => Array
(
)
[extraOptions] => Array
(
)
[repository] => App\Model\Table\VehiclesTable Object
(
[registryAlias] => Vehicles
[table] => vehicles
[alias] => Vehicles
[entityClass] => \Cake\ORM\Entity
[associations] => Array
(
[0] => dealers
[1] => users
[2] => model
[3] => brands
[4] => vdetails
[5] => series
[6] => offers
)
[behaviors] => Array
(
[0] => Timestamp
)
[defaultConnection] => default
[connectionName] => default
)
)
Now if I run the query in mysql console then I get value. Here is the mysql code which I executed
SELECT Vehicles.id AS `Vehicles__id`, Vehicles.title AS `Vehicles__title`, Vehicles.user_id AS `Vehicles__user_id`, Vehicles.dealer_id AS `Vehicles__dealer_id`, Vehicles.model_id AS `Vehicles__model_id`, Vehicles.brand_id AS `Vehicles__brand_id`, Vehicles.body_id AS `Vehicles__body_id`, Vehicles.year AS `Vehicles__year`, Vehicles.price AS `Vehicles__price`, Vehicles.nada_price AS `Vehicles__nada_price`, Vehicles.description AS `Vehicles__description`, Vehicles.latitude AS `Vehicles__latitude`, Vehicles.longitude AS `Vehicles__longitude`, Vehicles.zipcode AS `Vehicles__zipcode`, Vehicles.mileage AS `Vehicles__mileage`, Vehicles.vin_number AS `Vehicles__vin_number`, Vehicles.status AS `Vehicles__status`, Vehicles.vehicle_condition AS `Vehicles__vehicle_condition`, Vehicles.vin_image AS `Vehicles__vin_image`, Vehicles.image AS `Vehicles__image`, Vehicles.first_enlisting_date AS `Vehicles__first_enlisting_date`, Vehicles.second_enlisting_date AS `Vehicles__second_enlisting_date`, Vehicles.third_enlisting_date AS `Vehicles__third_enlisting_date`, Vehicles.listing_expired AS `Vehicles__listing_expired`, Vehicles.deleted_from_listing AS `Vehicles__deleted_from_listing`, Vehicles.created AS `Vehicles__created`, Vehicles.modified AS `Vehicles__modified` FROM vehicles Vehicles WHERE ((Vehicles.first_enlisting_date) IS NOT NULL AND (Vehicles.second_enlisting_date) IS NULL AND (Vehicles.third_enlisting_date) IS NULL AND Vehicles.first_enlisting_date < DATE(CURDATE() - INTERVAL 7 DAY) AND Vehicles.deleted_from_listing = 0 AND Vehicles.listing_expired = 0 AND Vehicles.status = 1)
Then I get the result:
Is it causing for the date functions? One important thing should be mention that the code is working at Ubuntu machine but not at windows machine!
Thanks for any help.
