Using the MySQL Pipe - I struggled at first but got the results I needed

Since starting with Tadabase 2 years ago I have become more adept, and our App has grown and now has many Zapier and Make tasks that link it to other systems. But this week I decided to try and link directly to our CMS system via the MySQL Pipe (NB: we use Joomla, but it could be any MySQL system like Wordpress).

At first I struggled, it would not work as I expected. But, being stuck at home with Covid I had time to keep trying - and eventually I think I understand how it works, what it does and doesnot do and how to extend the functionality the basic Pipe offers. This post is for anyone else who is having the same struggles - it may help.

What was I trying to do?
What I want to do is have a button in my Tadabase app, in a page about a specific course, that gets all the email addresses of the users who are on that course, from our CMS and bring them back into a concatenated field that I can use in a email. (I know I can do this in Zapier or Make - but want to try and more native approach)

I loaded the Pipe and connected to the database. That was easy.

In the MySQL Pipe there are fixed set of 9 Queries. One of these is called ‘Get Filtered Records’ and that seems reasonable - I want to get records of users filtered by the course. This particular Pipe has just 2 Parameters. The Table the data is in, and the ‘filter’. This second part has hard to grasp for me. I was expecting a MySQL type ‘select’ query but the filter is much compressed version of that - it is in the manual page but I had to read and re-read to grasp what it was saying. AND what made the whole process much harder was that I was not getting any results.

Or so I thought.

You see the ‘filter’ that I used in the end was “usergroups,cs,Registered”. This means select rows where the field ‘usergroups’ ‘contains’ the term ‘Registered’. You might recall my end game was a concatenated string of emails. This query doesn’t address that at all. It brings back an Array of all rows that match the filter.

And this is a general point about the MySQL Pipe, most of the requests will return an Array… and I don’t understand how that array can be used, its not ready for use in a Tadabase field.

I thought I was getting nothing from my request, but I was getting an Array and Tadabase didn’t know what do do with it. This had me stumped. I found that I could change the Pipe/Response datatype to ‘comma separated values’ but that just gave me the array of data separated by commas, but at least I could dump this into an Txt field in a Tadabase table.

I was closer to my goal, but I only wanted the email column from the rows, not everything else.

After too long (I am not a coder so apologies if this should have been obvious) I realised that the Request I was using was …
“action” : “/records/{tableName}?filter={filter}”
The fields with {} curly braces are Parameters in the Pipe

For a similar Pipe called ‘Get Records with Specific Colums’ the Request is …
“action” : “/records/{tableName}/{primaryKeys}?include={columns}”
This pipe gets data from specific columns.

I realised that I might be able to EDIT the Request of the Pipe I was using to include the column(s) I wanted as well.

And so that’s what I did. Into the ‘Get Filtered Records’ pipe I added a Parameter for column. Added the correct reference for the email field into the new ‘column’ parameter in the Action calling the Pipe. And edited the Request to …
“action” : “/records/{tableName}?filter={filter}&include={column}

AND lo and behold I get want I wanted. A single record returned, all the email addresses separated by commas !!

Embolded with this I went further.

You see I said I used this as my filter field “usergroups,cs,Registered”. But ‘Registered’ is only one of the usergroups on our CMS. I wanted the search to be dynamic, and pass a variable from Tadabase into the Request.

For this I created a new Parameter - usertype. The filter parameter became “usergroups,cs,” and this was made complete in the Request by editing it to be …
“action” : "/records/{tableName}?filter={filter}{usertype}&include={column}"
In the Action calling the Pipe I connectd the parameter for usertype to the record/field in tadabase.

This is now a dynamic search, the usertype is variable and the result is the list if email addresses I want.

I hope this makes sense to someone else and helps.

OBSERVATIONS

  1. Takes more work that ZApier/Make, but once set up its faster to run
  2. It’s a shame I can’t create my own Requests in the Pipe (I have to hack one of the 9 that are there)
  3. Shame I can’t use regular MySQL query format. Complex queries with joins are a possibility but look very complex.
  4. Not sure what to do with the Array’s that normally get selected - how can they be used (Databridge?)
  5. The Update/New Record Pipes don’t seem to work - I get errors (reported)
3 Likes