Collection filtering does not work with eloquent

[sdussaut]

Bug description

Hello!

I am trying to filter some product collection on my site using:

{{ collection:products as="products" paginate="24" price:gt="2000" }}

and it seems broken. Some products with a lower price than the conditon still appear in the results.

It also seems that the collection ordering in the CP is broken too (if I order items by price, the result is… interesting :-) )

You can see the initial discussion about this here: duncanmcclean/simple-commerce#1216

How to reproduce

• Have a statamic installed with eloquent driver and simple commerce installed (products must be stored in DB)
• Try filtering product collection by price on the front-end

Logs

Environment

Environment
Application Name: SiteName
Laravel Version: 12.6.0
PHP Version: 8.4.4
Composer Version: 2.8.6
Environment: local
Debug Mode: ENABLED
URL: site-name.test
Maintenance Mode: OFF
Timezone: UTC
Locale: en

Cache
Config: NOT CACHED
Events: NOT CACHED
Routes: NOT CACHED
Views: CACHED

Drivers
Broadcasting: log
Cache: file
Database: mysql
Logs: stack / single
Mail: log
Queue: sync
Session: file

Storage
public/storage: NOT LINKED

Simple Commerce
Currencies: EUR
Gateways: Dummy
Repository: Customer: DuncanMcClean\SimpleCommerce\Customers\EloquentCustomerRepository
Repository: Order: DuncanMcClean\SimpleCommerce\Orders\EloquentOrderRepository
Repository: Product: DuncanMcClean\SimpleCommerce\Products\EntryProductRepository
Shipping Methods: Free Shipping
Tax Engine: DuncanMcClean\SimpleCommerce\Tax\Standard\TaxEngine

Statamic
Addons: 3
Sites: 1
Stache Watcher: Enabled (auto)
Static Caching: Disabled
Version: 5.52.0 PRO

Statamic Addons
duncanmcclean/simple-commerce: 7.8.1
statamic-rad-pack/runway: 8.3.1
statamic/eloquent-driver: 4.20.2

Statamic Eloquent Driver
Asset Containers: eloquent
Assets: eloquent
Blueprints: eloquent
Collection Trees: eloquent
Collections: eloquent
Entries: eloquent
Forms: eloquent
Global Sets: eloquent
Global Variables: eloquent
Navigation Trees: eloquent
Navigations: eloquent
Revisions: file
Sites: file
Taxonomies: eloquent
Terms: eloquent
Tokens: file

Additional details

Thanks!

[ryanmitchell]

It looks to me that it's doing a string vs int comparison in the database engine itself - which isnt uncommon with JSON fields. An example of one of the workarounds we've done elsewhere is here -

eloquent-driver/src/Entries/EntryQueryBuilder.php

Line 84 in c98eb44

$this->builder->orderByRaw("cast({$actualColumn} as {$castType}) {$direction}");

I suspect the simplest solution might be to use the field mapping functionality this driver provides to make a 'price' field which you store as an int, or alternatively to make a query scope which would then let you apply the casting.

[sdussaut]

Thanks @ryanmitchell! I will look into that and report back if needed.