0

im developing a plugin which shows an information about orders, it shows billing information, purchased product, and travel date. I need to make an filters for table which let to select a specific product name, or travel date smth like this This is what i need, my table looks like this: My table How i can achieve to display a working filters?

class Employees_List_Table extends WP_List_Table
{
      private $users_data;

      private function get_users_data($search = "")
      {
            global $wpdb;

            if (!empty($search)) {
                  return $wpdb->get_results(
                        "SELECT
                        woi.order_item_name AS order_items,
                        order_id,
                        post_date,
                        p.post_status AS order_status,
                        MAX(
                            CASE WHEN pm.meta_key = '_billing_first_name' AND p.id = pm.post_id THEN pm.meta_value
                        END
                    ) AS '_billing_first_name',
                    MAX(
                        CASE WHEN pm.meta_key = '_billing_last_name' AND p.id = pm.post_id THEN pm.meta_value
                    END
                    ) AS '_billing_last_name',
                    MAX(
                        CASE WHEN pm.meta_key = '_billing_email' AND p.id = pm.post_id THEN pm.meta_value
                    END
                    ) AS 'billing_email',
                    MAX(
                        CASE WHEN pm.meta_key = '_billing_phone' AND p.id = pm.post_id THEN pm.meta_value
                    END
                    ) AS 'billing_phone',
                    MAX(
                        CASE WHEN woim.meta_key = 'Kelionės data' AND p.id = pm.post_id THEN woim.meta_value
                    END
                    ) AS 'travel_date',
                    MAX(
                        CASE WHEN woim.meta_key = '_qty' AND p.id = pm.post_id THEN woim.meta_value
                    END
                    ) AS 'kiekis',
                    (
                        SELECT
                            pm.meta_value
                        FROM
                            wp_postmeta pm
                        WHERE
                            woi.order_id = pm.post_id AND pm.meta_key LIKE '_billing_phone'
                    ) AS 'plucked_billing_phone',
                    (
                        SELECT
                            pm.meta_value
                        FROM
                            wp_postmeta pm
                        WHERE
                            woi.order_id = pm.post_id AND pm.meta_key LIKE '_billing_email'
                    ) AS 'plucked_billing_email',
                    (
                        SELECT
                            pm.meta_value
                        FROM
                            wp_postmeta pm
                        WHERE
                            woi.order_id = pm.post_id AND pm.meta_key LIKE '_billing_first_name'
                    ) AS 'plucked_billing_first_name',
                    (
                        SELECT
                            pm.meta_value
                        FROM
                            wp_postmeta pm
                        WHERE
                            woi.order_id = pm.post_id AND pm.meta_key LIKE '_billing_last_name'
                    ) AS 'plucked_billing_last_name',
                    (
                        SELECT
                            woim.meta_value
                        FROM
                        wp_woocommerce_order_itemmeta woim
                        WHERE
                            woi.order_item_id = woim.order_item_id AND woim.meta_key LIKE 'Kelionės data'
                    ) AS 'plucked_travel_date',
                    (
                        SELECT
                        woim.meta_value
                        FROM
                        wp_woocommerce_order_itemmeta woim
                        WHERE
                            woi.order_item_id = woim.order_item_id AND woim.meta_key LIKE '_qty'
                    ) AS 'plucked_kiekis'
                    FROM
                        wp_woocommerce_order_items woi
                    LEFT JOIN wp_posts p ON
                        woi.order_id = p.ID
                    LEFT JOIN wp_woocommerce_order_itemmeta woim ON
                        woi.order_item_id = woim.order_item_id
                    LEFT JOIN wp_postmeta pm ON
                        woi.order_id = pm.post_id
                    WHERE
                        order_id LIKE '%{$search}%' OR order_item_name LIKE '%{$search}%' OR pm.meta_value LIKE '%{$search}%' OR woim.meta_value LIKE '%{$search}%' AND p.post_type = 'shop_order'
                    GROUP BY
                        woi.order_item_id
                        ",
                        ARRAY_A
                  );
                  
            }else{
                  return $wpdb->get_results(
                        "SELECT
                        woi.order_item_name AS order_items,
                        order_id,
                        post_date,
                        p.post_status AS order_status,
                        MAX(
                            CASE WHEN pm.meta_key = '_billing_first_name' AND p.id = pm.post_id THEN pm.meta_value
                        END
                    ) AS '_billing_first_name',
                    MAX(
                        CASE WHEN pm.meta_key = '_billing_last_name' AND p.id = pm.post_id THEN pm.meta_value
                    END
                    ) AS '_billing_last_name',
                    MAX(
                        CASE WHEN pm.meta_key = '_billing_email' AND p.id = pm.post_id THEN pm.meta_value
                    END
                    ) AS 'billing_email',
                    MAX(
                        CASE WHEN pm.meta_key = '_billing_phone' AND p.id = pm.post_id THEN pm.meta_value
                    END
                    ) AS 'billing_phone',
                    MAX(
                        CASE WHEN woim.meta_key = 'Kelionės data' AND p.id = pm.post_id THEN woim.meta_value
                    END
                    ) AS 'travel_date',
                    MAX(
                        CASE WHEN woim.meta_key = '_qty' AND p.id = pm.post_id THEN woim.meta_value
                    END
                    ) AS 'kiekis',
                    (
                        SELECT
                            pm.meta_value
                        FROM
                            wp_postmeta pm
                        WHERE
                            woi.order_id = pm.post_id AND pm.meta_key LIKE '_billing_phone'
                    ) AS 'plucked_billing_phone',
                    (
                        SELECT
                            pm.meta_value
                        FROM
                            wp_postmeta pm
                        WHERE
                            woi.order_id = pm.post_id AND pm.meta_key LIKE '_billing_email'
                    ) AS 'plucked_billing_email',
                    (
                        SELECT
                            pm.meta_value
                        FROM
                            wp_postmeta pm
                        WHERE
                            woi.order_id = pm.post_id AND pm.meta_key LIKE '_billing_first_name'
                    ) AS 'plucked_billing_first_name',
                    (
                        SELECT
                            pm.meta_value
                        FROM
                            wp_postmeta pm
                        WHERE
                            woi.order_id = pm.post_id AND pm.meta_key LIKE '_billing_last_name'
                    ) AS 'plucked_billing_last_name',
                    (
                        SELECT
                            woim.meta_value
                        FROM
                        wp_woocommerce_order_itemmeta woim
                        WHERE
                            woi.order_item_id = woim.order_item_id AND woim.meta_key LIKE 'Kelionės data'
                    ) AS 'plucked_travel_date',
                    (
                        SELECT
                        woim.meta_value
                        FROM
                        wp_woocommerce_order_itemmeta woim
                        WHERE
                            woi.order_item_id = woim.order_item_id AND woim.meta_key LIKE '_qty'
                    ) AS 'plucked_kiekis'
                    FROM
                        wp_woocommerce_order_items woi
                    LEFT JOIN wp_posts p ON
                        woi.order_id = p.ID
                    LEFT JOIN wp_woocommerce_order_itemmeta woim ON
                        woi.order_item_id = woim.order_item_id
                    LEFT JOIN wp_postmeta pm ON
                        woi.order_id = pm.post_id
                    GROUP BY
                        woi.order_item_id",
                        ARRAY_A
                  );
            }
      }


