[Resolved] What is the most database efficient method to add a sequential invoice number?

[Author]

Posts

[PaulS4783]

Hi,
I have a use case where a vehicle seller creates a database by adding vehicles to a master vehicle table as they are purchased into stock.

Not all the vehicles are necessarily sold. And when they are sold, they are not necessarily sold in the same chronological order as they were purchased.

When a vehicle is sold the vehicle should have an invoice number generated for it and recorded in the master vehicle table.
For argument sake, let's call this custom field "invoice_number".

So, programmatically I can think of two ways to achieve this with:

add_action( 'cred_save_data', 'custom_save_func', 10, 2 );
function custom_save_func( $post_id, $form_data ) {
   //some code
};

I think this thread comes close to what I am trying to do.
https://toolset.com/forums/topic/automatically-add-sequential-value-in-a-field-when-form-is-saved/

A. Put ALL vehicle records into an array, filter out only those that have an existing invoice number into a new array, sort the array numerically by invoice number, get the the last invoice number, increment +1, save the newly generated invoice number into the "invoice_number" custom field for that vehicle record.

B. In a separate "Seller Data" table, have one record call "last_invoice_number".
When the vehicle is sold, look up the value of this record, increment +1, save the newly generated invoice number into the "invoice_number" custom field for that vehicle record, AND save the new value into the "last_invoice_number" of the Seller Data table (effectively advancing the saved field value by one).

Of the two options, B. seems more programmatically efficient with database resources since it doesn't need to cycle through the entire master vehicle records list.

BUT option A. seems less prone to error e.g. if the both the "invoice_number" and "last_invoice_number" aren't updated together for some reason (server glitch?) there is obviously potential for some invoice numbers to get duplicated.

Any advice on the best way to go about this?

[Christian Cox]

A. Put ALL vehicle records into an array, filter out only those that have an existing invoice number into a new array, sort the array numerically by invoice number, get the the last invoice number, increment +1, save the newly generated invoice number into the "invoice_number" custom field for that vehicle record.
Any advice on the best way to go about this?
Rather than putting all vehicle records into an array, it seems like you could simply query posts by the custom number field value and order by that custom field value, in descending order, with a limit of 1 result. That puts the sorting and filtering directly in the DB query, rather than requiring extra programming to sort and filter all results after an unfiltered DB query of all results. Get the value of that custom field from the 1 result of the query and increment it, then save it into the new post's custom field value.

That seems like a more efficient way of handling this scenario.

https://developer.wordpress.org/reference/classes/wp_query/#order-orderby-parameters

[PaulS4783]

OK. Thank you!

I'm not sure about the syntax for get_post_meta and update_post_meta.
But does this look basically on the right track?

add_action( 'cred_save_data', 'custom_save_func', 10, 2 );
function custom_save_func( $post_id, $form_data ) {
    if ($form_data['id']==18074) {
 
        // get the most recent invoice number
        $invoice_args = array(
            'numberposts'       =>   1,
            'post_type'         =>   'vehicles',
            'orderby'           =>   'invoice_number',
            'order'             =>   'DESC'
        );
        $last_invoice = get_posts( $invoice_args );
      
        // get the invoice number of the fetched invoice
        $last_invoice_number = get_post_meta( /* not sure what arguments go in here? */ );
      
         
        // increment
        $last_invoice_number = $last_invoice_number + 1;
      
        // set the ID of the current post
        update_post_meta(  /* not sure what arguments go in here? */);
         
    }
}

[Christian Cox]

On the right track, yes, but if invoice_number is a custom field, you should include the meta_key argument here and order by meta_value_num instead of invoice_number. See a similar example in the documentation linked above, where it says "Display posts with ‘Product’ type ordered by ‘Price’ custom field".

Also note that Types custom field slugs have a database prefix of "wpcf-", so you if the slug of your field in wp-admin is "invoice_number", the meta_key argument value in this query should be "wpcf-invoice_number", as opposed to "invoice_number". The same is true for all WordPress APIs that reference custom field slugs - you must include the "wpcf-" prefix.

Regarding get_post_meta and update_post_meta, you can find those API arguments in the documentation here:
https://developer.wordpress.org/reference/functions/get_post_meta/
https://developer.wordpress.org/reference/functions/update_post_meta/
Again, you must use the "wpcf-" prefix with any Types field slugs in these arguments, and any other WordPress APIs that reference field slugs.

[PaulS4783]

OK. Thanks.

This is on the road map for next month but you can close the ticket for now.