Question 30686 in Wordpress Question posted in
The official Wordpress documentation can be found here.

SQL NOT EXISTS in combination with LIKE doesn't work – WordPress

I’m trying to return those posts with post_type = "attachment" , where their meta_value (url) doesn’t appear in any post_content .

For now I’m trying to give the meta_value manually, which in this case is a image name speed1

I’ve cheked and there are some posts with the image "speed1.jpg" in the post_content.

When I select those WITH the word in the content, it works, the problem arises when I try to select everything else but those with the word in the content, using the query:

SELECT 
    i.ID,
    i.post_content,
    i.post_type
FROM
    wp_posts i
WHERE
    i.post_type = 'attachment'
    AND NOT EXISTS (SELECT * FROM wp_posts p WHERE p.post_type <> 'attachment' AND p.post_content LIKE "%speed1%")

this always returns empty. Why?

Tags:

3

Answers


  1. 0

    Could you do something like this

    SELECT 
    i.ID,
    i.post_content,
    i.post_type
FROM
    wp_posts i
WHERE
    i.post_type = 'attachment'
    AND i.post_content NOT LIKE '%speed1%';
    Login or Signup to reply.
  2. 0

    Literal values must be surrounded by single quotes and not by apostrophes (double quote). Double quote are requested to surround object name in SQL (table name, column name…).

    In your query the part :

    NOT LIKE "%speed1%"

    Is looking after a column nammed %speed1%

    It is not the same as comparing to a litteral static value…
    I think you will better do :

    NOT LIKE '%speed1%'
    Login or Signup to reply.
  3. 0

    Here is the simple CTE that I am using as a crude mock of wp_posts for the following queries –

    WITH wp_posts (ID, post_content, post_type, meta_value) AS (
    SELECT 1, null, 'attachment', 'speed1' UNION ALL
    SELECT 2, null, 'attachment', 'speed2' UNION ALL
    SELECT 3, null, 'attachment', 'speed3' UNION ALL
    SELECT 4, 'blah blah speed1 blah blah', 'post', null UNION ALL
    SELECT 5, 'blah blah speed3 blah blah', 'post', null
)

    which looks like this as a table –

    ID post_content post_type meta_value
    1 speed1 attachment speed1
    2 speed2 attachment speed2
    3 speed3 attachment speed3
    4 blah blah speed1 blah blah post
    5 blah blah speed3 blah blah post

    Your current sub-query in NOT EXISTS –

    SELECT *
FROM wp_posts p
WHERE p.post_type <> 'attachment'
AND p.post_content LIKE "%speed1%"

    always returns the row with ID 4 from my mock. This means NOT EXISTS always returns false as there is a result.

    If you change the sub-query to use the meta_value from the outer query you have something which makes more sense –

    SELECT 
    i.ID,
    i.post_content,
    i.post_type
FROM
    wp_posts i
WHERE
    i.post_type = 'attachment'
    AND NOT EXISTS (
        SELECT *
        FROM wp_posts p
        WHERE p.post_type <> 'attachment'
        AND p.post_content LIKE CONCAT('%', i.meta_value, '%')
    )

    This returns the row with ID 2 as ‘speed2’ is not in the post_content of either of the two wp_posts of type ‘post’. I hope you are able to make sense of this simple demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search