Prerequisites

• I have carried out troubleshooting steps and I believe I have found a bug.
• I have searched for similar bugs in both open and closed issues and cannot find a duplicate.

Describe the bug

I am not sure if this is a bug. But I've created this report for an issue reported at 5195308-zen. (The categories names never load up on reports under Analytics > Categories)

Either their category IDs are messed up, or it might be a bug with how data is generated in Analytics API for categories.

When we see reports for Categories in Analytics, the report page makes a call to URL:

/wp-json/wc-analytics/reports/categories?orderby=items_sold&order=desc&after=2022-05-01T00%3A00%3A00&before=2022-05-20T23%3A59%3A59&page=1&per_page=25&extended_info=true&_locale=user

Which in turn uses woocommerce/src/Admin/API/Reports/Categories/DataStore.php to generate the data.

The file above creates a DB query to get category IDs in order to pull category related information such as category name, etc.

For the case in 5195308-zen - The category names are not loading up, below is the view of WC Ajax call:

Next, the DB query that was used to generate the above response data looks roughly like the below:

Looking at the database table of 5195308-zen, _term_taxonomy.term_taxonomy_id would return 359 while the term ID is different.

Looking at the _terms table, the category exists:

Next, the category name is pulled from Line number: 197 $extended_info['name'] = get_the_category_by_ID( $category_data['category_id'] );

get_the_category_by_ID function uses get_term function which makes a database call like the below resulting in no results:

I am wondering if _term_taxonomy.term_taxonomy_id could be replaced by _term_taxonomy.term_id in order to solve this? (in function initialize_queries() to be specific)

OR

It is all well, but a 3PD plugin might have messed up their category IDs.

Expected behavior

NA

Actual behavior

NA

Steps to reproduce

1. Buy some products with categories.
2. Go to Analytics > Categories to see the stats.
3. For any specific product category, Change term_id column to a random number in _terms and _term_taxonomy tables in database.
4. Go to Analytics > Categories, and you'll see that the category name is missing.
5. Change _term_taxonomy.term_taxonomy_id to _term_taxonomy.term_id in function initialize_queries() of the file woocommerce/src/Admin/API/Reports/Categories/DataStore.php
6. Be sure to clear analytics categories transient cache entry. (transient_wc_report_categories)
7. Go to Analytics > Categories, and the product category would appear back.

WordPress Environment