      // Define table columns
      function get_columns()
      {
            
            $columns = array(
                  'cb'            => '<input type="checkbox" />',
                  'order_id' => 'Užsakymo ID',
                  'post_date' => 'Data',
                  'order_items' => 'Užsakytos kelionės',
                  'plucked_kiekis' => 'Keliautojų skaičius',
                  'plucked_billing_first_name'    => 'Vardas',
                  'plucked_billing_last_name'     => 'Pavardė',
                  'plucked_billing_email'      => 'Email',
                  'plucked_billing_phone'   => 'Tel. Nr',
                  'plucked_travel_date'   => 'Kelionės data',
                  'order_status' => 'Statusas'
                  
            );
            return $columns;

  
      
      }

      // Bind table with columns, data and all
      function prepare_items()
      {
            if (isset($_POST['page']) && isset($_POST['s'])) {
                  $this->users_data = $this->get_users_data($_POST['s']);
            } else {
                  $this->users_data = $this->get_users_data();
            }

            $columns = $this->get_columns();
            $hidden = array();
            $sortable = $this->get_sortable_columns();
            $this->_column_headers = array($columns, $hidden, $sortable);

            /* pagination */
            $per_page = 20;
            $current_page = $this->get_pagenum();
            $total_items = count($this->users_data);

            $this->users_data = array_slice($this->users_data, (($current_page - 1) * $per_page), $per_page);

            $this->set_pagination_args(array(
                  'total_items' => $total_items, // total number of items
                  'per_page'    => $per_page // items to show on a page
            ));

            usort($this->users_data, array(&$this, 'usort_reorder'));

            $this->items = $this->users_data;
      }

