Forum

Thread tagged as: Problem, Error, Shop

Multiple Filter For Dates

We're building a site with Perch Shop that will have products available between 2 dates, kind of like Wowcher/Groupon, so I've needed to add 2 date fields to the product template:

<perch:shop id="startDate" type="date" label="Start Date and Time" time="true" format="d F Y H:i" order="5" />
<perch:shop id="endDate" type="date" label="End Date and Time" time="true" format="d F Y H:i" order="6" />

I'm then filtering on those dates in an attempt to only display products that have a start date before the today and end later than today.

This is what I'm trying but it's not rendering any products

perch_shop_products([      
      'filter'   => [
        [
          'filter'   => 'startDate',
          'match'    => 'lte',
          'value'    => date('Y-m-d H:i:s')
        ],
        [
          'filter'   => 'endDate',
          'match'    => 'gte',
          'value'    => date('Y-m-d H:i:s')
        ]
      ]
    ]);

This doesn't return any products, but using those filters individually does.

Debug for that query is this:

SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' WHERE 1=1 AND ((idx.indexKey='startDate' AND idx.indexValue <= '2017-08-14 12:53:11') OR (idx.indexKey='endDate' AND idx.indexValue >= '2017-08-14 12:53:11')) AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey GROUP BY idx.itemID, idx2.indexValue, productID ) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval HAVING count(*)=2 ORDER BY sortval ASC

Diagnostics

Perch Runway: 3.0.8
Production mode: Production (100)
Installed apps: content (3.0.8), assets (3.0.8), categories (3.0.8), perch_forms (1.9.1), perch_shop_orders (1.2.3), perch_shop_products (1.2.3), perch_shop (1.2.3), perch_members (1.6.2)
DB driver: PDO
DB tables: perch2_backup_plans (1), perch2_backup_resources (0), perch2_backup_runs (20), perch2_categories (7), perch2_category_counts (0), perch2_category_sets (1), perch2_collection_index (0), perch2_collection_items (0), perch2_collection_revisions (0), perch2_collections (0), perch2_content_index (78), perch2_content_items (29), perch2_content_locks (0), perch2_content_regions (7), perch2_forms (1), perch2_forms_responses (2), perch2_members (3), perch2_members_forms (1), perch2_members_member_tags (0), perch2_members_sessions (3), perch2_members_tags (0), perch2_menu_items (15), perch2_navigation (0), perch2_navigation_pages (0), perch2_page_routes (6), perch2_page_templates (18), perch2_pages (17), perch2_resource_log (69), perch2_resource_tags (0), perch2_resources (18), perch2_resources_to_tags (0), perch2_settings (32), perch2_shop_addresses (75), perch2_shop_admin_index (64), perch2_shop_brands (1), perch2_shop_cart (84), perch2_shop_cart_data (72), perch2_shop_cart_items (33), perch2_shop_countries (250), perch2_shop_currencies (152), perch2_shop_customers (4), perch2_shop_emails (2), perch2_shop_index (893), perch2_shop_option_values (0), perch2_shop_options (0), perch2_shop_order_items (65), perch2_shop_order_promotions (0), perch2_shop_order_statuses (9), perch2_shop_orders (33), perch2_shop_orders_meta (1), perch2_shop_product_files (0), perch2_shop_product_option_values (0), perch2_shop_product_options (0), perch2_shop_product_tags (0), perch2_shop_products (16), perch2_shop_promotions (0), perch2_shop_sales (0), perch2_shop_search (16), perch2_shop_shipping_zone_countries (1), perch2_shop_shipping_zones (1), perch2_shop_shippings (2), perch2_shop_tax_exhibits (105), perch2_shop_tax_group_rates (1), perch2_shop_tax_groups (1), perch2_shop_tax_locations (1), perch2_shop_tax_rates (1), perch2_shop_variants (0), perch2_user_passwords (1), perch2_user_privileges (72), perch2_user_role_privileges (22), perch2_user_roles (2), perch2_users (3)
Users: 3
App runtimes:
<?php
  $apps_list = [
        'perch_forms',
        'perch_members',
        'perch_shop',
    ];
