Support

Home Forums Event Espresso Premium Searching and sorting additional columns on the transaction page

Searching and sorting additional columns on the transaction page

Posted: September 19, 2023 at 12:58 pm

Viewing 4 reply threads


pathwise

September 19, 2023 at 12:58 pm

Continued from the previous thread.

I am looking to add a few additional columns to the Transaction page in Event Espresso 4 and have an additional column be sortable and all columns to be searchable.

So far I have been able to add those columns and their values. However, I am having issues with getting both sorting and searching those new columns and values.

Here is the current version of my code

//Add columns to Transaction page *Under Construction*
/**
 * @param $columns
 * @param $screen
 * @return array|mixed
 */
function tw_custom_columns( $columns, $screen ) {
    // This is for the 'default' transactions list table
    // Event Espresso -> Transactions.
    if( $screen == 'espresso_transactions_default' ){
        // EEH_Array::insert_into_array() allows you to specific a specific key
        // that you want to add your additional column before/after.
        // This adds the custom columns just before 'actions' column.
        $columns = EEH_Array::insert_into_array(
            $columns,
            array( 'company-or-band-name-column' => 'Company or Band Name',
                'billing-first-name-column' => 'Billing First Name',
                'billing-last-name-column' => 'Billing Last Name'
            ),
            'actions'
        );
    }
    // Add a company-or-band-name-column column to the transactions list table.
    if( $screen == 'espresso_transactions_event_transactions' ) {
        //This is another method you can use that just adds the column to the end of the array.
        $columns['company-or-band-name-column'] = 'Company or Band Name';
    }
    return $columns;
}
add_filter('FHEE_manage_event-espresso_page_espresso_transactions_columns', 'tw_custom_columns', 10, 2);

/**
 * @param $item
 * @param $screen
 * @return void
 * @throws EE_Error
 * @throws ReflectionException
 */
function transaction_table_company_or_band_question( $item, $screen ) {
    //Sanity check to confirm we have an EE_Transaction object.
    if( $item instanceof EE_Transaction ){
        //Pull the answer object for Question ID 21 using the current registration.
        $primary_reg = $item->primary_registration();
        $question_id = 21;
        $answer_obj = EEM_Answer::instance()->get_registration_question_answer_object( $primary_reg, $question_id );
        if ( $answer_obj instanceof EE_Answer ){
            //If we have an answer object, echo the 'pretty' value for it.
            echo $answer_obj->pretty_value();
        }
    }
}
// 'company-or-band-name-column'
add_action( 'AHEE__EE_Admin_List_Table__column_company-or-band-name-column__event-espresso_page_espresso_transactions', 'transaction_table_company_or_band_question', 10, 2 );

function transaction_table_billing_first_name_question( $item, $screen ) {
    //Sanity check to confirm we have an EE_Transaction object.
    if( $item instanceof EE_Transaction ){
        //Pull the answer object for Question ID 21 using the current registration.
        $primary_reg = $item->primary_registration();
        $question_id = 13;
        $answer_obj = EEM_Answer::instance()->get_registration_question_answer_object( $primary_reg, $question_id );
        if ( $answer_obj instanceof EE_Answer ){
            //If we have an answer object, echo the 'pretty' value for it.
            echo $answer_obj->pretty_value();
        }
    }
}
// 'billing-first-name-column'
add_action( 'AHEE__EE_Admin_List_Table__column_billing-first-name-column__event-espresso_page_espresso_transactions', 'transaction_table_billing_first_name_question', 10, 2 );

function transaction_table_billing_last_name_question( $item, $screen ) {
    //Sanity check to confirm we have an EE_Transaction object.
    if( $item instanceof EE_Transaction ){
        //Pull the answer object for Question ID 21 using the current registration.
        $primary_reg = $item->primary_registration();
        $question_id = 14;
        $answer_obj = EEM_Answer::instance()->get_registration_question_answer_object( $primary_reg, $question_id );
        if ( $answer_obj instanceof EE_Answer ){
            //If we have an answer object, echo the 'pretty' value for it.
            echo $answer_obj->pretty_value();
        }
    }
}
// 'billing-last-name-column'
add_action( 'AHEE__EE_Admin_List_Table__column_billing-last-name-column__event-espresso_page_espresso_transactions', 'transaction_table_billing_last_name_question', 10, 2 );

/**
 * Enable sorting for the custom column
 * @param $sortable_columns
 * @param $screen
 * @return mixed
 */
function tw_ee_sortable_columns( $sortable_columns, $screen ) {

    if ( $screen == 'espresso_transactions_default' ) {
        $sortable_columns['company-or-band-name-column'] = array( 'company-or-band-name-column' => false );
    }

    return $sortable_columns;
}
add_filter('FHEE_manage_event-espresso_page_espresso_transactions_sortable_columns', 'tw_ee_sortable_columns', 10, 2);

