I am trying to get every post, that has a specific URL in the meta key _cornerstone_data
. From the WordPress documentary I was able to build this query, that probably gets me close to what I'm trying to achieve:
$meta_query_args = array(
array(
'key' => '_cornerstone_data',
'value' => $attachment_url,
'compare' => 'LIKE',
),
);
$meta_query = new WP_Meta_Query( $meta_query_args );
$mysql =$meta_query->get_sql(
'post',
$wpdb->posts,
'ID'
);
The result is an array that looks like this:
'join' => string 'INNER JOIN fx30k_postmeta ON ( fx30k_posts.ID = fx30k_postmeta.post_id )'
'where' => string 'AND ( ( fx30k_postmeta.meta_key = '_cornerstone_data' AND fx30k_postmeta.meta_value LIKE '{c6fc…
AND ( ( fx30k_postmeta.meta_key = '_cornerstone_data' AND fx30k_postmeta.meta_value LIKE '{c6fc239abf1a24a4f555ce84bb7edf17dd16d1cd29f38c738529c38aafc546ff}https://usd1.wireless-empire.de/wp-content/uploads/usd-ISO-27001-Foundation-Seminar-2018.pdf{c6fc239abf1a24a4f555ce84bb7edf17dd16d1cd29f38c738529c38aafc546ff}' )
)
However, I don't know what to do with this result and I didn't find any documentary or tutorial explaining this. How can I further use this, to get the posts that I am looking for?
NEW APPROACH
This is my new approach to the problem, incorporating some of the suggestions here. However, I get a lot of wrong results:
$meta_query_args = array(
'post_type' => array( 'post', 'page' ),
'meta_query' => array(
'relation' => 'AND',
array(
'key' => '_cornerstone_data',
'value' => $attachment_url,
'compare' => 'LIKE',
)
),
);
$meta_query = new WP_Query( $meta_query_args );
$posts = $meta_query->posts;
When I put it in plain SQL, this is what should be happening:
SELECT *
FROM `wp_postmeta`
WHERE `meta_key` = '_cornerstone_data'
AND `meta_value` LIKE '%https://example.com/wp-content/uploads/this.pdf%'
This query actually returns the correct result. How do I put this in a WP_QUERY
?
Printing out the SQL query that is being generated with my query, it looks like this:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) JOIN wp_icl_translations t
ON wp_posts.ID = t.element_id
AND t.element_type = CONCAT('post_', wp_posts.post_type) WHERE 1=1 AND (
( wp_postmeta.meta_key = '_cornerstone_data' AND wp_postmeta.meta_value LIKE '{645cebb1ce3cff4f917092440a092979319a39817e68ccd213b321a8cef9f388}https://example.com/wp-content/uploads/this.pdf{645cebb1ce3cff4f917092440a092979319a39817e68ccd213b321a8cef9f388}' )
) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private') AND ( ( ( t.language_code = 'de' OR 0 ) AND wp_posts.post_type IN ('post','page','x-portfolio' ) ) OR wp_posts.post_type NOT IN ('post','page','x-portfolio' ) ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10
I don't know what this very long string inside this query is supposed to be. It's where there should actually be %
. But even when I take this query, replace the %
and try it out on my database (with phpMyAdmin), it doesn't return the right results.
WP_Query
with themeta_query
parameter. There are tons of articels out there about this subject.