We are using WPdatatables and Toolset (with pleasure!) for a client. In Toolset we made sevaral CPT. Now we want to use a SQL query to retrieve data out of the CPT and present them to the user with wpdatatables. Now the query is difficult because there are several cpt's and the are related to each other. So there are employers who have employees and the employees do have educations. Alle tree in different tables in a 1:n way. The SQL is to difficult for the wizards in WPdatafabels en to difficult for us. Do you have a service to make SQL on toolset querys for us?
Do you have a service to make SQL on toolset queries for us?
No, unfortunately not.
Maybe a Contractor could craft custom code:
https://toolset.com/contractors/
But, before you ask them, I would like to understand the issue you report and see if I can help.
See, something is strange in the report:
"Alle tree in different tables in a 1:n way."
If you are using the stable toolset, there are no additional tables for our post relationships.
I mean, it's all in the posts table and referred to each other by a simple post meta entry that holds the parent Post ID, there isn't more magic to it.
The post meta field has a key "_wpcf_bleongs_{your-parent-post-type-slug}_id
Now, in the new Many To Many Relationship available in the Beta, the situation is different, but you don't mention using them.
Can you explain to us how the Posts are created and what version you used to create them?
If that is with the stable version then you can simply use the above-shared postmeta to find related posts with a SQL query.
May I also ask why you need to use SQL?
Views provide great methods to display related posts, and our API also helps to receive such data.
Hi Beda,
Thanks for the reply.
The situation is that we have a functionality in the system tot give the user te possibly tot retrieve data form the database and use it to export tot excel, pdf and Word and use filters on that. We use WPdatatables for that. Yes I would like tot do it with views but than I can't export it tot then amend formats. WP data tables does. Bu for that I need to get the data in WP data tables and it uses SQL for that. WPdatatables has a sql wizards but the wizzard don't 'see' the way that Toolset saves the data as you described. I sees the CPT but when I wan to retrieve data it's not working. So WPdatatables see then amend tree data tables and tries to relate them in a way Toolset doesn't.
Yes I use the stables version. Is it easier to use the Beta for this?
I want to retrieve everything from the cpt with SQL. No filters in the SQL. I do that in WP datatables.
Is there a way to see the way Views retrieves data with SQL.
If you are using the stable versions, then you are able to get all posts from the posts table, and relations by the custom field i elaborated.
This is like creating any other SQL Query by a custom field.
Usually, you don't need to write SQL to get those, but a simple get_posts() will do:
https://developer.wordpress.org/reference/functions/get_posts/
Of course, if your plugin wants SQL that is what you need to give them.
If they have a GUI it may be possible to query "your post type" by "a custom field value".
That is what you would do and then choose the hidden custom field above-elaborated.
Something like this for example represents posts with custom field "meta_key_i_want_to_find":
SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON
wp_posts.ID=wp_postmeta.post_ID WHERE (meta_key='meta_key_i_want_to_find');
This is a good guide to craft SQL Queries:
https://code.tutsplus.com/tutorials/writing-custom-queries-in-wordpress--wp-25510
Toolset does not use Custom tables or Custom SQL Syntaxes.
With the stable versions, this is as simple as getting any other Post with or by Custom Fields values, which am sure your plugin can do?
In the new version, however, this will be more complex, I am not sure this will work with that plugin without writing complex SQL Queries, which we cannot assist in the forum here, unfortunately.
For that eventually the contractors can help:
https://toolset.com/contractors/
I can however elaborate on the new Database tables used by Toolset if you plan to use the new features (sooner or later you will need to use it, as when you update Toolset it will migrate the relationships and only support legacy PHP queries, not SQL Queries, as that was never directly supported on our API (we provide wrappers for that).
For now, you can get those posts by their meta.
In future, other database tables will need to be queried.
Beda,
Pffff... SQL is not my thing! I'm a designer. So when I want al the fields for a CPT included in one SQL query the syntax is:
SELECT * FROM wp_posts INNER JOIN wp_postmeta ON
wp_posts.ID=wp_postmeta.post_ID WHERE (meta_key='meta_key_i_want_to_find');
Then what is te syntax for the where.
Example:
I want to retrieve alle the data form the cpt 'werkgevers' and 'werknemers' (related in toolset)
SELECT * FROM wp_posts INNER JOIN wp_postmeta ON
wp_posts.ID=wp_postmeta.post_ID WHERE (meta_key='.......');
Retrieve all the data just can't be so difficult?
Jacques
I understand, but we do not provide Custom Code here, we provide Support for Toolset, and SQL Queries are not part of it.
I understand that you need this - moreover, it will become more complex in the future with the new relationships.
This is exactly why we create Toolset.
Because SQL and WordPress API is not easy, it's an advanced topic and Toolset makes it possible to not use it.
Hence I go above the scope of Support and Toolset if I start to craft Custom SQL Queries.
I am sure you can understand my limits here.
My SQL Query example gets you all Posts where a certain Custom Field is present.
Enhancing this query needs a Custom work on it and knowledge of the SQL Commands.
The Types data is stored as elaborated, and with that information, someone who understands SQL can craft the query.
But I cannot do that for you, as elaborated this is custom code that you can get from Contractors, for example.
The meta key you look for (in the stable verison) is "_wpcf_belongs_{your-parent-post-type-slug}_id" where you will need to replace the part {your-parent-post-type-slug} with the actual slug of that post type.
The Custom Field holds one value:
The ID of the post that is a parent to the post where the field is attached to.
Hence, if you get all posts, where the field "_wpcf_belongs_{your-parent-post-type-slug}_id" is value "18", you would get all posts that are CHILD to the post with ID 18.
All these posts are saved in the posts table.
All these postmeta are saved in the respective postmeta table.
This is how it currently works. That will not work anymore like this in the future versions of Toolset.
What I can do is ask permission to craft a query alike for you, but as said, this is not within the scope of this forum.
Beda,
I totale understand your and Toolset's point of view. Yes I would be very happy is you'll get permission to that. Thank you very much.
I will be back Monday or Tuesday for more details.
I cannot guarantee that I will get allowance.
Beda,
Have a nice weekend. Thanks so far. Im hope you will get allowance.
Jacques
We decided to take another approach, that does not infringe our Support rules and hence is just towards other users of the forum.
I will try to understand how this plugin you use works and make it work with Toolset, using the GUI's and DOCs our both plugins have.
We will then provide a solution if we can find one.
It seems that the plugin wpdatatables is a paid product, and we do not have it at our disposal.
The "wpdatatables lite plugin" doesn't seem to have all the features available.
Could you zip up a copy of the plugin and give me a short oversight where I have to do what, in case there are any special steps to consider?
I activated a private reply, that protects the link to the ZIP from any fraud.
Only OnTheGoSystems Employees and yourself have access to the private reply.
Once tested, the copy will be removed from our testing grounds.
The browser neither the FTP can find your server.
It's an unknown host.
Please can you upload the plugin to a Google Folder, for example, and then share with anyone who has that link?
The WordPress Database GUI would, in my opinion, be the right tool to use, but clearly, the plugin does not support post meta query.
The SQL tool on the other hand for me is confusing, misleading, and if you do not 100% understand SQL, you will not be able to craft proper queries above basic ones.
So I went the manual way, and came up with this example, based on the below data which I already shared above.
1. The Toolset refers to related Post Types with the hidden custom field "_wpcf_belongs_{parent-post-type-slug}_id
2. That field will hold the ID of the parent Post.
3. In the new Many To Many, this won't work anymore
The query below will get all posts that are of type "xy", and belong to a post with ID "xy".
It returns now the post ID and field slug, but that can be changed as you know.
SELECT wp_posts.ID, wp_postmeta.meta_key
FROM wp_posts
JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
AND wp_postmeta.meta_key = '_wpcf_belongs_toolset-parent_id'
WHERE wp_posts.post_type = 'toolset-post-type'
AND wp_postmeta.meta_value = '40'
order by wp_posts.ID asc
As you see you will need to pass the ID of the parent Post you look for in the child post type.
Please edit:
ID, PostMeta Key, Post Type as per your needs.
This plugin required high knowledge of SQL in my opinion, to make queries like this.
The GUI (for me) was not enough.
I might miss something of course.
Above SQL should give a godd example how to build the query.
Beda,
Thanks!
I tried:
SELECT wp_posts.ID, wp_postmeta.meta_key
FROM wp_posts
JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
AND wp_postmeta.meta_key = '_wpcf_belongs_werkgever_id'
WHERE wp_posts.post_type = 'werkgever'
AND wp_postmeta.meta_value > '4623'
order by wp_posts.ID asc
to retrieve information from the CPT 'werkgevers'. I don't get any results.
Jacques
Beda,
I've something running now.
SELECT wp_posts.id, wp_postmeta.meta_key, wp_postmeta.meta_value, wp_posts.post_type
FROM wp_posts JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
AND wp_postmeta.meta_key = '_wpcf_belongs_werkgever_id'
WHERE 1=1
AND wp_posts.ID IN (3366,3346,3345,3344,3343)
AND wp_posts.post_type IN ('medewerker', 'werkgever')
AND ((wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private'))
ORDER BY wp_posts.post_date DESC
But how get I the toolset fields in the select statement and what is the syntax.
Jacques
