Index

  1. Home
  2. Docs
  3. Index
  4. Data Publisher
  5. Adding filters

Adding filters

There are three ways to add a filter to a publication:

  1. Default where clause
  2. Shortcode parameters
  3. URL parameters

Default where clause

The Data Publisher allows plugin users to add any default where clause to a publication (click image below to enlarge). This includes subqueries and other more complex filters. The default where clause also supports the $$USER$$, $$USERID$$ and $$EMAIL$$ environment variables to filter user specific data (user must be logged in).

Shortcode parameters

Shortcode wpdataaccess provides two parameters to filter data:

  • filter_field_name (field name(s) to be filtered)
  • filter_field_value (field value(s) to filter, performs a LIKE and therefor allows wildcards)

Example of a filter for one specific column:

[wpdataaccess pub_id="18" filter_field_name="lastname" filter_field_value="jose%"]

For filters containing multiple column names and values, use a comma seperated value and make sure the array size of filter_field_name and filter_field_value are the same:

[wpdataaccess pub_id="6" filter_field_name="job,ename" filter_field_value="president,ford"]

The filter allows to use % as a wildcard as shown in the single column filter example. These filter parameters do not support operators like IN, OR, NOT, >, < and so on. Please use the default where clause to use these operators and more complex filters.

URL parameters

To add a filter on a specific table column you can add an URL argument to your request that follows the following name convention:

wpda_search_column_<column_name>

Only GET requests are supported for publications!

Example

YOUR-URL/?wpda_search_column_first_name=Sacha
<input name="wpda_search_column_first_name" type="text" value="Sacha" />

Adds condition

first_name = 'Sacha'

Wilcards are supported in URL parameters as well

YOUR-URL/?wpda_search_column_title=Last%

IN conditions

Use arrays to filter a column on multiple values (allows wildcard usage as well). For example:

YOUR-URL/?wpda_search_column_first_name[0]=Sacha&wpda_search_column_first_name[1]=Peter

Adds condition

first_name = 'Sacha' OR first_name = 'Peter'

OR conditions

Use parameter wpda_search_column_operator to compare using OR instead of AND. The plugin uses AND by default.

Example AND (default)

YOUR-URL/?wpda_search_column_first_name=Sacha&wpda_search_column_last_name=Schulz

Adds condition

first_name = 'Sacha' AND last_name = 'Peter'

Example OR

YOUR-URL/?wpda_search_column_first_name=Sacha&wpda_search_column_last_name=Schulz&wpda_search_column_operator=or

Adds condition

first_name = 'Sacha' OR last_name = 'Peter'

It is currently not possible to combine AND and OR conditions with URL parameters.

Was this article helpful to you? Yes No