`

WordPress Environment

WordPress address (URL):
Site address (URL):
WC Version: 6.5.1
REST API Version: ✔ 6.5.1
WC Blocks Version: ✔ 7.4.3
Action Scheduler Version: ✔ 3.4.0
Log Directory Writable: ✔
WP Version: 5.9.3
WP Multisite: –
WP Memory Limit: 512 MB
WP Debug Mode: –
WP Cron: ✔
Language: en_US
External object cache: –

Server Environment

Server Info: Apache
PHP Version: 7.4.28
PHP Post Max Size: 128 MB
PHP Time Limit: 300
PHP Max Input Vars: 1000
cURL Version: 7.81.0
OpenSSL/1.1.1m

SUHOSIN Installed: –
MySQL Version: 5.7.23-23
Max Upload Size: 128 MB
Default Timezone is UTC: ✔
fsockopen/cURL: ✔
SoapClient: ✔
DOMDocument: ✔
GZip: ✔
Multibyte String: ✔
Remote Post: ✔
Remote Get: ✔

Database

WC Database Version: 6.5.1
WC Database Prefix: wpfh_
Total Database Size: 18.06MB
Database Data Size: 10.13MB
Database Index Size: 7.93MB
wpfh_woocommerce_sessions: Data: 0.09MB + Index: 0.02MB + Engine InnoDB
wpfh_woocommerce_api_keys: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_woocommerce_attribute_taxonomies: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_woocommerce_downloadable_product_permissions: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wpfh_woocommerce_order_items: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_woocommerce_order_itemmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_woocommerce_tax_rates: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wpfh_woocommerce_tax_rate_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_woocommerce_shipping_zones: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpfh_woocommerce_shipping_zone_locations: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_woocommerce_shipping_zone_methods: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpfh_woocommerce_payment_tokens: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_woocommerce_payment_tokenmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_woocommerce_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_actionscheduler_actions: Data: 0.13MB + Index: 0.14MB + Engine InnoDB
wpfh_actionscheduler_claims: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_actionscheduler_groups: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_actionscheduler_logs: Data: 0.14MB + Index: 0.11MB + Engine InnoDB
wpfh_advanced_notifications: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpfh_advanced_notification_triggers: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpfh_afwc_campaigns: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpfh_afwc_commission_plans: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpfh_afwc_hits: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpfh_afwc_payouts: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_afwc_payout_orders: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_afwc_referrals: Data: 0.02MB + Index: 0.08MB + Engine InnoDB
wpfh_check_email_log: Data: 0.19MB + Index: 0.00MB + Engine InnoDB
wpfh_commentmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_comments: Data: 0.02MB + Index: 0.09MB + Engine InnoDB
wpfh_followup_coupons: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_followup_coupon_logs: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wpfh_followup_customers: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wpfh_followup_customer_carts: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_followup_customer_notes: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_followup_customer_orders: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_followup_email_excludes: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wpfh_followup_email_logs: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wpfh_followup_email_orders: Data: 0.02MB + Index: 0.09MB + Engine InnoDB
wpfh_followup_email_tracking: Data: 0.02MB + Index: 0.08MB + Engine InnoDB
wpfh_followup_followup_history: Data: 0.08MB + Index: 0.05MB + Engine InnoDB
wpfh_followup_order_categories: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_followup_order_items: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wpfh_followup_subscribers: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_followup_subscribers_to_lists: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_followup_subscriber_lists: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_gla_budget_recommendations: Data: 0.22MB + Index: 0.14MB + Engine InnoDB
wpfh_gla_merchant_issues: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpfh_gla_shipping_rates: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_gla_shipping_times: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_options: Data: 3.02MB + Index: 0.09MB + Engine InnoDB
wpfh_postmeta: Data: 2.52MB + Index: 1.91MB + Engine InnoDB
wpfh_posts: Data: 0.19MB + Index: 0.11MB + Engine InnoDB
wpfh_termmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_terms: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_term_relationships: Data: 0.05MB + Index: 0.02MB + Engine InnoDB
wpfh_term_taxonomy: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_tm_taskmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_tm_tasks: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_usermeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_users: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wpfh_wc_admin_notes: Data: 0.05MB + Index: 0.00MB + Engine InnoDB
wpfh_wc_admin_note_actions: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_wc_category_lookup: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpfh_wc_customer_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_wc_download_log: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_wc_order_coupon_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_wc_order_product_lookup: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wpfh_wc_order_stats: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wpfh_wc_order_tax_lookup: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_wc_points_rewards_user_points: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_wc_points_rewards_user_points_log: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wpfh_wc_product_attributes_lookup: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_wc_product_download_directories: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_wc_product_meta_lookup: Data: 0.05MB + Index: 0.09MB + Engine InnoDB
wpfh_wc_rate_limits: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_wc_reserved_stock: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpfh_wc_tax_rate_classes: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_wc_warranty_products: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wpfh_wc_webhooks: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_woocommerce_gc_activity: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wpfh_woocommerce_gc_cards: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wpfh_woocommerce_gc_cardsmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_woocommerce_prl_deploymentmeta: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_woocommerce_prl_deployments: Data: 0.02MB + Index: 0.06MB + Engine InnoDB
wpfh_woocommerce_prl_frequencies: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_woocommerce_prl_tracking_clicks: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wpfh_woocommerce_prl_tracking_conversions: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wpfh_woocommerce_prl_tracking_views: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_woocommerce_square_customers: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpfh_wpfm_backup: Data: 0.02MB + Index: 0.00MB + Engine InnoDB
wpfh_wps_hit: Data: 0.02MB + Index: 0.14MB + Engine InnoDB
wpfh_wps_index: Data: 1.52MB + Index: 2.17MB + Engine InnoDB
wpfh_wps_key: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_wps_object_term: Data: 0.06MB + Index: 0.09MB + Engine InnoDB
wpfh_wps_object_type: Data: 0.02MB + Index: 0.08MB + Engine InnoDB
wpfh_wps_query: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_wps_uri: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_wps_user_agent: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_wp_phpmyadmin_extension__errors_log: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_yoast_indexable: Data: 0.02MB + Index: 0.09MB + Engine InnoDB
wpfh_yoast_indexable_hierarchy: Data: 0.02MB + Index: 0.05MB + Engine InnoDB
wpfh_yoast_migrations: Data: 0.02MB + Index: 0.02MB + Engine InnoDB
wpfh_yoast_primary_term: Data: 0.02MB + Index: 0.03MB + Engine InnoDB
wpfh_yoast_seo_links: Data: 0.02MB + Index: 0.02MB + Engine InnoDB

Post Type Counts

addify_acr_carts: 2
attachment: 4
customize_changeset: 6
fme_cart_discount: 3
follow_up_email: 2
global_product_addon: 2
nav_menu_item: 1
page: 12
post: 4
prl_engine: 4
product: 75
product_variation: 126
revision: 23
shop_coupon: 2
shop_order: 29
shop_order_refund: 3
warranty_request: 3
wcpf_item: 4
wcpf_project: 1
wp_global_styles: 1

Security

Secure connection (HTTPS): ✔
Hide errors from visitors: ✔

Active Plugins (7)

Quantity Discounts & Pricing For Woocommerce: by Plugify – 2.0.1
WooCommerce Stripe Gateway: by WooCommerce – 6.3.0
WooCommerce Square: by WooCommerce – 3.0.2
WooCommerce: by Automattic – 6.5.1
WP Crontrol: by John Blackbourn & crontributors – 1.12.1
WP File Manager: by mndpsingh287 – 7.1.5
WP phpMyAdmin: by Puvox.software – 5.2.0.1

Inactive Plugins (66)

Abandoned Cart Recovery: by Addify – 1.2.4
Affiliate For WooCommerce: by StoreApps – 4.5.0
Age Verification Popup: by KoalaApps – 1.0.2
Akismet Anti-Spam: by Automattic – 4.2.3
Avada Builder: by ThemeFusion – 3.4
Avada Core: by ThemeFusion – 5.4
Bulk Discounts for WooCommerce: by Plugify – 1.0.4
Cart Discounts For Woocommerce: by FME ADDONS – 1.0.1
Check & Log Email: by WPChill – 1.0.6
Classic Editor: by WordPress Contributors – 1.6.2
Classic Widgets: by WordPress Contributors – 0.3
Database Management tool - Adminer: by Pexle Chris – 1.0.0
Direct Checkout Options Pro For Woocommerce: by Plugify – 1.0.4
Follow-Up Emails: by WooCommerce – 4.9.20 (update to version 4.9.22 is available)
Google Listings and Ads: by WooCommerce – 1.12.8
Gravity Forms: by Gravity Forms – 2.5.16.2
Hello Dolly: by Matt Mullenweg – 1.7.2
Jetpack: by Automattic – 10.9
Loco Translate: by Tim Whitlock – 2.6.1
Lottery for WooCommerce: by FantasticPlugins – 5.6
MOJO Marketplace: by Mike Hansen – 1.5.3
My Database Admin: by wpshrike – 1.1.23
OptinMonster: by OptinMonster Popup Builder Team – 2.6.12
Product Filters for WooCommerce: by WooCommerce – 1.3.0 (update to version 1.3.1 is available)
Quick Checkout for WooCommerce: by FantasticPlugins – 1.4
Storefront Powerpack: by WooCommerce – 1.6.1
Under Construction: by Noah Kagan – 1.20
User Role Editor: by Vladimir Garagulya – 4.62
WooCommerce - ShipStation Integration: by WooCommerce – 4.1.48
WooCommerce Admin: by WooCommerce – 3.3.2
WooCommerce Advanced Notifications: by WooCommerce – 1.2.36 (update to version 1.2.37 is available)
WooCommerce Australia Post Shipping: by WooCommerce – 2.4.32
WooCommerce Blocks: by Automattic – 7.6.0
WooCommerce Bookings: by WooCommerce – 1.15.50 (update to version 1.15.56 is available)
WooCommerce Bookings Availability: by WooCommerce – 1.1.19
WooCommerce Brands: by WooCommerce – 1.6.29 (update to version 1.6.31 is available)
WooCommerce Cart Add-Ons: by WooCommerce – 2.1.0 (update to version 2.2.0 is available)
WooCommerce Conditional Shipping and Payments: by WooCommerce – 1.12.1 (update to version 1.13.0 is available)
WooCommerce Conversion Tracking: by weDevs – 2.0.9
WooCommerce Conversion Tracking Pro: by weDevs – 1.0.7
WooCommerce Dynamic Pricing: by Element Stark – 3.1.27
WooCommerce Gift Cards: by WooCommerce – 1.11.0
WooCommerce Gravity Forms Product Add-Ons: by Element Stark – 3.3.26
WooCommerce Measurement Price Calculator: by SkyVerge – 3.20.1
WooCommerce One Page Checkout: by Automattic – 1.9.3 (update to version 1.9.4 is available)
WooCommerce Order Test: by WP Fix It – 1.6
WooCommerce Payments: by Automattic – 4.1.0
WooCommerce PayPal Payments: by WooCommerce – 1.8.0
WooCommerce Photography: by WooCommerce – 1.0.29
WooCommerce Points and Rewards: by WooCommerce – 1.7.6 (update to version 1.7.8 is available)
WooCommerce Pre-Orders: by WooCommerce – 1.7.1
WooCommerce Product Add-ons: by WooCommerce – 4.9.0
WooCommerce Product CSV Import Suite: by WooCommerce – 1.10.46 (update to version 1.10.48 is available)
WooCommerce Product Recommendations: by WooCommerce – 1.4.18 (update to version 2.0.1 is available)
WooCommerce Product Search: by itthinx – 4.4.0 (update to version 4.6.0 is available)
WooCommerce Shipping & Tax: by WooCommerce – 1.25.28
WooCommerce Subscribe to Newsletter: by Themesquad – 3.3.6
WooCommerce Subscriptions: by WooCommerce – 4.0.1 (update to version 4.2.0 is available)
WooCommerce USPS Shipping: by WooCommerce – 4.4.70 (update to version 4.4.73 is available)
WooCommerce Warranty Requests: by WooCommerce – 1.9.33
WooCommerce Xero Integration: by WooCommerce – 1.7.42
WP-Optimize - Clean, Compress, Cache: by David Anderson
Ruhani Rabin
Team Updraft – 3.2.3

WPForms Lite: by WPForms – 1.7.4.1
WP Reset: by WebFactory Ltd – 1.95
WP Rollback: by GiveWP.com – 1.7.1
Yoast SEO: by Team Yoast – 18.8

Must Use Plugins (1)

Endurance Page Cache: by Mike Hansen – 2.2

Settings

API Enabled: –
Force SSL: –
Currency: INR (₹)
Currency Position: left
Thousand Separator: ,
Decimal Separator: .
Number of Decimals: 2
Taxonomies: Product Types: external (external)
grouped (grouped)
simple (simple)
subscription (subscription)
variable (variable)
variable subscription (variable-subscription)

Taxonomies: Product Visibility: exclude_from_catalog (exclude_from_catalog)
exclude_from_search (exclude_from_search)
exclude-from-catalog (exclude-from-catalog)
exclude-from-search (exclude-from-search)
featured (featured)
outofstock (outofstock)
rated-1 (rated-1)
rated-2 (rated-2)
rated-3 (rated-3)
rated-4 (rated-4)
rated-5 (rated-5)

Connected to WooCommerce.com: ✔
Enforce Approved Product Download Directories: –

WC Pages

Shop base: #6 - /shop/
Cart: #7 - /cart/
Checkout: #8 - /checkout/
My account: #9 - /my-account/
Terms and conditions: #2 - /sample-page/

Theme

Name: Storefront
Version: 4.1.0
Author URL: https://woocommerce.com/
Child Theme: ❌ – If you are modifying WooCommerce on a parent theme that you did not build personally we recommend using a child theme. See: How to create a child theme
WooCommerce Support: ✔

Templates

Overrides: –

Action Scheduler

Complete: 397
Oldest: 2022-05-18 06:59:06 +0000
Newest: 2022-05-20 10:31:29 +0000

Pending: 3
Oldest: 2022-05-20 10:32:51 +0000
Newest: 2022-05-21 04:55:26 +0000

Canceled: 1
Oldest: 2022-05-16 13:05:08 +0000
Newest: 2022-05-16 13:05:08 +0000

Status report information

Generated at: 2022-05-20 10:32:44 +00:00
`

Isolating the problem

• I have deactivated other plugins and confirmed this bug occurs when only WooCommerce plugin is active.
• This bug happens with a default WordPress theme active, or Storefront.
• I can reproduce this bug consistently using the steps above.