/**
 * The models only accept certain values for orderby, normally the column name is used but if that's custom we'll need to pass a value
 * the models understand. THis sets the order by to 'Answer.ANS_value' when you select the custom column above.
 * @param $order_by
 * @param $request
 * @return array|mixed
 */
function tw_ee_get_orderby_for_transactions_query( $order_by, $request )
{

    write_log( 'Order By' );
    write_log( $order_by );

    if( isset( $order_by['company-or-band-name-column'] ) ) {

        $fixed_order_by = array(
            'Answer.ANS_value' => $order_by['company-or-band-name-column']
        );
        unset( $order_by['company-or-band-name-column'] );

        foreach( $order_by as $key => $value ) {
            $fixed_order_by[$key] = $value;
        }

        return $fixed_order_by;
    }

    //Return the original order_by array.
    return $order_by;
}
add_filter('FHEE__Transactions_Admin_Page___get_transactions_query_params', 'tw_ee_get_orderby_for_transactions_query', 10, 2);

I was referenced to https://gist.github.com/Pebblo/88f2a0a9213c716e4886a249e7709245
as a starting point. One main thing I see from that example is that filter that is used is

FHEE__Registrations_Admin_Page___get_orderby_for_registrations_query

However, I am looking to make adjustments to the Transaction_Admin_Page so I looked for the same filter for the Transaction page but no luck. There doesn’t seem to be a FHEE__Transactions_Admin_Page___get_orderby_for_transactions_query or similar.

The closest filter I could find is

FHEE__Transactions_Admin_Page___get_transactions_query_params

However, when I looked at what was available to alter for order it doesn’t seem like I am in the right place. Here is what is in the $order_by variable.

Array
(
    [0] => Array
        (
            [TXN_timestamp] => Array
                (
                    [0] => BETWEEN
                    [1] => Array
                        (
                            [0] => EventEspresso\core\domain\entities\DbSafeDateTime Object
                                (
                                    [_datetime_string:protected] => 
                                    [_error_log_dir:protected] => 
                                    [date] => 2013-09-19 00:00:00.000000
                                    [timezone_type] => 3
                                    [timezone] => America/Vancouver
                                )

                            [1] => EventEspresso\core\domain\entities\DbSafeDateTime Object
                                (
                                    [_datetime_string:protected] => 
                                    [_error_log_dir:protected] => 
                                    [date] => 2023-09-19 23:59:59.000000
                                    [timezone_type] => 3
                                    [timezone] => America/Vancouver
                                )

                        )

                )

            [Registration.REG_count] => 1
            [STS_ID] => Array
                (
                    [0] => !=
                    [1] => TFL
                )

            [STS_ID*] => Array
                (
                    [0] => !=
                    [1] => TAB
                )

        )

    [order_by] => Array
        (
            [TXN_timestamp] => desc
        )

    [limit] => Array
        (
            [0] => 0
            [1] => 100
        )

    [default_where_conditions] => this_model_only
)

Maybe I am going down the wrong direction here but yes I am still having issues with both the sort and search of a custom added column on the transaction page. Any help would be appreciated.


pathwise

September 19, 2023 at 1:57 pm

I possibly got a little further. I was able to differentiate the requests using the second parameter from the filter. After that I unset the order_by array and instead added a new option for order_by[‘Answer.ANS_value’] => asc/desc

function tw_ee_get_orderby_for_transactions_query( $order_by, $request )
{
    if( isset( $request['orderby'] ) && isset( $request['order'] ) ) {

        if( $request['orderby'] === 'company_column' ) {

            unset( $order_by['order_by'] );

            $order_by['order_by'] = array(
                'Answer.ANS_value' => $request['order']
            );

            return $order_by;
        }
    }

    return $order_by;
}
add_filter('FHEE__Transactions_Admin_Page___get_transactions_query_params', 'tw_ee_get_orderby_for_transactions_query', 10, 2);

However, I appear to run into an issue where I believe the problem is that the Answer.ANS_value model is not part of the Transaction model so it throws an error.

An EE_Error exception was thrown!   code: EEM_Base - _extract_related_model_info_from_query_param - 4172
"There is no model named 'Answer.ANS_value' related to EEM_Transaction. Query param type is order_by and original query param is Answer.ANS_value"

I tried a few different options both trying to figure out if I can somehow pass the model in the [‘order_by’] array or if there is something to do with the [‘defualt_where_conditions’] but I couldn’t find anything.


Tony

  • Support Staff

September 21, 2023 at 5:02 am

function tw_ee_get_orderby_for_transactions_query( $order_by, $request )

I highly recommend you fix the variable names here.

FHEE__Transactions_Admin_Page___get_transactions_query_params is passed the query params as the first parameter, NOT just the order_by… so right now when your working on that code it makes sense but looking back on it on say 3 months and you’ll think $order_by is just the order by based on the name.