Scheduled tasks for Backup: plan_1 (10 mins)
Editor plug-ins:
H1: c2c0d4c862fcff0f53f3e20e7722906e
L1: b8e0f7cd814dce17db4311d25a624608
F1: 0c66c2e1f82f9e0b7617b2cb8270f2c7
content_singlePageEdit: 1
helpURL:
hideBranding: 0
content_collapseList: 1
lang: en-gb
update_2.8.34: done
headerColour: #000000
headerScheme: dark
update_runway_2.8.34: done
perch_shop_update: 1.2.3
perch_members_login_page: /members/login.php?r={returnURL}
perch_shop_price_tax_mode: inc
perch_shop_trade_price_tax_mode: inc
perch_shop_product_url: /products/{slug}
perch_shop_default_currency: 47
perch_shop_reporting_currency: 47
perch_shop_invoice_number_format: invoice%d
dashboard: 0
hide_pwd_reset: 0
content_hideNonEditableRegions: 0
content_frontend_edit: 0
latest_version:
on_sale_version:
update_runway_3.0.8: done
perch_members_update: 1.4
keyboardShortcuts: 0
siteOffline: 0
content_skip_region_list: 0
siteURL: /
perch_shop_site_url: https://1dw.creativemonster.co.uk
logoPath: /admin/resources/logo.svg
PERCH_DEVELOPMENT: 10
PERCH_STAGING: 50
PERCH_PRODUCTION: 100
PERCH_SITEPATH: /Users/Toby/Sites/1dw
PERCH_SCHEDULE_SECRET: eXybvCZ4wDfAzr9F
PERCH_DB_USERNAME: root
PERCH_DB_SERVER: localhost
PERCH_DB_DATABASE: perch_1dw_local
PERCH_DB_PREFIX: perch2_
PERCH_EMAIL_FROM: admin@1daywow.com
PERCH_EMAIL_FROM_NAME: 1 Day Wow
PERCH_LOGINPATH: /admin
PERCH_PATH: /Users/Toby/Sites/1-day-wow/admin
PERCH_CORE: /Users/Toby/Sites/1-day-wow/admin/core
PERCH_RESFILEPATH: /Users/Toby/Sites/1-day-wow/admin/resources
PERCH_RESPATH: /admin/resources
PERCH_HTML5: 1
PERCH_TZ: europe/london
PERCH_RUNWAY: 1
PERCH_ERROR_MODE: DIE
PERCH_DATE_LONG: %d %B %Y
PERCH_DATE_SHORT: %d %b %Y
PERCH_TIME_SHORT: %H:%M
PERCH_TIME_LONG: %H:%M:%S
PERCH_RUNWAY_ROUTED:
PERCH_STRONG_PASSWORDS:
PERCH_ASSET_VERSION: 5163d57ff611b3cf853b
PERCH_DEBUG:
PERCH_PREVIEW_ARG: preview
PERCH_TEMPLATE_PATH: /Users/Toby/Sites/1-day-wow/admin/templates
PERCH_TEMPLATE_FILTERS:
PERCH_DEFAULT_DOC: index.php
PERCH_DEFAULT_EXT: .php
PERCH_PRODUCTION_MODE: 100
PERCH_XHTML_MARKUP:
PERCH_RWD: 1
PERCH_HTML_ENTITIES:
PERCH_SSL:
PERCH_STRIPSLASHES:
PERCH_PROGRESSIVE_FLUSH: 1
PERCH_PARANOID:
PERCH_FORCE_SECURE_COOKIES:
PERCH_DEFAULT_BUCKET: default
PERCH_TRANSLATION_ASSIST:
PERCH_PASSWORD_MIN_LENGTH: 6
PERCH_MAX_FAILED_LOGINS: 10
PERCH_AUTH_LOCKOUT_DURATION: 1 HOUR
PERCH_VERIFY_UPLOADS:
PERCH_PRIV_ASSIST:
PERCH_CUSTOM_EDITOR_CONFIGS:
PERCH_AUTH_PLUGIN:
PERCH_DB_CHARSET: utf8
PERCH_DB_PORT:
PERCH_DB_SOCKET:
PERCH_SHOP_VERSION: 1.2.3
PERCH_APPS_EDITOR_PLUGIN: markitup
PERCH_APPS_EDITOR_MARKUP_LANGUAGE: markdown
Hosting settings

