Hello,
We have an 1:n association: A →* B
And B has a custom field "X".
Now I want to get all A where X of B > 0.
How can I do this by code?
Before the major upgrade I was just able to do a basic SQL query using the associations table.
But through the new format, this isn't working. I now must use another table to get the origina post ids...
There must be a better solution?
Hello and thank you for contacting the Toolset support.
In fact, the last upgrade introduced the new table toolset_connected_elements that actually holds the ids of the connected posts. The SQL query needs to be adapted.
I wonder how you are manipulating SQL in the view/archive? Can you elaborate more on your use case?
Can you share the SQL query that you were using?
On the other hand, I would recommend using our APIs:
- https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/
- https://toolset.com/documentation/programmer-reference/views-filters/#wpv_filter_query
- https://toolset.com/documentation/customizing-sites-using-php/post-relationships-api/how-to-migrate-your-site-to-new-post-relationships/
Please consider that custom code is beyond the scope of the support forum. https://toolset.com/toolset-support-policy/
If you are not comfortable with programming, you may need to hire a developer. You can check our partners. here https://toolset.com/contractors/
Hello Jamal,
I don't use (need) a view for this, just plain SQL.
My current query is quite simple, but with the new changes I have to add another JOIN, which makes it more complicated.
I wonder why there is not a method like toolset_get_all_posts_from_relation(parent|child, relation-slug, WHERE)?
SELECT parent.parent_id, meta.post_id
FROM {$wpdb->prefix}toolset_associations parent, {$wpdb->postmeta} meta, {$wpdb->posts} post
WHERE parent.child_id = meta.post_id
AND meta.meta_key = 'wpcf-ende'
AND meta.meta_value >= %d
AND meta.post_id = post.id
AND post.post_type = 'mi-dates-events'
AND parent.parent_id
IN (SELECT id FROM {$wpdb->posts} WHERE post_type = 'mi-event')
GROUP BY parent.parent_id ORDER BY meta_value ASC;
(Generally it is quite annoying, that there is no way to export synchronize views/fields/... within a theme (like in ACF) , and I always have to use the module import/export manually → this is why I try to avoid using views directly. → in addition the new Toolset Blocks is even more annoying, as the is no central place to edit views and the blocks are not so mighty like the classic views... )
Hello and my apologies for the late reply.
I tested some SQL queries on the new database schema, and I could not come up with the correct query because of my little knowledge of SQL. I still would like to share the following query that might help you figure out how to join the new tables:
SELECT Count(*)
FROM wp_posts cities
LEFT JOIN wp_toolset_connected_elements wtce
ON wtce.element_id = cities.id
LEFT JOIN wp_toolset_associations wta
ON wta.parent_id = wtce.id
LEFT JOIN wp_toolset_connected_elements wtce2
ON wtce2.id = wta.child_id
LEFT JOIN (SELECT *
FROM wp_posts
WHERE post_type = "entreprise") ent
ON ent.id = wtce2.element_id
WHERE cities.post_type = "ville"
AND ent.id IS NULL
You are right, currently, with blocks, we do not have a central location to manage views, but this will be added in the coming release when we will allow to create/edit views in the block editor without passing from a page or a content template.
Let me know if you have any questions.
