Forum

Thread tagged as: Problem, Runway, Shop

Shop - sort by current price

Hi,

I am trying to sort by current_price but I am getting no results returned. This is my page code:

perch_shop_products([
   'sort' => 'current_price',
   'sort-order' => 'ASC',
   'sort-type' => 'numeric'
]);

I haven't updated to Perch 3 yet as I will need to do onboarding etc. with this client and they can't fit that in currently. If you could still offer some support that would be great. This is what I am getting in Debug:

    [nil] SELECT tbl.* FROM ( SELECT idx.itemID, main.*, idx2.indexValue as sortval FROM watches_shop_index idx JOIN watches_shop_products main ON idx.itemID=main.productID AND idx.itemKey='productID' JOIN watches_shop_index idx2 ON idx.itemID=idx2.itemID AND idx.itemKey='productID' AND idx2.indexKey='current_price' WHERE 1=1 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 ORDER BY sortval ASC

This is my diagnostics:

PERCH INFORMATION

Perch Runway: 2.8.34
Production mode: Development (10)
Installed apps: content (2.8.34), assets (2.8.34), categories (2.8.34), perch_blog (5.0), perch_forms (1.8.3), perch_shop_orders (1.0.11), perch_shop_products (1.0.11), perch_shop (1.0.11), perch_members (1.5), perch_mailchimp (3.0.1), perch_twitter (3.6.2)
DB driver: PDO
DB tables: watches_backup_plans (1), watches_backup_resources (484), watches_backup_runs (105), watches_blog_authors (6), watches_blog_comments (0), watches_blog_index (1444), watches_blog_posts (41), watches_blog_posts_to_tags (0), watches_blog_sections (1), watches_blog_tags (0), watches_blogs (1), watches_categories (23), watches_category_counts (5), watches_category_sets (4), watches_collection_index (0), watches_collection_items (0), watches_collection_revisions (0), watches_collections (0), watches_content_index (4622), watches_content_items (177), watches_content_regions (45), watches_forms (1), watches_forms_responses (5), watches_mailchimp_campaigns (9), watches_mailchimp_imports (0), watches_mailchimp_lists (1), watches_mailchimp_subscribers (163), watches_mailchimp_subscriptions (163), watches_mailchimp_webhooks (4), watches_members (555), watches_members_forms (4), watches_members_member_tags (0), watches_members_sessions (5), watches_members_tags (2), watches_navigation (0), watches_navigation_pages (0), watches_page_routes (25), watches_page_templates (33), watches_pages (42), watches_resource_log (5119), watches_resource_tags (0), watches_resources (7486), watches_resources_to_tags (0), watches_scheduled_tasks (40), watches_settings (42), watches_shop_addresses (3655), watches_shop_admin_index (74), watches_shop_brands (1), watches_shop_cart (174207), watches_shop_cart_data (393), watches_shop_cart_items (1804), watches_shop_countries (250), watches_shop_currencies (152), watches_shop_customers (762), watches_shop_emails (1), watches_shop_index (10249), watches_shop_option_values (0), watches_shop_options (0), watches_shop_order_items (1126), watches_shop_order_promotions (228), watches_shop_order_statuses (11), watches_shop_orders (543), watches_shop_orders_meta (1), watches_shop_product_files (0), watches_shop_product_option_values (0), watches_shop_product_options (0), watches_shop_product_tags (0), watches_shop_products (77), watches_shop_promotions (7), watches_shop_search (84), watches_shop_shipping_zone_countries (206), watches_shop_shipping_zones (12), watches_shop_shippings (2), watches_shop_tax_exhibits (1753), watches_shop_tax_group_rates (1), watches_shop_tax_groups (2), watches_shop_tax_locations (1), watches_shop_tax_rates (1), watches_shop_variants (0), watches_twitter_scheduled_tweets (0), watches_twitter_settings (1), watches_twitter_tweets (0), watches_user_passwords (1), watches_user_privileges (81), watches_user_role_privileges (16), watches_user_roles (2), watches_users (13)
Users: 13
PHPMailer: 5.2.21
App runtimes:
<?php
    $apps_list = array(
        'content',
        'categories',
        'perch_forms',
        'perch_mailchimp',
        'perch_twitter',
        'perch_members',
        'perch_shop',
        'perch_blog'
    );
Scheduled tasks for perch_mailchimp: import_data (1 mins)
Scheduled tasks for perch_twitter: post_tweets (1 mins)
Scheduled tasks for perch_blog: delete_spam_comments (1440 mins)
Scheduled tasks for Backup: plan_1 (10 mins)
Editor plug-ins: markitup
H1: 83b5391d6988783b81e346ece1791a1f
L1: 5a46b657baffcbfca32f8fe1d2036f54
F1: 6a33f95eca3667f9e0c39bf5ca2980fe
headerColour: #ca0000
content_singlePageEdit: 1
helpURL:
hideBranding: 1
content_collapseList: 1
lang: en-gb
update_2.8.31: done
perch_twitter_update: 3.5
headerScheme: dark
update_runway_2.8.31: done
latest_version: 2.8.15
on_sale_version:
hide_pwd_reset: 1
dashboard: 0
content_hideNonEditableRegions: 0
content_frontend_edit: 0
perch_shop_update: 1.0.11
perch_mailchimp_api_key: b8e0b18debe717ba1efe75f01703f85b-us12
perch_mailchimp_campaign_url: /mailchimp/campaign/{campaignSlug}
perch_shop_price_tax_mode: inc
perch_shop_trade_price_tax_mode: exc
perch_shop_site_url: https://newgatewatches.com
perch_shop_product_url: /shop/products/{slug}
perch_shop_default_currency: 47
perch_shop_reporting_currency: 47
perch_shop_invoice_number_format: #%d
perch_members_login_page: /members/login.php?r={returnURL}
perch_mailchimp_secret: 1c40981
perch_members_update: 1.4
logoPath: /admin/resources/perch-logo-1.png
perch_blog_update: 5.0.1
perch_blog_post_url: /magazine/{postSlug}
perch_blog_site_name:
perch_blog_slug_format: %Y-%m-%d-{postTitle}
perch_blog_akismet_key:
perch_blog_max_spam_days: 0
perch_blog_comment_notify: 0
update_runway_2.8.32: done
siteURL: https://newgatewatches.com
update_runway_2.8.34: done
PERCH_DEVELOPMENT: 10
PERCH_STAGING: 50
PERCH_PRODUCTION: 100
PERCH_SITEPATH: /Volumes/Data/g Websites/newgatewatches/newgate-watches
PERCH_SCHEDULE_SECRET: UehFD0ambiReSATQ
PERCH_DB_USERNAME: root
PERCH_DB_SERVER: localhost
PERCH_DB_DATABASE: watches
PERCH_EMAIL_METHOD: smtp
PERCH_EMAIL_HOST: mailtrap.io
PERCH_EMAIL_SECURE: tls
PERCH_EMAIL_AUTH: 1
PERCH_EMAIL_PORT: 2525
PERCH_EMAIL_USERNAME: 42004aa368a4f9d32
PERCH_DEBUG: 1
PERCH_PRODUCTION_MODE: 10
PERCH_DB_PREFIX: watches_
PERCH_EMAIL_FROM: sales@newgatewatches.com
PERCH_EMAIL_FROM_NAME: Newgate Watches
PERCH_LOGINPATH: /admin
PERCH_PATH: /Volumes/Data/g Websites/newgatewatches/newgate-watches/admin
PERCH_CORE: /Volumes/Data/g Websites/newgatewatches/newgate-watches/admin/core
PERCH_RESFILEPATH: /Volumes/Data/g Websites/newgatewatches/newgate-watches/admin/resources
PERCH_RESPATH: /admin/resources
PERCH_HTML5: 1
PERCH_TZ: UTC
PERCH_SSL: 1
PERCH_UNDO_BUFFER: 1
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_PREVIEW_ARG: preview
PERCH_TEMPLATE_PATH: /Volumes/Data/g Websites/newgatewatches/newgate-watches/admin/templates
PERCH_DEFAULT_DOC: index.php
PERCH_DEFAULT_EXT: .php
PERCH_RWD:
PERCH_HTML_ENTITIES:
PERCH_STRIPSLASHES:
PERCH_PROGRESSIVE_FLUSH: 1
PERCH_PARANOID:
PERCH_FORCE_SECURE_COOKIES:
PERCH_PASSWORD_MIN_LENGTH: 6
PERCH_MAX_FAILED_LOGINS: 10
PERCH_AUTH_LOCKOUT_DURATION: 1 HOUR
PERCH_VERIFY_UPLOADS:
PERCH_AUTH_PLUGIN:
PERCH_DB_CHARSET: utf8
PERCH_DB_PORT:
PERCH_DB_SOCKET:
PERCH_SESSION_TIMEOUT_MINS: 20
PERCH_SHOP_VERSION: 1.0.11
PERCH_APPS_EDITOR_PLUGIN: markitup
PERCH_APPS_EDITOR_MARKUP_LANGUAGE: textile
HOSTING SETTINGS

PHP: 7.0.13
Zend: 3.0.0
OS: Darwin
SAPI: apache2handler
Safe mode: not detected
MySQL client: mysqlnd 5.0.12-dev - 20150407 - $Id: 241ae00989d1995ffcbbf63d579943635faf9972 $
MySQL server: 5.6.34
Free disk space: 784.69 GB
Extensions: Core, date, 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, PDO, pdo_mysql, pdo_sqlite, Phar, posix, Reflection, mysqli, SimpleXML, soap, sockets, exif, tokenizer, wddx, xml, xmlreader, xmlwriter, xsl, zip, apache2handler, imap, gettext, mcrypt, pgsql, pdo_pgsql, igbinary, memcached
GD: Yes
ImageMagick: No
PHP max upload size: 128M
PHP max form post size: 128M
PHP memory limit: 128M
Total max uploadable file size: 128M
Resource folder writeable: Yes
Session timeout: 24 minutes
Native JSON: Yes
Filter functions: Yes
Transliteration functions: Yes
HTTPS: on
SSL_TLS_SNI: newgatewatches.dev
SSL_SERVER_S_DN_C: GB
SSL_SERVER_S_DN_ST: Shropshire
SSL_SERVER_S_DN_L: Oswestry
SSL_SERVER_S_DN_O: Newgate Clocks Ltd
SSL_SERVER_S_DN_CN: newwatches.dev
SSL_SERVER_S_DN_Email: mike.harrison@newgateclocks.com
SSL_SERVER_I_DN_C: GB
SSL_SERVER_I_DN_ST: Shropshire
SSL_SERVER_I_DN_L: Oswestry
SSL_SERVER_I_DN_O: Newgate Clocks Ltd
SSL_SERVER_I_DN_CN: newwatches.dev
SSL_SERVER_I_DN_Email: mike.harrison@newgateclocks.com
SSL_VERSION_INTERFACE: mod_ssl/2.2.31
SSL_VERSION_LIBRARY: OpenSSL/1.0.2j
SSL_PROTOCOL: TLSv1.2
SSL_SECURE_RENEG: true
SSL_COMPRESS_METHOD: NULL
SSL_CIPHER: ECDHE-RSA-AES128-GCM-SHA256
SSL_CIPHER_EXPORT: false
SSL_CIPHER_USEKEYSIZE: 128
SSL_CIPHER_ALGKEYSIZE: 128
SSL_CLIENT_VERIFY: NONE
SSL_SERVER_M_VERSION: 1
SSL_SERVER_M_SERIAL: B075F1C2C929BA54
SSL_SERVER_V_START: Aug 8 10:04:30 2016 GMT
SSL_SERVER_V_END: Aug 6 10:04:30 2026 GMT
SSL_SERVER_S_DN: /C=GB/ST=Shropshire/L=Oswestry/O=Newgate Clocks Ltd/CN=newwatches.dev/emailAddress=mike.harrison@newgateclocks.com
SSL_SERVER_I_DN: /C=GB/ST=Shropshire/L=Oswestry/O=Newgate Clocks Ltd/CN=newwatches.dev/emailAddress=mike.harrison@newgateclocks.com
SSL_SERVER_A_KEY: rsaEncryption
SSL_SERVER_A_SIG: sha1WithRSAEncryption
SSL_SESSION_ID: 6CA08A13E7472F33D3AB693086961E4C07F323E6D62EC0B94C8047C2CCF09FAF
HTTP_HOST: newgatewatches.dev
HTTP_CONNECTION: keep-alive
HTTP_UPGRADE_INSECURE_REQUESTS: 1
HTTP_USER_AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36
HTTP_ACCEPT: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
HTTP_REFERER: https://newgatewatches.dev/admin/core/settings/diagnostics/
HTTP_ACCEPT_ENCODING: gzip, deflate, sdch, br
HTTP_ACCEPT_LANGUAGE: en-US,en;q=0.8,fr;q=0.6,it;q=0.4
HTTP_COOKIE: cookieconsent_dismissed=yes; signup=viewed; cmsa=1; _ga=GA1.2.731137483.1486479453; PHPSESSID=dfa82638e232fee733d93db2f1e87c7b
PATH: /usr/bin:/bin:/usr/sbin:/sbin
SERVER_SOFTWARE: Apache
SERVER_NAME: newgatewatches.dev
SERVER_ADDR: ::1
SERVER_PORT: 443
REMOTE_ADDR: ::1
DOCUMENT_ROOT: /Volumes/Data/g Websites/newgatewatches/newgate-watches
SERVER_ADMIN: you@example.com
SCRIPT_FILENAME: /Volumes/Data/g Websites/newgatewatches/newgate-watches/admin/core/settings/diagnostics/index.php
REMOTE_PORT: 61413
GATEWAY_INTERFACE: CGI/1.1
SERVER_PROTOCOL: HTTP/1.1
REQUEST_METHOD: GET
QUERY_STRING: extended
REQUEST_URI: /admin/core/settings/diagnostics/?extended
SCRIPT_NAME: /admin/core/settings/diagnostics/index.php
PHP_SELF: /admin/core/settings/diagnostics/index.php
REQUEST_TIME_FLOAT: 1491226977.55
REQUEST_TIME: 1491226977
argc: 1

Thanks for any help
Mike Harrison

Mike Harrison 37 points

  • 4 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

4 years ago

Try using price instead.
Mike Harrison

Mike Harrison 37 points

4 years ago

I am trying to do a customer-controlled price sort. At the moment I am using price as you suggest. This means items on sale are appearing in that sort where their full price would place them, rather than their sale_price. current_price looked to be what I was after as it always shows the active price.
Drew McLellan

Drew McLellan 2638 points
Perch Support

4 years ago

Current price (as the name suggests) is calculated and not stored. It can't be pre-indexed, as we don't know when it will be used and therefore if it's current.
Mike Harrison

Mike Harrison 37 points

4 years ago

OK thanks Drew - I thought as it was on the showall list it was available to be used.

Would you have a suggestion of how to show a listing of products, sorted by price, that takes into account those where the sale price is active and shows them in the correct place?
Drew McLellan

Drew McLellan 2638 points
Perch Support

4 years ago

showall shows you what's available to the template. That includes anything that's retrieved, and is supplemented with runtime calculated values (like perch_item_index, for example).

I'm not sure I have a good recommendation for that. It might be worth us speccing out the use case and seeing what solution can be found.
Mike Harrison

Mike Harrison 37 points

4 years ago

Ah I see - thanks.

The use case for me is as follows:

  • Customer is on my product list view, looking at all the available products.
  • They use my filter form at the top of the page to choose 'Sort by price - low to high'
  • They submit the form, which sets a query string which Perch uses in the page function
  • The products are displayed in order of their active price, taking into account if they are currently on sale

You can see what I am doing here (if you scroll to the bottom you will see the King watch is right at the end based on it's price, despite it being on sale for less so should be at the top):

https://newgatewatches.com/shop?filter=yes&family=&sort=asc&price=&case=&diameter=&strap=

This is what I have on the page at the moment - I tried substituting current_price for price which is what brought me here:

$cart = perch_shop_cart([ 'skip-template' => 'true', ]);
$currencycode = $cart['currency_code'];

$sort = (perch_get('sort'));

if(perch_get('sort')) {
  perch_shop_products([
    'template' => 'products/category_grid',
    'sort' => 'price.' . $currencycode,
    'sort-order' => $sort,
    'sort-type' => 'numeric',
  ]);
}

Thanks for your help

Mike
Drew McLellan

Drew McLellan 2638 points
Perch Support

4 years ago

Ok, thanks.