Trying to make dynamic filters

  • Resolved marcodisco

    (@marcodisco)


    4 years, 2 months ago

    Hi Peter,

    I was trying to filter data based on a dynamic hyperlink. That didn’t seem to work, so I am going a few steps back and trying the various filter options that I have seen listed in your documentation and here in the forum.

    My data is music based, simplified lets say artists and tracks. I have an artists table and a tracks table. Each track has 1 artist and each artist has 1 or more tracks.

    In my artists table there is id, artist_name
    In my tracks table there is id, id_artist, track_name

    My goal is to have publication showing a list of artists, with a link to ‘view all tracks’ for that artist.

    Step 1. I can define a WHERE clause in my Data Publisher entry for the tracks table (id_artist=”9″). This works – and if I embed the shortcode [wpdataaccess pub_id=”5″] then I do see a list of tracks for artist 9 when I visit the page:
    https://mysite.com/tester/

    Step 2. I tried to add the filter inside the shortcode, like this:
    [wpdataaccess pub_id=”5″ filter_field_name=”id_artist” filter_field_value=”9″]
    So, visiting https://mysite.com/tester/
    This gives no results (I did remove the previous WHERE from the publication).

    Step 3. Following your comments here (https://wpdataaccess.com/2020/07/09/add-user-input-filter-to-where-clause/) I tried to add a query variable to provide the filter:
    shortcode [wpdataaccess pub_id=”5″]
    https://mysite.com/tester/?wpda_search_column_id_artist=9
    This returns all of the records, so no filtering.

    Step 3 would provide the best solution (if it would work) as ultimately I want to add a dynamic hyperlink to the artists table using the data explorer, so that I can have a ‘view tracks’ link next to reach artist.

    If Step 2 would work then I could probably create a WordPress page to retrieve query variable data, then run a do_shortcode() in php adding the filter tags in the shortcode.

    I have an additional question – can you confirm if these filters are applied when SELECTing from the DB, so only relevant data is retrieved.

    I would welcome any advice as to how to best implement this dynamic filter functionality.

    Thanks,
    Mark

Viewing 8 replies - 1 through 8 (of 8 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Mark,

    Thank you for your extensive explanation! That helps.

    Let’s skip step 1. This cannot fulfil your requirements.

    I wonder why your step 2 example is not working. Can you confirm your shortcode looks exactly like this:
    [wpdataaccess pub_id="5" filter_field_name="id_artist" filter_field_value="9"]

    This shortcode – which is almost the same as yours – works fine for me:
    [wpdataaccess pub_id="10" filter_field_name="id" filter_field_value="9"]

    What is the data type of column id_artist? Is the column name case sensitive?

    I tried your step 3 example. This is not working for me either. It looks like this only works with Data Projects. I will have a look at this and try to fix it. I’ll let you know when it works…

    Best regards,
    Peter

    Thread Starter marcodisco

    (@marcodisco)

    Hi Peter,

    Thanks for your notes.

    Step 2 – id_artist is an INT field. Yes, the shortcode is the same. As yours works, I tried removing some of the settings in my publication (field list, ORDER by), but still no luck.

    Step 3 – OK, yes, using the wpda_search_column query variable against a page created from a Data Project DOES WORK!! I set up the dynamic hyperlink to point to a page with a shortcode like [wpdadiehard project_id=”1″ page_id=”3″] and now my View Tracks against each Artist works fine.

    That should be enough for me to complete my proof-of-concept site for now.

    Thanks,
    Mark

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Mark,

    I patched version 3.1.5 to fix your step 3 issue. This allows you to use wpda_search_column query variables in publications as well. You need to delete and re-install the plugin (or wait for the next release).

    Be carefull when you delete the plugin!!! Make sure your publications, projects and plugin settings are not deleted!
    – Go to menu Settings
    – Sub menu WP Data Access
    – Tab Uninstall
    – Uncheck both checkboxes
    – Save your setting
    With these settings you can delete and re-install the plugin without losing your data (you can also just delete the plugin folder).

    After re-installing the plugin, make sure you delete your cached files. This update involves a javascript file.

    Thank your for reporting this bug! Please let me know if this solves your problem…

    Best regards,
    Peter

    Thread Starter marcodisco

    (@marcodisco)

    Hi Peter,

    Thanks for your work here, but I don’t have any good news. I de/re-installed your pluging from WordPress following your instructions.

    I tried wpda_search_column as a query variable against a Data Publication but it still didn’t work for me.

    Also I noticed that when I used wpda_search_column in a Data Project page, all worked, but then as soon as I do something like re-order the data by clicking a column header, the filter is lost. I needed to re-order as the order that I specified in the Data Project option group itself didn’t work.

    Kind regards,
    Mark

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Mark,

    Can you please make sure your js files are not cached? You wont see any difference if the plugin is still using your old js files.

    Can I access your site? You can use the contactform on the plugin website if your prefer to share this information in private.

    I will have a look at the re-ordering…

    Best regards,
    Peter

    Thread Starter marcodisco

    (@marcodisco)

    Hi Peter,

    Thanks,

    Yes, you were quite correct – wpda_search_column IS working for a Data Publication now, I just needed to reload the page without cache to get latest javascript. Also, I note that sorting by column in the broweser/client works nicely here.

    I will send the access details for my site via the contact form in case you would find it helpful.

    Kind regards,
    Mark

    Thread Starter marcodisco

    (@marcodisco)

    Hi Peter,

    I did send you the details for access to my site via the contactform on you plugin page.

    Please ignore the issue that I mentioned about not being able to update records – that was caused by DB access permissions and is resolved.

    Thanks for your help, once again. You are the most responsive plugin author. It makes me re-think about publishing my own plugins, if I have to live up to this level of support ??

    The only things that I have as still an issue are
    1) using the wpda_search_column as a GET query variable against a Data Project page – the filter is lost if I click on column headers to re-order data.
    2) just being able to set an order_by in an option set used by a Data Project page. I have tried this using a SQL-style entry ‘report_time desc’, and no good. I tried the same format as works in a Data Publication i.e. ‘2, desc’ but that didn’t seem to work either.

    Finally, what DB GRANTs are required for target Database? My example DB (not the WordPress DB) only uses wp data access for reads and updates, so I have granted only select and update access. Is that enough?

    Any insights would be appreciated.

    Thanks,
    Mark

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    Hi Mark,

    Good to hear the permission issue is resolved! And thanks for your compliments! ??

    (1) The issue with the filter being lost is on my to do list.
    (2) The order by request for projects is already on my list for a while… ?? Sorry! I will pick it up after my holiday.

    Your privileges on a remote database define what you are allowed to do on that database. If you are allowed to connect, you will see all features in the Data Explorer for your remote database. But that doesn’t mean all features work… ?? If you click the drop table button for example, you’ll get an error if you do not have the necessary privileges. So, depending on your requirements you can decide for yourself which privileges you want to give your remote users. The minimum is that they are allowed to connect. The rest is up to you. Does that make sense?

    Best regards,
    Peter

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘Trying to make dynamic filters’ is closed to new replies.