namespace WPForms\Db\Payments;
* Class for the Payments database queries.
class Queries extends Payment {
* Check if given payment table column has different values.
* @param string $column Column name.
public function has_different_values( $column ) {
$subquery[] = "SELECT $column FROM $this->table_name WHERE 1=1";
$subquery[] = $this->add_secondary_where_conditions();
$subquery = implode( ' ', $subquery );
$query[] = "SELECT $column FROM $this->table_name WHERE 1=1";
$query[] = $this->add_secondary_where_conditions();
$query[] = "AND $column != ( $subquery )";
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
$result = $wpdb->get_var( implode( ' ', $query ) );
return ! empty( $result );
* Check if there is a subscription payment.
public function has_subscription() {
'type' => implode( '|', array_keys( ValueValidator::get_allowed_subscription_types() ) ),
* Retrieve the number of all payments.
* @param array $args Redefine query parameters by providing own arguments.
* @return int Number of payments or count of payments.
public function count_all( $args = [] ) {
// Retrieve the global database instance.
$query[] = 'SELECT SUM(count) AS total_count FROM (';
$query[] = "SELECT COUNT(*) AS count FROM {$this->table_name} as p";
* Add parts to the query for count_all method before the WHERE clause.
* @param string $where Before the WHERE clause in DB query.
* @param array $args Query arguments.
$query[] = apply_filters( 'wpforms_db_payments_queries_count_all_query_before_where', '', $args );
$query[] = $this->add_columns_where_conditions( $args );
$query[] = $this->add_secondary_where_conditions( $args );
* Append custom query parts after the WHERE clause for the count_all method.
* This hook allows external code to extend the SQL query by adding custom conditions
* immediately after the WHERE clause.
* @param string $where After the WHERE clause in the database query.
* @param array $args Query arguments.
$query[] = apply_filters( 'wpforms_db_payments_queries_count_all_query_after_where', '', $args );
$query[] = ') AS counts;';
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
return (int) $wpdb->get_var( implode( ' ', $query ) );
* Whether at least one payment exists with the given arguments.
* @param array $args Optionally, you can redefine query parameters by providing custom arguments.
* @return bool False if no results found.
public function if_exists( $args = [] ) {
// Retrieve the global database instance.
$query[] = "SELECT 1 FROM {$this->table_name}";
* Add parts to the query for if_exists method before the WHERE clause.
* @param string $where Before the WHERE clause in DB query.
* @param array $args Query arguments.
$query[] = apply_filters( 'wpforms_db_payments_queries_count_if_exists_before_where', '', $args );
$query[] = $this->add_columns_where_conditions( $args );
$query[] = $this->add_secondary_where_conditions( $args );
* Append custom query parts after the WHERE clause for the if_exists method.
* This hook allows external code to extend the SQL query by adding custom conditions
* immediately after the WHERE clause.
* @param string $where After the WHERE clause in the database query.
* @param array $args Query arguments.
$query[] = apply_filters( 'wpforms_db_payments_queries_count_if_exists_after_where', '', $args );
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
return (bool) $wpdb->get_var( implode( ' ', $query ) );
* @param int $payment_id Payment ID.
* @param array $args Where conditions.
* @return object|null Object from DB values or null.
public function get_next( $payment_id, $args = [] ) {
if ( empty( $payment_id ) ) {
$query[] = "SELECT * FROM {$this->table_name}";
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$query[] = $wpdb->prepare( "WHERE $this->primary_key > %d", $payment_id );
$query[] = $this->add_secondary_where_conditions( $args );
$query[] = "ORDER BY $this->primary_key LIMIT 1";
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
return $wpdb->get_row( implode( ' ', $query ) );
* @param int $payment_id Payment ID.
* @param array $args Where conditions.
* @return object|null Object from DB values or null.
public function get_prev( $payment_id, $args = [] ) {
if ( empty( $payment_id ) ) {
$query[] = "SELECT * FROM $this->table_name";
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$query[] = $wpdb->prepare( "WHERE $this->primary_key < %d", $payment_id );
$query[] = $this->add_secondary_where_conditions( $args );
$query[] = "ORDER BY $this->primary_key DESC LIMIT 1";
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
return $wpdb->get_row( implode( ' ', $query ) );
* Get previous payments count.
* @param int $payment_id Payment ID.
* @param array $args Where conditions.
public function get_prev_count( $payment_id, $args = [] ) {
if ( empty( $payment_id ) ) {
$query[] = "SELECT COUNT( $this->primary_key ) FROM $this->table_name";
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$query[] = $wpdb->prepare( "WHERE $this->primary_key < %d", $payment_id );
$query[] = $this->add_secondary_where_conditions( $args );
$query[] = "ORDER BY $this->primary_key ASC";
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
return (int) $wpdb->get_var( implode( ' ', $query ) );
* Get subscription payment history for the given subscription ID.
* This function returns an array of subscription payment object and renewal payments associated with the subscription.
* @global wpdb $wpdb Instantiation of the wpdb class.
* @param string $subscription_id Subscription ID.
* @param string $currency Currency that the payment was made in.
* @return array Array of payment objects.
public function get_subscription_payment_history( $subscription_id, $currency = '' ) {
// Bail early if the subscription ID is empty.
if ( empty( $subscription_id ) ) {
return [ $subscription, $renewals ];
// Get the currency, if not provided.
if ( empty( $currency ) ) {
$currency = wpforms_get_currency();
// Get the database instance.
// Get the general where clause.
$where_clause = $this->add_secondary_where_conditions( [ 'currency' => $currency ] );
// Construct the query using a prepared statement.
// Execute the query and fetch the results.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
$results = $wpdb->get_results(
"SELECT * FROM {$this->table_name}
WHERE subscription_id = %s AND (type = 'subscription' OR type = 'renewal') {$where_clause}
ORDER BY type ASC, date_created_gmt DESC",
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// Search for the subscription object in the "$results" array.
foreach ( $results as $key => $result ) {
if ( $result->type === 'subscription' ) {
unset( $results[ $key ] );
break; // Exit the loop after finding the subscription object.
// Assign the remaining results to renewals.
return [ $subscription, $renewals ];
* Determine if given subscription has a renewal payment.
* @global wpdb $wpdb Instantiation of the wpdb class.
* @param string $subscription_id Subscription ID.
* @return bool True if the subscription has a renewal payment, false otherwise.
public function if_subscription_has_renewal( $subscription_id ) {
// Bail early if the subscription ID is empty.
if ( empty( $subscription_id ) ) {
// Get the database instance.
$query[] = "SELECT 1 FROM {$this->table_name} AS s";
$query[] = 'WHERE s.subscription_id = %s';
$query[] = "AND s.type = 'subscription'";
$query[] = 'AND EXISTS(';
$query[] = "SELECT 1 FROM {$this->table_name} AS r";
$query[] = 'WHERE s.subscription_id = r.subscription_id';
$query[] = "AND r.type = 'renewal'";
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
return (bool) $wpdb->get_var( $wpdb->prepare( implode( ' ', $query ), $subscription_id ) );
* Get subscription payment for given subscription ID.
* @param string $subscription_id Subscription ID.
public function get_subscription( $subscription_id ) {
$query[] = "SELECT * FROM {$this->table_name}";
$query[] = "WHERE subscription_id = %s AND type = 'subscription'";
$query[] = 'ORDER BY id DESC LIMIT 1';
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
return $wpdb->get_row( $wpdb->prepare( implode( ' ', $query ), $subscription_id ) );
* Get renewal payment for given invoice ID.
* @param string $invoice_id Invoice ID.
public function get_renewal_by_invoice_id( $invoice_id ) {
$meta_table_name = wpforms()->obj( 'payment_meta' )->table_name;
$query[] = "SELECT p.* FROM {$this->table_name} as p";
$query[] = "INNER JOIN {$meta_table_name} as pm ON p.id = pm.payment_id";
$query[] = "WHERE pm.meta_key = 'invoice_id' AND pm.meta_value = %s";
$query[] = 'ORDER BY p.id DESC LIMIT 1';
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
return $wpdb->get_row( $wpdb->prepare( implode( ' ', $query ), $invoice_id ) );
It is recommended that you Edit text format, this type of Fix handles quite a lot in one request
