0

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.

3
  • You want posts, not meta values, therefore you should use WP_Query with the meta_query parameter. There are tons of articels out there about this subject.
    – Hans
    Oct 7, 2018 at 18:43
  • Why are you using LIKE for what seems like an exact match? Also the string of chars is normal for a LIKE query
    – Milo
    Oct 8, 2018 at 13:06
  • It's not an exact match. The string appears somewhere in the content. Oct 15, 2018 at 22:02

1 Answer 1

1

Try this:

$meta_query_args = array(
    'meta_query'=>array(
        array(
            'key' => '_cornerstone_data',
            'value' => $attachment_url,
            'compare' => 'LIKE',
        ),
    )
);

$meta_query = new WP_Query( $meta_query_args );

$posts = $meta_query->posts;
5
  • I tried it your way, as well as a similar code with only some changes, which both returned way too many results. Oct 8, 2018 at 7:23
  • Did you try without 'relation'=>'AND'? Oct 8, 2018 at 8:33
  • Also try to put url into brackets ' "https:// ... " ', check without % sign and see wordpress.stackexchange.com/a/159433/147957 Oct 8, 2018 at 8:43
  • Neither of these work. I printed out the SQL query that is being generated with my query and it doesn't even contain my meta field... see my post above. Oct 8, 2018 at 9:43
  • I deleted the "meta_" part from the query and I have the meta field in my result at least. But I have a weird very long string of characters in my SQL query now. Oct 8, 2018 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.