Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Database error when performing an Order query containing a date_query and a product_id #9699

Open
jkudish opened this issue Nov 28, 2023 · 0 comments
Labels
type-bug workflow-needs-replication workflow-pending-triage Items that need to be triaged and reviewed before they are worked on.

Comments

@jkudish
Copy link

jkudish commented Nov 28, 2023

Bug Report

Expected behavior

Performing a Order query containing a date_query and a product_id should return results.

This bug is similar to the one reported in #7920 and fixed in #7951. However the solution that was applied there was specific to the exports page (which performs a custom SQL query) and did not fix the issue when performing a query using the EDD\Database\Queries\Order query object.

Actual behavior

Performing a Order query containing a date_query and a product_id produces the following database error: WordPress database error Column 'date_created' in where clause is ambiguous for query SELECT...

Steps to reproduce the behavior

Perform a query similar to this:

$query = array(
    'product_id' => 1
    'date_query' => array(
        array(
            'after'     => '2023-11-01 00:00:00',
            'before'    => '',
            'inclusive' => true,
        ),
    ),
);

// this will produce a database error:
// WordPress database error Column 'date_created' in where clause is ambiguous for query SELECT ... 
// this is due to the JOIN on the order items table
$orders = edd_get_orders( $query );

For ease of reproduction I've made a minimum viable reproduction plugin in the form of a WP_CLI command availalble here: https://gist.github.com/jkudish/e5cd9254abfa4e4903dd837c72513c8f

Information (if a specific version is affected):

PHP Version: any
EDD Version (or branch): 3.0+
WordPress Version: any

I have submitted #9700 as an attempted fix for this bug

@jkudish jkudish added type-bug workflow-needs-replication workflow-pending-triage Items that need to be triaged and reviewed before they are worked on. labels Nov 28, 2023
jkudish added a commit to jkudish/edd-export-tool that referenced this issue Nov 28, 2023
implement filtering by product ID or product price ID

while implementing this, I discovered a bug in the EDD query system. Specifically, when using `date_query` in combination with `product_id`, the query produces an invalid query error "WordPress database error Column 'date_created' in where clause is ambiguous for query" because the `date_query` is not properly scoped to the `edd_orders` table.

This bug has been reported here: awesomemotive/easy-digital-downloads#9699
and I will work on a PR to fix the issue.

A workaround in the form of applying the more specific `date_created_query` has been implemented as a workaround for now
jkudish added a commit to jkudish/easy-digital-downloads that referenced this issue Nov 28, 2023
use the received table name & table alias arguments in the `get_sql` function to set those as properties on the Date class.

Then, when building the SQL query, use the alias or name (only if available) to prepend to the column name in the query.

This prevents and fixes the issue described in awesomemotive#9699
jkudish added a commit to jkudish/easy-digital-downloads that referenced this issue Nov 28, 2023
use the received table name & table alias arguments in the `get_sql` function to set those as properties on the Date class.

Then, when building the SQL query, use the alias or name (only if available) to prepend to the column name in the query.

This prevents and fixes the issue described in awesomemotive#9699
jkudish added a commit to jkudish/easy-digital-downloads that referenced this issue Nov 28, 2023
use the received table name & table alias arguments in the `get_sql` function to set those as properties on the Date class.

Then, when building the SQL query, use the alias or name (only if available) to prepend to the column name in the query.

This prevents and fixes the issue described in awesomemotive#9699
jkudish added a commit to jkudish/core that referenced this issue Dec 5, 2023
use the received table name & table alias arguments in the `get_sql` function to set those as properties on the Date class.

Then, when building the SQL query, use the alias or name (only if available) to prepend to the column name in the query.

This prevents and fixes the issue described in awesomemotive/easy-digital-downloads#9699
JJJ pushed a commit to berlindb/core that referenced this issue Sep 19, 2024
* Use the received table name & table alias arguments in the `get_sql` function to set those as properties on the Date class.
* Then, when building the SQL query, use the alias or name (only if available) to prepend to the column name in the query.
* This prevents and fixes the issue described in awesomemotive/easy-digital-downloads#9699
* Update get_sql params as per WP_Meta_Query
* Update get_column to only use the table_name
* Removed the table alias as that's not something WP_Meta_Query uses
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
type-bug workflow-needs-replication workflow-pending-triage Items that need to be triaged and reviewed before they are worked on.
Projects
None yet
Development

No branches or pull requests

1 participant