PHP: 5.6.30
Zend: 2.6.0
OS: Darwin
SAPI: cgi-fcgi
Safe mode: not detected
MySQL client: mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $
MySQL server: 5.6.35
Free disk space: 32.77 GB
Extensions: Core, date, ereg, libxml, openssl, pcre, sqlite3, zlib, bcmath, bz2, calendar, ctype, curl, dom, hash, fileinfo, filter, ftp, gd, SPL, iconv, intl, json, ldap, mbstring, session, standard, mysqlnd, mysqli, PDO, pdo_mysql, pdo_sqlite, Phar, posix, readline, Reflection, mysql, SimpleXML, soap, sockets, exif, tokenizer, wddx, xml, xmlreader, xmlwriter, xsl, zip, cgi-fcgi, imap, gettext, mcrypt, pgsql, pdo_pgsql, igbinary, memcached, mhash
GD: Yes
ImageMagick: No
PHP max upload size: 32M
PHP max form post size: 32M
PHP memory limit: 128M
Total max uploadable file size: 32M
Resource folder writeable: Yes
Session timeout: 24 minutes
Native JSON: Yes
Filter functions: Yes
Transliteration functions: Yes
PHP_FCGI_CHILDREN: 4
PWD: /Applications/MAMP/fcgi-bin
PHP_FCGI_MAX_REQUESTS: 200
__CF_USER_TEXT_ENCODING: 0x1F5:0x0:0x2
ORIG_SCRIPT_NAME: /fcgi-bin/php5.6.30.fcgi
ORIG_PATH_TRANSLATED: /Users/Toby/Sites/1-day-wow/admin/core/settings/diagnostics/index.php
ORIG_PATH_INFO: /admin/core/settings/diagnostics/index.php
ORIG_SCRIPT_FILENAME: /Applications/MAMP/fcgi-bin/php5.6.30.fcgi
SCRIPT_NAME: /admin/core/settings/diagnostics/index.php
REQUEST_URI: /admin/core/settings/diagnostics/?extended
QUERY_STRING: extended
REQUEST_METHOD: GET
SERVER_PROTOCOL: HTTP/1.1
GATEWAY_INTERFACE: CGI/1.1
REDIRECT_URL: /admin/core/settings/diagnostics/index.php
REDIRECT_QUERY_STRING: extended
REMOTE_PORT: 49526
SCRIPT_FILENAME: /Users/Toby/Sites/1-day-wow/admin/core/settings/diagnostics/index.php
SERVER_ADMIN: you@example.com
DOCUMENT_ROOT: /Users/Toby/Sites/1-day-wow
REMOTE_ADDR: ::1
SERVER_PORT: 8888
SERVER_ADDR: ::1
SERVER_NAME: 1dw.loc
SERVER_SOFTWARE: Apache
PATH: /usr/bin:/bin:/usr/sbin:/sbin
HTTP_COOKIE: PHPSESSID=e785781da27908b35b005271476d84c6; cmsa=1
HTTP_ACCEPT_LANGUAGE: en-US,en;q=0.8
HTTP_ACCEPT_ENCODING: gzip, deflate
HTTP_REFERER: https://1dw.loc:8888/admin/core/settings/diagnostics/add-ons/
HTTP_ACCEPT: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8
HTTP_USER_AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36
HTTP_UPGRADE_INSECURE_REQUESTS: 1
HTTP_CONNECTION: keep-alive
HTTP_HOST: 1dw.loc:8888
REDIRECT_STATUS: 200
REDIRECT_HANDLER: php-fastcgi
FCGI_ROLE: RESPONDER
PHP_SELF: /admin/core/settings/diagnostics/index.php
REQUEST_TIME_FLOAT: 1502711384.39
REQUEST_TIME: 1502711384
argc: 1
Toby Martin

