1

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:

enter image description here

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.

Mushfiqur Rahman
  • 306
  • 4
  • 18
  • Please add the complete code from calling `$this->Vehicles->find()` to your `print_r()`. Be sure to understand when cakephp executes the query (https://book.cakephp.org/3.0/en/orm/query-builder.html#queries-are-lazily-evaluated). – RiWe Apr 16 '18 at 11:29
  • One important thing should be mention that the code is working at Ubuntu machine but not at windows machine! – Mushfiqur Rahman Apr 16 '18 at 11:36
  • 1
    This has been answered many times. [Here's one example](https://stackoverflow.com/questions/30446859/i-need-to-count-the-today-created-account-in-cake-3). – Greg Schmidt Apr 16 '18 at 15:02
  • I've found my answer from your given link. Actually though the accepted answer was good for details and to understand a concept but the second answer works for me. It is mentioned in the second answer that: `Note that passing it in form where(['DATE(created)' => 'CURDATE()']) will not work, since CURDATE() will be interpreted as a string.` – Mushfiqur Rahman Apr 18 '18 at 06:45
  • So my modified code looks like as follows: `$firstExpiredConditions = [ 'Vehicles.first_enlisting_date IS NOT' => null, 'Vehicles.second_enlisting_date IS' => null, 'Vehicles.third_enlisting_date IS' => null, 'DATE(Vehicles.first_enlisting_date) <= (CURDATE() - INTERVAL 7 DAY)' ];` – Mushfiqur Rahman Apr 18 '18 at 06:57

0 Answers0