How to get 2 Wordpress post lists from content from one custom post type?

I need help with a wordpress web app project (see sketch attached).
A manufacturing company with over 30 Regional Offices with each regional offices having 20 branches each.

Now each branch prepares a weekly report of their activity which includes Week, Number of attendance, Punctuality of staff , meeting location, donations,payments,branch name and region name--say Week1, Week2, Week3, Week4 and Week5.....I can create a custom post type with custom post meta to handle this.

However, my challenge is with creating a report post list for a Region(this will be monthly) with aggregated details from the custom post type and details created above....
Region 1 for example will have the following tables => Branch, Number of attendance(this will be total for all the weeks in the month for a specific branch), Punctuality of staff(total for the week for a branch) , meeting location, donations(total for the week for a branch) and payments(total for the week for a branch).

How can i handle this especially to create a post list for each Region. Remember i already have an exiting post list from the custom post type for each Branch weekly report.

please note that everything must be in the wp-admin backend..

Someone suggested Wp-list table api but How can i build a Post lists by using the wp-list table api for wordpress backend?The contents of the this table will be a special query taken from a custom post type content. I dont want to use sql queries , i want to remain native with wordpress post queries.

  • Dimitris

    Hello there Bez

    Here's official documentation of WP_List_Table class:
    https://codex.wordpress.org/Class_Reference/WP_List_Table

    I believe it'd be easier to create a frontend page though, with any custom queries you need, using WP_Query class:
    https://codex.wordpress.org/Class_Reference/WP_Query
    and simply "protect" these data to be visible only to logged-in users or use more complicated conditionals:
    https://developer.wordpress.org/reference/functions/is_user_logged_in/
    https://codex.wordpress.org/Function_Reference/current_user_can

    Warm regards,
    Dimitris

  • Bez

    Hello Dimitris,

    I have succeeded in creating a custom post type called Manufacturing with the accompanying columns for Weeks ( Week1, Week2, Week3, Week4) , Number of attendance, (this will be for each of the weeks 1-4 in the month for a specific branch), Punctuality of staff(for each of the weeks 1-4 for a branch in a specific month) , meeting location(for each of the weeks 1-4 for a branch in a specific month), donations(for each of the weeks 1-4 for a branch in a specific month) , payments(for each of the weeks 1-4 for a branch in a specific month) and the Month.
    It works okay.
    However, i need to a create a post list page for Regions which will have columns such as Branch (contains the list of branches), Number of attendance (this will contain the sum of all attendances for a specific branch in a specific month), Donations (total donations(sum) for a specific branch in a specific month) , Payments( total payments(sum) for a specific branch in a specific month).These columns are all Meta_Keys with their contents as Meta_Values. And so the list will have special focus on branches which is a kind of summary of their activities for the month.

    I figure out that i have to use some wpdb custom query to achieve a post list as stated above since i want it in the backend. I have tried using the WP List Table Api in the link you sent as well as use this stated https://wordpress.org/plugins/custom-list-table-example/ as a reference but im kinda stuck on the sql query especially where i have to sum up specific Meta_key_values based on branch and month conditions.
    This is my query but it does nothing but display only title :

    function prepare_items() {
      global $wpdb; //database queries
    
      $query = "SELECT ID, post_title, branch, week, donation, attendance, payments,post_date FROM $wpdb->posts, $wpdb->postmeta WHERE $wpdb->posts.ID=$wpdb->postmeta.post_id AND post_type = 'manufacturing' AND post_status = 'publish' ORDER BY post_date";
    
      $data = $wpdb->get_results($query, ARRAY_A);
    
      I also have this query for summation :
      //these are Meta_keys such as donation, attendance, payments,
      $meta_key = 'donation';
      $meta_key = 'attendance';
      $meta_key = 'payments';
    
      $data = $wpdb->get_result($wpdb->prepare("SELECT sum(meta_value) FROM $wpdb->postmeta WHERE meta_key = %$", $meta_key));

    How can i make this query a single query to populate the list? In other words, how do i merge this together based on the conditions for branch and month?

  • Konstantinos Xenos

    Hi Bez !

    There's a lot of data in your OP about what you want to create exactly that is still a bit confusing since we don't have at least the code to run it as a plugin and see how you've set up things. The specs are there, but I think I'm missing something.

    First of all let me clarify this from the start: yes straight $wpdb queries tend to be a lot faster than using either a get_posts() or a WP_Query(), BUT create a multi-query with various sums and joins needed might end up being more expensive than splitting it down to multiple either $wpdb or get_posts().

    From your sketch I understand that you have 1 CPT only that has all the data inside and not an actual CPT per District. So for example you have 1 CPT 'Districts' ( or branch as you call it ) and inside you have values like: Week 1, 56, 30, Melfort, 7200, 56000, Ohio in meta_values.

    If this is the case and everything is actually in meta_values then you have to find first all the posts that have a meta_value like 'ohio' and then loop again to sum up the other values like 'attendance'.

    As an example a create a script that reads normal 'posts' that have everything in meta_keys.

    The keys used here are 'district' and 'attendance'.

    global $wpdb;
    
    $query = "SELECT pm.meta_value
    			FROM $wpdb->postmeta pm
    			WHERE pm.meta_key LIKE 'district'
    			GROUP BY pm.meta_value";
    
    $my_districts = $wpdb->get_results( $query, ARRAY_A );
    
    $my_key_results = array();
    
    foreach ( $my_districts as $key => $district ) {
    
    	$posts = get_posts( array(
    		'post_type'      => 'post',
    		'post_status'    => 'publish',
    		'posts_per_page' => -1,
    		'meta_query'     => array(
    			array(
    				'key'     => 'district',
    				'value'   => $district['meta_value'],
    				'compare' => 'LIKE',
    			),
    		),
    	) );
    	if ( ! empty( $posts ) ) {
    		$attendance_sum = 0;
    		foreach ( $posts as $post ) {
    			$attendance = (int) get_post_meta( $post->ID, 'attendance', true );
    			$attendance_sum = $attendance_sum + $attendance;
    		}
    		$my_key_results[ $district['meta_value'] ] = array(
    			'attendance' => $attendance_sum,
    		);
    	}
    }
    
    var_dump( $my_key_results );

    This results in an multidimensional array of :

    array (size=2)
      'matchpoint' =>
        array (size=1)
          'attendance' => int 10
      'ohio' =>
        array (size=1)
          'attendance' => int 100

    You can then use each columns case switch from the WP_List_Table to pass any value from your results.

    I hope this helps a bit if I understood what you're trying to achieve correctly.

    This is something definitely achievable but as I said I'm not sure how everything is set up on your end at the moment, I got lost a bit on where is what in your database.

    Regards,
    Konstantinos

  • Dimitris

    Hello Bez

    Hope you're doing good today! :slight_smile:

    As this is public forum, you must send us your details through our safe contact form https://premium.wpmudev.org/contact/#i-have-a-different-question
    using this template:

    Subject: "Attn: Konstantinos"
    - WordPress admin (login url/username/password)
    - FTP credentials (host/username/password/path of site in question)
    - specific paths of plugins and/or custom code files
    - any other relevant urls
    - link back to this thread for reference

    Keep in mind the subject line as ensures that it gets assigned to Konstantinos.

    Also let us know, if you made any efforts with custom code that Konstantinos shared, or you want us to have a look in your initial setup. :slight_smile:

    Thank you,
    Dimitris

  • Konstantinos Xenos

    Hi Bez ,

    I've rewrote the code in branchesmfg.php. You'll also find an _branchesmfg.php ( deactivated ) that is your original file in your plugins folder.

    I removed the Delete option for now since that process would again require grouping by IDs etc to properly work, as there's not a single 'ID' because it's a group of posts basically.

    The WP_List_Table is grouped by 20 posts per page and has sorting as well. I used the _meta that I've found inside your Activities posts to make the sums but double check those since I was working with what I 'understood' you needed in each column.

    Just for future reference this kind of custom work actually surpasses the scope of our support, so in the future you might need to hire a developer for further edits and customization. You can always make use of our partnership with Codeable from here: https://premium.wpmudev.org/partners

    Regards,
    Konstantinos

  • Bez

    Hello Kostatinos/Oguz,
    I figured out how to fix the issue . It was not difficult .
    I noticed in the DB , that those values were saved with the commas like this - 730,031.89 and the sum function does not recognize such.
    So what i did was to edit those values , remove the comma(s) and the sum function worked as it should and then i used number_format ( $number , 2 , '.' , ',') to display.
    Hope this helps someone

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.