Toby Martin 1 points

  • 4 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

4 years ago

I think you have your gte and lte the wrong way around.
Toby Martin

Toby Martin 1 points

4 years ago

No, I don't believe so.

Only show products that have a start date & time before the time now (i.e. not pre-scheduled).

[
          'filter'   => 'startDate',
          'match'    => 'lte',
          'value'    => date('Y-m-d H:i:s')
]

and that have an end date & time after the time now (i.e. not expired)

        [
          'filter'   => 'endDate',
          'match'    => 'gte',
          'value'    => date('Y-m-d H:i:s')
        ]
Drew McLellan

Drew McLellan 2638 points
Perch Support

4 years ago

Ah right, sorry, I misunderstood. I was thinking about comparing against a known date.

In which case, it looks fine. Have you broken it down?
Toby Martin

Toby Martin 1 points

4 years ago

Yeah, those filters work fine on their own. I can filter all products with a start date previous to now, and I can view all products with an end date past today but not a combination of the two.
Toby Martin

Toby Martin 1 points

4 years ago

The issue seems eerily similar to this one, which was resolved with an update to Perch 2:

https://forum.grabaperch.com/forum/06-21-2016-multiple-filters-on-perch-shop-products
Drew McLellan

Drew McLellan 2638 points
Perch Support

4 years ago

Try:

'filter-mode' => 'ungrouped',
Toby Martin

Toby Martin 1 points

4 years ago

Boom, nailed it.

I've not seen that parameter before.
Toby Martin

Toby Martin 1 points

4 years ago

I've been using a another version of this code elsewhere on the site to show a featured product but even with 'filter-mode' => 'ungrouped', it doesn't return any results.

perch_shop_products([
        'template'  => 'products/_featured_product.html',
        'filter' => [
            [
                'filter' => 'endDate',
                'match'  => 'gte',
                'value'  => date('Y-m-d H:i:s'),
            ],
            [
                'filter' => 'featured',
                'match'  => 'eq',
                'value'  => 'true',
            ]
        ],
        'filter-mode' => 'ungrouped'
    ]);

Debug:

SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM perch2_shop_index idx JOIN perch2_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN perch2_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='title' WHERE 1=1 AND ((idx.indexKey='endDate' AND idx.indexValue >= '2017-08-15 10:32:05') OR (idx.indexKey='featured' AND idx.indexValue='true')) AND idx.itemID=idx2.itemID AND idx.itemKey=idx2.itemKey ) as tbl WHERE (productDeleted IS NULL AND productStatus=1 AND parentID IS NULL) GROUP BY itemID, sortval HAVING count(*)=2 ORDER BY sortval ASC
Drew McLellan

Drew McLellan 2638 points
Perch Support

4 years ago

What is featured and are you sure it has a value the same as the string true ?
Toby Martin

Toby Martin 1 points

4 years ago

Sorry drew:

<perch:shop id="featured" type="checkbox" label="Featured On The Homepage" value="true" />

Yes, filtering on just featured works fine
Drew McLellan

Drew McLellan 2638 points
Perch Support

4 years ago

And filtering on just endDate works?
Toby Martin

Toby Martin 1 points

4 years ago

Yes they both work perfectly on their own.
Drew McLellan

Drew McLellan 2638 points
Perch Support

4 years ago

I'd expect them to work together with either one filter mode or the other.