Problems with order queries for users who have never logged in

[cdfwebsolutions]

Description

Filtering orders by user fails if the user has never logged in and has no associated orders. Instead of returning no results, the query returns all orders. This is a problem when we attempt to check if a user has any orders and the user has never logged in.

This behavior can be seen in both an order query using the user parameter (per https://docs.craftcms.com/commerce/v2/dev/element-queries/order-queries.html#users) and on the Customer Info tab (which is blank instead of displaying the usual "No orders exist for this user yet." message).

Steps to reproduce

(Assumes we already have a number of Commerce orders in the CMS.)

1. Create a new user with no orders, e.g.: user ID # 999
2. Run the following:

{% set user = craft.users()
    .id(999)
    .one()
%}

{% set orders = craft.orders()
    .user(user)
    .count()
 %}

The above outputs the total # of orders in the CMS. If we change '999' to a user's ID that has logged in but has no orders, it returns 0. And, if we set the user ID to a user with orders, the correct # of orders is retrieved. (Likewise, using .all() returns all orders.)

3. Go to /admin/users/999#customerInfo - this will be blank instead of having the expected "Orders - No orders exist for this user yet.".

Additional info

• Craft version: 3.3.18.2
• Commerce version: 2.2.15
• PHP version: 7.2.26
• Database driver & version: MySQL 5.6.45

[nfourtythree]

Hey @cdfwebsolutions

Just before we dig into this. Can I check that in your example:

{% set orders = craft.orders()
    .user(user)
    .count()
 %}

The variable user is actually a user element and not null? As passing null would result in the behaviour you have explained.

Remember that when querying for a user it will only return active users unless you specify otherwise. For example:

{% set orders = craft.users
    .id(999)
	.status(['active', 'pending'])
    .one()
 %}

[cdfwebsolutions]

Yes, user is actually a user element. For example, {{ user.fullName }} returns "Jane Doe" (for this test user). All of these users are activated. The only thing that seems to distinguish them (i.e., the ones that do not work with the orders query) is that they have never logged in.

[cdfwebsolutions]

We have noticed in outputting the raw query generated by craft.orders() that the WHERE ('commerce_orders'.'customerId'='X') does not get set when the user has never logged in. For users that have logged in but have no orders, there is still a customerId in the query. I'm not sure how these customer IDs get created, but perhaps there is an issue with creating customer records for users that have never logged in. Again, the user element is valid and active in these cases.

[nfourtythree]

Ah, I think I follow now @cdfwebsolutions.

So what is likely happening here is that this user doesn't have a customer record as they have never logged in/ordered (a "customer" is only created when an ordered is placed or a cart saved.

As a temporary solution in your code you can wrap your query in:

{% set customerId = craft.commmerce.customers.getCustomerByUserId(user.id) %}
{% if customerId %}
	...
{% endif %}

This will check to see if they have a customer record. This can be a stop-gap until we rework the user() portion of the Order query.

[cdfwebsolutions]

Thanks, yes, that does fix things for now :) Just for clarity, does a customer record get created on first login or just when a cart is saved or order placed? We have some users with 0 orders (who show up correctly), but perhaps they started the order process and saved a cart.

[nfourtythree]

@cdfwebsolutions it is when a cart is saved.

Having said all that, I have just pushed an update that makes sure all users have a customer record no matter if they have ordered or not.

So this should not be an issue in the future. This will be included in the next release.