John Davidson

Message:

I have this sql query in wordpress with php

        $sql = "SELECT last_update.post_id FROM {$wpdb->postmeta} last_update";

        $sql .= " JOIN {$wpdb->posts} AS post ON last_update.post_id = post.ID";



        $sql .= $wpdb->prepare(" WHERE {$time} - last_update.meta_value > {$ttl} AND last_update.meta_key = %s", Importer::META_LAST_UPDATE);

        





        if (SyncConfig::getInstance()->option('published_only'))

            $sql .= " AND post.post_status = 'publish'";



        $sql .= $wpdb->prepare(" ORDER BY last_update.meta_value ASC LIMIT %d", self::getProductLimit());



        $product_ids = $wpdb->get_col($sql);



        shuffle($product_ids);

        error_log(print_r( $product_ids, true ));

What I want is to skip the results with a "NOT LIKE" for the wildcard result "Product data not found"

Normally I would do something like this

        $sql = "SELECT last_update.post_id FROM {$wpdb->postmeta} last_update";

        $sql .= " JOIN {$wpdb->posts} AS post ON last_update.post_id = post.ID";

        

        //QUITAR PRODUCTOS CON DATA NOT FOUND

        $sql .= " JOIN {$wpdb->postmeta} AS _ety_product_info ON last_update.post_id = _ety_product_info.post_id";





        $sql .= $wpdb->prepare(" WHERE {$time} - last_update.meta_value > {$ttl} AND last_update.meta_key = %s", Importer::META_LAST_UPDATE);

        

        //QUITAR PRODUCTOS CON DATA NOT FOUND

        $sql .= " AND _ety_product_info.meta_value NOT LIKE '%Product data not found%'";





        if (SyncConfig::getInstance()->option('published_only'))

            $sql .= " AND post.post_status = 'publish'";



        $sql .= $wpdb->prepare(" ORDER BY last_update.meta_value ASC LIMIT %d", self::getProductLimit());



        $product_ids = $wpdb->get_col($sql);



        shuffle($product_ids);

        error_log(print_r( $product_ids, true ));

But it gives wrong data.
The strangest thing is that if I remove the "NOT LIKE" and just leave it on "LIKE" it works delivering the expected result if the "_ety_product_info" column contains the value "Product data not found"

What am I doing wrong?
I want to exclude with "NOT LIKE" but it doesn't work.

Apparently this error only happens when I do the "JOIN"

Note: To identify the changes made in the original code with the edited one, just search for: //QUITAR PRODUCTOS CON DATA NOT FOUND

The final query
$sql it would be this:

SELECT last_update.post_id

FROM wp_postmeta last_update

JOIN wp_posts AS post ON last_update.post_id = post.ID

JOIN wp_postmeta AS _ety_product_info ON last_update.post_id = _ety_product_info.post_id

WHERE 1659698743 - last_update.meta_value > 86400

AND last_update.meta_key = '_ety_last_update' 

AND _ety_product_info.meta_value NOT LIKE '%Product data not found%' 

AND post.post_status = 'publish' 

ORDER BY last_update.meta_value ASC LIMIT 17

I also tried this but it didn't work.

$wild = '%';

$find = 'Product data not found';

$like = $wild . $wpdb->esc_like( $find ) . $wild;

$sql .= $wpdb->prepare(" AND _ety_product_info.meta_value NOT LIKE %s", $like);

---

My Answer:
"SELECT * FROM wp_posts
JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_type = 'post'
AND wp_postmeta.meta_key = 'author'
AND wp_postmeta.meta_value NOT LIKE '%John%'";