      // bind data with column
      function column_default($item, $column_name)
      {

            switch ($column_name) {
                  case 'order_id':
                        case 'post_date':
                        case 'order_items':
                              return $item[$column_name];
                        case 'plucked_billing_first_name':
                        case 'plucked_billing_last_name' :
                              case 'plucked_billing_email':
                              case 'plucked_billing_phone':
                                    case 'plucked_travel_date': 
                                    case 'order_status':
                                          case 'plucked_kiekis':
                              return ucwords($item[$column_name]);
                       
                              
                        default:
                              return print_r($item, true); //Show the whole array for troubleshooting purposes
            }
      }

      

      // To show checkbox with each row
      function column_cb($item)
      {
            return sprintf(
                  '<input type="checkbox" name="user[]" value="%s" />',
                  $item['ID']
            );
      }

      // Add sorting to columns
      protected function get_sortable_columns()
      {
            $sortable_columns = array(
                  'order_id'  => array('order_id', false),
                  'post_date' => array('post_date', false),
                  'plucked_billing_first_name'   => array('plucked_billing_first_name', true),
                  'plucked_billing_last_name'   => array('plucked_billing_last_name', true),
                  'plucked_billing_email'   => array('plucked_billing_email', true),
                  'plucked_billing_phone'   => array('plucked_billing_phone', true),
                  'order_items'   => array('order_items', true),
                  'plucked_travel_date'   => array('plucked_travel_date', true),
                  'order_status' => array('order_status', true),
                  'plucked_kiekis' => array('plucked_kiekis', true)
            );
            return $sortable_columns;
      }

      // Sorting function
      function usort_reorder($a, $b)
      {
            // If no sort, default to user_login
            $orderby = (!empty($_GET['orderby'])) ? $_GET['orderby'] : 'order_id';
            // If no order, default to asc
            $order = (!empty($_GET['order'])) ? $_GET['order'] : 'asc';
            // Determine sort order
            $result = strcmp($a[$orderby], $b[$orderby]);
            // Send final sort direction to usort
            return ($order === 'asc') ? $result : -$result;
      }




}

TO display table:

function rezervuotos_keliones()
{
      // Creating an instance
      $empTable = new Employees_List_Table();
      echo '<div class="wrap"><h2>Rezervuotos kelionės</h2>';

    // Prepare table
      $empTable->prepare_items();
      ?>
            <form method="post">
                  <input type="hidden" name="page" value="rezervuotos_keliones" />
                  <?php $empTable->search_box('Paieška', 'search_id'); ?>
            </form>
      <?php
      // Display table
      $empTable->display();
      echo '</div>';
     
}
2
  • can you edit your question to include code? How have you implemented your WP List Table and how is the data you are displaying and want to filter being stored? Also edit out the links to the screenshots and embed them directly in the question using the provided image upload
    – Tom J Nowell
    Sep 6 at 13:17
  • 1
    I edited the question, included photos and code
    – Vyk
    Sep 6 at 13:32

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.