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.

  • 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. – Michael Oct 7 '18 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 '18 at 13:06
  • It's not an exact match. The string appears somewhere in the content. – TheKidsWantDjent Oct 15 '18 at 22:02
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;
  • I tried it your way, as well as a similar code with only some changes, which both returned way too many results. – TheKidsWantDjent Oct 8 '18 at 7:23
  • Did you try without 'relation'=>'AND'? – Piotr Kucułyma Oct 8 '18 at 8:33
  • Also try to put url into brackets ' "https:// ... " ', check without % sign and see wordpress.stackexchange.com/a/159433/147957 – Piotr Kucułyma Oct 8 '18 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. – TheKidsWantDjent Oct 8 '18 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. – TheKidsWantDjent Oct 8 '18 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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