Anywhere you have $order_by can be changed our to $query_params

Which also means then that $query_params['order_by'] makes more sense in place of $order_by['order_by'] 🙂

unset( $order_by['order_by'] ); you don’t need to unset the value, you’re replacing it on the next line anyway.

However, I appear to run into an issue where I believe the problem is that the Answer.ANS_value model is not part of the Transaction model so it throws an error.

Correct, the problem is that the transaction doesn’t relate to the Answers.

Answers are related to registrations and registrations are related to the transaction.

What you are trying to do here is pretty complex, it may not seem it, but it is. I know I’ve previously posted to you about how our models make things easier and this is an example of just how they do that.

A couple of things to note:

$query_params['order_by'] = array(
    'Answer.ANS_value' => $request['order']
);

As you on the Transaction page, that run on EEM_Transaction, meaning its looking for values related to the Transaction objects, so in English the above code says:

Set the order_by value to be based on the Answer.ANS_value, sorted ASC/DESC (depending what the request is)

When that’s run on EEM_Transaction is looks for relations related TO the based model (In this case the EEM_Transation)… but you can tell the models where the relation applies by passing a little more information. Answers related to registrations, so tell the model that with:

$query_params['order_by'] = array(
    'Registration.Answer.ANS_value' => $request['order']
);

Which in English:

Set the order_by value to be based on the Answer.ANS_value related to the Registrations, related to this transaction, sorted ASC/DESC

(Its a little easier to read it backwards as ‘related to’ etc.

I can’t say for sure if that will work, I’m going off the top of my head and a quick test with the EEM_Transaction model query using the correct relations above but it should give you more of an idea of what is happening.


pathwise

September 25, 2023 at 9:38 am

That’s great. I have it working with the change you supplied.

function tw_ee_get_orderby_for_transactions_query( $query_params, $request )
{
    if( isset( $request['orderby'] ) && isset( $request['order'] ) ) {

        if( $request['orderby'] === 'company_column' ) {

            $query_params['order_by'] = array(
                'Registration.Answer.ANS_value' => $request['order']
            );

            return $query_params;
        }
    }

    return $query_params;
}
add_filter('FHEE__Transactions_Admin_Page___get_transactions_query_params', 'tw_ee_get_orderby_for_transactions_query', 10, 2);

However, the next problem I am having is these new columns that I have added do not work for the search functionality on the Transactions page. Do we need to add the new columns to the search somehow?


Tony

  • Support Staff

September 26, 2023 at 3:43 am

Do we need to add the new columns to the search somehow?

Yes, if you take a look in the codebase where you found FHEE__Transactions_Admin_Page___get_transactions_query_params

You will see something like this:


$search_term = $this->request->getRequestParam('s');
if ($search_term) {
    $search_term  = '%' . $search_term . '%';
    $_where['OR'] = [
        'Registration.Event.EVT_name'         => ['LIKE', $search_term],
        'Registration.Event.EVT_desc'         => ['LIKE', $search_term],
        'Registration.Event.EVT_short_desc'   => ['LIKE', $search_term],
        'Registration.Attendee.ATT_full_name' => ['LIKE', $search_term],
        'Registration.Attendee.ATT_fname'     => ['LIKE', $search_term],
        'Registration.Attendee.ATT_lname'     => ['LIKE', $search_term],
        'Registration.Attendee.ATT_short_bio' => ['LIKE', $search_term],
        'Registration.Attendee.ATT_email'     => ['LIKE', $search_term],
        'Registration.Attendee.ATT_address'   => ['LIKE', $search_term],
        'Registration.Attendee.ATT_address2'  => ['LIKE', $search_term],
        'Registration.Attendee.ATT_city'      => ['LIKE', $search_term],
        'Registration.REG_final_price'        => ['LIKE', $search_term],
        'Registration.REG_code'               => ['LIKE', $search_term],
        'Registration.REG_count'              => ['LIKE', $search_term],
        'Registration.REG_group_size'         => ['LIKE', $search_term],
        'Registration.Ticket.TKT_name'        => ['LIKE', $search_term],
        'Registration.Ticket.TKT_description' => ['LIKE', $search_term],
        'Payment.PAY_source'                  => ['LIKE', $search_term],
        'Payment.Payment_Method.PMD_name'     => ['LIKE', $search_term],
        'TXN_session_data'                    => ['LIKE', $search_term],
        'Payment.PAY_txn_id_chq_nmbr'         => ['LIKE', $search_term],
    ];
}

You’ll need to add the Registration.Answer.ANS_value to the above, you can do that within the filter you have above by checking the requestParams passed to the filter within $request and then adding it to $query_params[0]['OR']

Viewing 4 reply threads

The support post ‘Searching and sorting additional columns on the transaction page’ is closed to new replies.

Have a question about this support post? Create a new support post in our support forums and include a link to this existing support post so we can help you.

Event Espresso