30 Replies to “Adding filters”

  1. Hi, is it possible to set the order by attribute in the admin page with a column that is not selected in column names? For example sort by date_time column but I don’t choose this column in column names because I don’t want to how this column in the result.

    1. Yes Michal, you can do that, although it might be a bit confusing fo a user to get a sorted list without seeing the sorting column. Please keep in mind that a user can change the order by clicking on the header of another column.

  2. Hi, I am still evaluating your plugin and I might already be pushing it to the limit. But is there a way to filter the content with the help of user meta? I tried WHERE Clause: FIELD_NAME = ‘get_the_author_meta( ‘FIELD_NAME’, $user->ID )’ with zero result.

    1. Hi Clas, You can use FIELD_NAME=$$USERID$$ to compare with the user id or FIELD_NAME=$$USER$$ to compare with the user name. You cannot use PHP functions in a where. Hope this helps, Peter

    1. Hi Charles,

      Please change: ?wpda_search_column_Title to ?wpda_search_column_title. It works than.

      Good luck,
      Peter

  3. Hi Peter,
    I probably once again need your help and expertise to figure this one out, I have option to search for the production table based on Cargo Control Number (unique field) or by PO (reference number) which may vary, the whole PO number might have additional characters to the left or right of it, but we need to see all occurrences that contain the searchable PO term.

    We use the Gravity Form on https://www.rutherfordglobal.com/pars-search/ where the user has to choose the search by, then Gravity form notification is set to wpda_search_column_ccn={CCN:2}&wpda_search_column_po={PO:4} where CCN:2 is the second field ID of the term and PO:4 is the form field ID 4 and the final results are displayed on https://www.rutherfordglobal.com/pars-search-results/

    The search results page has the shortcode [wpdataaccess pub_id="2"]

    All works well as long as the user enters a CCN number or a full exact PO number, but as soon as the PO number is any shorter form it will not find the record(s)

    As I understand I need a WHERE clause similar to what I have on the old website where I use php diect select statement such as:
    ==============================================================
    // Set SQL string based on selected search option
    if ($search == “CCN”)
    $sql = “SELECT FileNo,rdate,rtime,port,ccn,po,sub,status from Production where ccn = ‘”.$parsNo.”‘”;
    else if ($search == “REF”)
    $sql = “SELECT FileNo,rdate,rtime,port,ccn,po,sub,status from Production where po like ‘%”.$parsNo.”%'”;
    ===============================================================
    I tried to put some filter in the Where clause under Data Publisher using po like ‘%wpda_search_column_po%’, tried many variations of this, even tried to put in the actual PO number I’m searching po like ‘%2588594%’ and never found, but there are many records in the PO table having in them this number

    Sorry for the long description, I just wanted to explain in detail what I’m looking to achieve:

    1). Allow the search for PO only to be any part of the actual PO number stored in the database and display all results
    2). If that is possible how do I make sure that it will not affect the search for CCN number which is always exact

    Hope you can help on this as you did many times in the past.

    1. Hi Joseph,

      The plugin allows you to use wildcard with URL arguments. You just need to add them yourself. This example:
      wpda_search_column_po=%%
      adds a wildcard in front and after your search value.

      Not sure if this works with Gravity Forms, but it could look like this:
      wpda_search_column_po=%{PO:4}%

      Can your please test if this works?

      Thanks,
      Peter

      1. Hi Peter,

        Excellent idea and thank you very much for your reply.

        I applied the change and tested in current website using WP-Access and in old website using direct PHP search, I tested for PO # 858859, on the PHP side shows 10 rows:
        https://gyazo.com/25f45df5ff1f0baa430a7c4ba23901af
        Using WP-Access and applying wpda_search_column_ccn={CCN:2}&wpda_search_column_po=%{PO:4}% as confirmation for Gravity Search Form gives me all the records in the database:
        https://gyazo.com/5e556cc618610e8ef3c8ef799fa98d4d

        But I also tested for search on CCN # and that was correct, so the change you suggested works well for CCN, but gives me all the database as a result for PO#

        Thanks,

        Joseph

      2. Hi Peter,

        Excellent idea and thank you very much for your reply.

        I applied the change and tested in current website using WP-Access and in old website using direct PHP search, I tested for PO # 858859, on the PHP side shows 10 rows:

        https://gyazo.com/25f45df5ff1f0baa430a7c4ba23901af

        Using WP-Access and applying wpda_search_column_ccn={CCN:2}&wpda_search_column_po=%{PO:4}% as confirmation for Gravity Search Form gives me all the records in the database:

        https://gyazo.com/5e556cc618610e8ef3c8ef799fa98d4d

        But I also tested for search on CCN # and that was correct, so the change you suggested works well for CCN, but gives me all the database as a result for PO#

        Thanks,

        Joseph

        1. Hi Joseph,

          Great to read it works for CCN #. What happens if you remove the wildcards for PO #?

          Thanks,
          Peter

          1. Hi Peter,

            If I remove the wildcards from PO it continues to work for CCN, but PO needs to be exact match to show, what I need is a way to allow still for CCN search and in the same time make the PO search show all available results that contain the PO entered

            Hope that’s possible, if not I thought to create 2 separate searches, one for the exact CCN wpda_search_column_ccn={CCN:2} and a second one for wildcard PO wpda_search_column_po=%{PO:4}% each displaying its own results, but I don’t know if I can create 2 separate searches in Data Publisher

            Thanks,

            Joseph

          2. Hi Joseph,

            I see there already is some javascript in your form submit. Can you change it, so that:
            – Only the CCN # is send WITH wildcards when CCN # is entered
            – Only the exact PO # is send when PO # is entered

            Would this solve the problem?

            Thanks,
            Peter

  4. Hi Peter,
    How would I define a WHERE clause when I have 2 options for searching: option by CCN# (which is always the same length) and option by REF# where I need to display all records that have the REF# entered as part of the record?

    I tried adding in the WHERE clause: po like ‘%wpda_search_column_po%’ which did not work and which also prevented getting the results for any search by ccn number which are working without needed a where clause

    Thanks,

    Joseph

  5. Hi Peter,

    sorry was away for awhile, my search results page uses:
    wpda_search_column_ccn={CCN:2}&wpda_search_column_po={PO:4}

    If I use the %{CCN:2}% and no % on the PO I get no results for any
    If I use {CCN:2} and %{PO:4}% i get CCN result and all the database for PO results
    If I use {CCN:2} and %{PO:4} I get CCN result and all the database as result for PO
    Same would happen if I use {PO:4}% I get CCN result and no result for PO

    I’m not using any JS in the form, it’s a Gravity Form that allows only entering the search term for either CCN or PO, then it takes the entered term and adds it to the wpda shortcode

    For testing, if I use 3959752984492 for CCN# I get correct results, if I use 858859 for REF# (PO) I get no results, but there are 12 records that have 858859 in their full PO number

    Thanks,

    Joseph

    1. Hi Joseph,

      Are you sure about your numbers? If I search for PO=106306 it works!

      I noticed that a search on column PO is much slower than a search on column CCN. Is it possible to create an idex on column PO?

      Thanks,
      Peter

      1. Hi Peter,

        Tried the PO number 106306 it does display 2 records, but looking in the actual database I can see 61 records that have the number 106306 included, it’s true that there are these 2 records that have identical PO, but all the other ones have the same number in their PO column, like SEGU9106306 or GM605321937010630666 and they want to see all the occurrences of the PO column that has the number entered anywhere in the record (noticed that the entered number is never at the front of the string)

        I noticed too that the search for PO is much slower than for CCN, how would I go and create an index to speed up the search for PO?

        Thanks,

        Joseph

        1. Hi Joseph,

          So you want to do a wildcard search? In that case you need to add % characters to both sides of your argument like: PO=%106306%

          But that might return too many rows. Can you check that? It also makes no sense to add an index on that column if you use a wildcard. Is there maybe a way to narrow a search on PO?

          Thanks,
          Peter

          1. Hi Peter,
            Here is my search:
            wpda_search_column_ccn={CCN:2}&wpda_search_column_po=%{PO:4}%
            doing a live search at https://www.rutherfordglobal.com/pars-search/ for CCN 17K8PARS016310 display the correct amount of records from the database, but if I do a PO search for 858859 it gives me the whole database while there are only 12 records%858859% with this pattern
            Using the same search with %PO% for PO # 106306 doesn’t show any records while in the database there 78 records with this pattern
            I cleared the cache and used 2 separate browsers, same result

            Thanks,

            Joseph

          2. Hi Joseph,

            Can you send me your table structure so I can have a closer look at this issue? Please follow these steps:
            > Data Explorer > Select table > Manage > SQL > Copy to clipboard

            You can use the contactform to send it in private.

            Thanks,
            Peter

  6. Is it possible, as part of a publication, to create a filter for users to apply – in particular for a date range?
    For example in my weather records table, a user might want to see the data from 1 January-31 January 2005.

    And while we’re at it, is it possible to perform calculations on the filtered result – average temperature for the days selected and so on?

    Thanks as always.

    1. Hi Mike,

      A date range filter is not available at this time. Sorry!

      I did a little research. I’ll add a custom search form to the next release, which allows you to make data range selections. But it only works client side! WP Data Access works server side by default. With server side processing the plugin loads the data shown on the actual page only, which usually makes a publication faster. Especially if you have a lot of data. Client side processing can have advantages to of course. If users do a lot of searching, ordering, etc on your publication data, loading all the data on page load might speed up local (client side) actions.

      You can change your publication to work client side, forcing the plugin to load all the data at once and see if that works for you. Although you have over 4400 rows in your publication, the row content is not taking up a lot of space. Might be worth to give it a try. To force WP Data Access to use client side processing, just add this to column table options advanced of your publication:
      { "serverSide": true }

      >>> And while we’re at it, is it possible to perform calculations on the filtered result – average temperature for the days selected and so on?

      I will add support for row grouping which allows you to do that to the next release. That was on my to do list anyway… 🙂

      Best regards,
      Peter

    1. Your master detail report looks great! 🙂 You just need to change your URL argument
      from: ?idPeticion=4
      to: ?wpda_search_column_idPeticion=4

      The plugin needs the wpda_search_column_ prefix to start searching.

      Good work,
      Peter

  7. Hi Peter:

    Yes! It works even hiding id colum to final user. That’s exactly the idea: The user doesn’t need to know how it works.

    Thank you for the final help!!

    Regads

  8. I’ve just given five stars to your amazing plugin. The most powerfull I have reached for WordPress all over the world.

    Regards from Spain

Leave a Reply to Joseph Cancel reply

Your email address will not be published. Required fields are marked *

4 + 1 =