Filtering where product_price

SimpleCart
#1

Wondering if anyone has attempted to filter a list of simplecart products by their product price? (where product_price < $50 or > $100 for example.

I attempted this using both getResources and pdoResources and neither seem to have access to this custom field with the where query, however if you show the debug log for those snippets product_prices shows up in the results?

Get Resources Call

[[!getResources? 
    &parents=`704,710,716,717,718,719`
    &hideContainers`1`
    &includeTVs=`1` 
    &includeContent=`1` 
    &limit=`0`
    &tpl=`site.scProductOverviewItem`
    &where=`{"product_price:>":"50"}`
    ]]

Fields Available to query:

Array
(
    [tpl] => 
    [tplOdd] => 
    [tplFirst] => 
    [tplLast] => 
    [tplWrapper] => 
    [wrapIfEmpty] => 
    [sortby] => publishedon
    [sortbyTV] => 
    [sortbyTVType] => string
    [sortbyAlias] => 
    [sortbyEscaped] => 0
    [sortdir] => DESC
    [sortdirTV] => DESC
    [limit] => 5
    [offset] => 0
    [tvFilters] => 
    [tvFiltersAndDelimiter] => ,
    [tvFiltersOrDelimiter] => ||
    [depth] => 10
    [parents] => 704,710,716,717,718,719
    [includeContent] => 1
    [includeTVs] => 1
    [includeTVList] => 
    [showHidden] => 
    [showUnpublished] => 
    [showDeleted] => 
    [resources] => 
    [processTVs] => 
    [processTVList] => 
    [prepareTVs] => 1
    [prepareTVList] => 
    [tvPrefix] => tv.
    [idx] => 1
    [first] => 1
    [last] => 5
    [toPlaceholder] => 
    [toSeparatePlaceholders] => 
    [debug] => 1
    [where] => 
    [dbCacheFlag] => 0
    [context] => 
    [tplCondition] => 
    [tplOperator] => ==
    [conditionalTpls] => 
    [odd] => 1
    [id] => 666
    [type] => document
    [contentType] => text/html
    [pagetitle] => SLK002
    [longtitle] => 
    [description] => 
    [alias] => slk002
    [link_attributes] => 
    [published] => 1
    [pub_date] => 
    [unpub_date] => 
    [parent] => 717
    [isfolder] => 
    [introtext] => 
    [content] => 
    [richtext] => 1
    [template] => 14
    [menuindex] => 160
    [searchable] => 1
    [cacheable] => 1
    [createdby] => 2
    [createdon] => 2015-03-03 13:57:50
    [editedby] => 
    [editedon] => 
    [deleted] => 
    [deletedon] => 
    [deletedby] => 
    [publishedon] => 2015-11-18 10:35:25
    [publishedby] => 16
    [menutitle] => 
    [donthit] => 
    [privateweb] => 
    [privatemgr] => 
    [content_dispo] => 
    [hidemenu] => 
    [class_key] => scProductResource
    [context_key] => web
    [content_type] => 1
    [uri] => slk002.html
    [uri_override] => 
    [hide_children_in_tree] => 
    [show_in_tree] => 
    [properties] => Array
        (
            [stercseo] => Array
                (
                    [index] => 1
                    [follow] => 1
                    [sitemap] => 
                    [priority] => 
                    [changefreq] => 
                    [urls] => 
                )

        )

    [product_code] => 
    [product_price] => 0
    [product_image] => assets/images/products/666/slk002.jpg
    [product_stock] => 
    [product_price_formatted] => $ 0.00
    [product_tax_ex] => 0
    [product_tax_in] => 0
    [product_tax_price] => 0
    [product_tax_rate] => 0
    [product_tax_ex_formatted] => $ 0.00
    [product_tax_in_formatted] => $ 0.00
    [product_tax_price_formatted] => $ 0.00
    [product_tax_rate_formatted] => $ 0.00
)
#2

Hi Nick,

Apologies, I overlooked this post.

SimpleCart stores the price in the scProductMeta object, in the price field. The debug information you’re getting includes the fields that are automatically pulled in, but they’re not actually available in the query like that.

I’m not sure what the easiest way to do it is with getResources or pdoResources (personally I’d probably be inclined to write a custom snippet :smiley: ), but you’ll need to convince either of the snippets to join with the meta table (scProductResource has an alias Meta to the scProductMeta object), and filter on Meta.price.

#3

Thanks Mark,

That’s what I ended up doing (writing a custom snippet) - was easier than fiddling around with getresources any longer :smile:

#4

Glad we think alike there :wink:

#5

Hi Nick,

could you provide your solution to give a hint how I can fix it for my project?

Thanks!

#6

I’ll need to go back and find the snippet I wrote to give a code example, but the gist of what I did was query the simplecart_productsmeta object with a couple conditionals and have the results return the ‘resource’ (the id) in a comma separated list (33,44,33) and then used that in a getResources or Query call (which I’ll admit was kind of a lazy way to do it, but it works)

something like:

[[byPrice? &under=`100` &over=`50` &toPlaceholder=`results`]]
[[Query? 
    &_select=`*`
    &id:IN=`[[+results]]`
    &published=`1`
    &_tpl=`site.scProductOverviewItem`
]]

I’ll see if I can find the code snippet.