Top Customer widget not working with eloquent users

[byron-roots]

Description

The top customers widget on the overview page is erroring out on the SQL query, which also causes the page to show a widget that is stuck loading.

The current query builder in Overview.php L:205 looks like

$query = $userModel::query()
                        ->where('orders', '!=', null)
                        ->orderBy(function ($query) {
                            $query->selectRaw('JSON_ARRAY_LENGTH(orders)');
                        }, 'desc')
                        ->limit(5)
                        ->get()
                        ->map(function ($model) {
                            return User::fromUser($model);
                        });

Changing

$query->selectRaw('JSON_ARRAY_LENGTH(orders)');

to

$query->selectRaw('JSON_LENGTH(orders)');

Fixes the issue for MySQL, but wouldn't know how it would affect other databases

Steps to reproduce

1. Set up simple commerce
2. Set up eloquent users for customers
3. Make some orders
4. Check the overview page

Environment

Environment
Application Name: Newby Leisure
Laravel Version: 10.20.0
PHP Version: 8.1.22
Composer Version: 2.5.5
Environment: local
Debug Mode: ENABLED
URL: newbyleisure.test/
Maintenance Mode: OFF

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

Drivers
Broadcasting: pusher
Cache: redis
Database: mysql
Logs: stack / single
Mail: smtp
Queue: redis
Session: file

Localization
Installed: en, es
LaravelLang\Attributes\Plugin: v2.4.0
LaravelLang\HttpStatuses\Plugin: v3.4.3
LaravelLang\Lang\Plugin: 13.1.2
Protected Locales: en
Publisher Version: v14.7.0

Simple Commerce
Currencies: GBP, EUR
Gateways: Stripe, Invoice
Repository: Customer: DoubleThreeDigital\SimpleCommerce\Customers\UserCustomerRepository
Repository: Order: App\Repositories\EloquentEntryOrderRepository
Repository: Product: DoubleThreeDigital\SimpleCommerce\Products\EntryProductRepository
Shipping Methods: Free Shipping
Tax Engine: DoubleThreeDigital\SimpleCommerce\Tax\BasicTaxEngine

Statamic
Addons: 13
Antlers: runtime
Stache Watcher: Disabled
Static Caching: Disabled
Version: 4.18.0 PRO

Statamic Addons
appswithlove/statamic-one-click-content-translation: 4.18.1
aryehraber/statamic-logbook: 3.1.0
doublethreedigital/runway: 5.2.0
doublethreedigital/simple-commerce: 5.3.5
jacksleight/statamic-bard-texstyle: 3.1.3
jonassiewertsen/statamic-livewire: 2.12.0
nineteensquared/instagram: 1.2.0
rootstudio/cache-primer: 0.9
rootstudio/monitor: 0.2
rootstudio/mux-video: 0.5
statamic/collaboration: 0.7.2
statamic/eloquent-driver: 2.6.1
stillat/relationships: 2.1.1

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

[duncanmcclean]

JSON_ARRAY_LENGTH works fine for me on MySQL 8.0.32 - which version of MySQL are you using?

[duncanmcclean]

Since I've not heard back from you in the last few weeks, I'm going to close this issue now.

[de-raaf-media]

Hi Duncan,

I just encountered the exact same issue.

select * from 'users' where 'orders' is not null ORDER BY (SELECT JSON_ARRAY_LENGTH('orders')) DESC LIMIT 5

results in an error:

FUNCTION [database name].JSON_ARRAY_LENGTH does not exist.

I think JSON_LENGTH does the same?

MySQL 8.0.33 is my version on OSX.

Mark

[duncanmcclean]

Unfortunately, JSON_LENGTH doesn't exist in SQLite so we can't do that (unless there's a way to figure out which database engine is being used).

I'm still confused as to why JSON_ARRAY_LENGTH works for me on MySQL 8.0.32 but not on MySQL 8.0.33 for you.

[de-raaf-media]

I cannot find this in the official documentation: https://dev.mysql.com/doc/search/?d=201&p=1&q=JSON_ARRAY_LENGTH

Did you maybe install a plugin for this?

[duncanmcclean]

I haven't installed anything on top of mysql from Homebrew. I'll do some research and take a look at this when I have time.

In the meantime, you can disable the "Top Customers" widget on the Overview page.

[de-raaf-media]

Thanks @duncanmcclean

[duncanmcclean]

I've just tagged a release which should hopefully fix this for you (PR: #977). I've replaced JSON_ARRAY_LENGTH with JSON_LENGTH so hopefully it'll work for you both now.

[de-raaf-media]

Thanks @duncanmcclean!