Search Post Not Working Properly

[iqbaljayadi-tonjoo]

Hi,

Tried to setup WordPress with Postgre and testing some basic functions. When I tried to search posts on WP Admin (WP Admin --> Posts --> All Posts) here are the results:

• Using single keyword: works properly
• Using two or more keywords: gave wp debug error message and pagination was not displayed

Here's the error (WP Debug is on):

WordPress database error: [ERROR: syntax error at or near ")" LINE 3: OR wp_posts.post_status = 'private')))) AS count0, wp_posts.... ^]
SELECT FOUND_ROWS()

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/wp-content/pg4wp/core.php(34) : eval()'d code:1854) in /var/www/html/wp-admin/admin-header.php on line 9

It seems that the regex on this line https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress/blob/v3/pg4wp/rewriters/SelectSQLRewriter.php#L28 needs to be fixed.

$sql = preg_replace('/\s+ORDER\s+BY\s+[^)]+/i', '', $sql);

Can someone give me a hint to fix this issue?

Thank you!

[mattbucci]

What is most helpful is if you can provide the full SQL statement which fails. This way it can be added to the test suite

[iqbaljayadi-tonjoo]

What is most helpful is if you can provide the full SQL statement which fails. This way it can be added to the test suite

Hi, is this enough?

---------------------
[1708049524.3155] Error running :
SELECT FOUND_ROWS()
---- converted to ----
SELECT COUNT(*) FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts."ID" = wp_term_relationships.object_id)
          WHERE 1=1  AND ( 
  wp_term_relationships.term_taxonomy_id IN (2)
) AND (((wp_posts.post_title ILIKE '%harga%') OR (wp_posts.post_excerpt ILIKE '%harga%') OR (wp_posts.post_content ILIKE '%harga%')) AND ((wp_posts.post_title ILIKE '%emas%') OR (wp_posts.post_excerpt ILIKE '%emas%') OR (wp_posts.post_content ILIKE '%emas%')))  AND ((wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' 
OR wp_posts.post_status = 'private')))
          ) AS count0, wp_posts.post_date DESC
----> ERROR:  syntax error at or near ")"
LINE 6:      ) AS count0, wp_posts.post_date DESC
             ^
---------------------

Thank you in advance

[mattbucci]

Yup that's perfect.

The issue is that the first statement is executed and then the 2nd statement found rows is translated based on that first statement. If the first statement executed correctly than that rewriter is fine, but specifically the found rows implementation needs to be adjusted. I can write a test for this and make a PR

[mattbucci]

my query is a little different but it results in the same error, the query is quite long

SELECT wp_posts.ID FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%Hello%') OR (wp_posts.post_excerpt LIKE '%Hello%') OR (wp_posts.post_content LIKE '%Hello%')) AND ((wp_posts.post_title LIKE '%world%') OR (wp_posts.post_excerpt LIKE '%world%') OR (wp_posts.post_content LIKE '%world%'))) 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'))) ORDER BY (CASE WHEN wp_posts.post_title LIKE '%Hello world%' THEN 1 WHEN wp_posts.post_title LIKE '%Hello%' AND wp_posts.post_title LIKE '%world%' THEN 2 WHEN wp_posts.post_title LIKE '%Hello%' OR wp_posts.post_title LIKE '%world%' THEN 3 WHEN wp_posts.post_excerpt LIKE '%Hello world%' THEN 4 WHEN wp_posts.post_content LIKE '%Hello world%' THEN 5 ELSE 6 END), wp_posts.post_date DESC LIMIT 0, 20

This is not valid postgres, it should be

SELECT wp_posts."ID" FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%Hello%') OR (wp_posts.post_excerpt LIKE '%Hello%') OR (wp_posts.post_content LIKE '%Hello%')) AND ((wp_posts.post_title LIKE '%world%') OR (wp_posts.post_excerpt LIKE '%world%') OR (wp_posts.post_content LIKE '%world%'))) 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'))) ORDER BY (CASE WHEN wp_posts.post_title LIKE '%Hello world%' THEN 1 WHEN wp_posts.post_title LIKE '%hello%' AND wp_posts.post_title LIKE '%world%' THEN 2 WHEN wp_posts.post_title LIKE '%Hello%' OR wp_posts.post_title LIKE '%world%' THEN 3 WHEN wp_posts.post_excerpt LIKE '%hello world%' THEN 4 WHEN wp_posts.post_content LIKE '%Hello world%' THEN 5 ELSE 6 END), wp_posts.post_date DESC OFFSET 0 LIMIT 20

After the regexes run this is changed to

SELECT COUNT(*) FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%hello%') OR (wp_posts.post_excerpt LIKE '%hello%') OR (wp_posts.post_content LIKE '%hello%')) AND ((wp_posts.post_title LIKE '%world%') OR (wp_posts.post_excerpt LIKE '%world%') OR (wp_posts.post_content LIKE '%world%'))) 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')))), wp_posts.post_date DESC

but this is incorrect, it should be

SELECT COUNT(*) FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%Hello%') OR (wp_posts.post_excerpt LIKE '%Hello%') OR (wp_posts.post_content LIKE '%Hello%')) AND ((wp_posts.post_title LIKE '%world%') OR (wp_posts.post_excerpt LIKE '%world%') OR (wp_posts.post_content LIKE '%world%'))) 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')))

basically ), wp_posts.post_date DESC must be removed from the end

[mattbucci]

New Order By regex added here: https://github.com/PostgreSQL-For-Wordpress/postgresql-for-wordpress/pull/102/files