jmaver's Replies | WordPress.org

Forum Replies Created

Viewing 9 replies - 1 through 9 (of 9 total)

Forum: Plugins
In reply to: [ACF: Better Search] Imports from WP All Imports aren’s searchable

Thread Starter jmaver
(@jmaver)

6 years, 1 month ago

@mateuszgbiorczyk of course, that post wasn’t so much for you as for anyone that wants try to fix the underlying issue that WP All Imports caused with the field order.

Your plugin change was great for me because it allowed our live site to continue to function with the bad state of the import. Thank you for the quick turnaround.

It was just bothering me all weekend that I couldn’t fix the actual problem in the database, so I spent a bunch of time today relearning SQL enough to get rid of the issue. I expect that almost everyone else in the world will not ever look at this code, but instead will benefit from your more recent change. It would not make sense to put into your plugin. (I see I used the word “your” in my previous post, but I meant “the reader” who discovers this post later.

That filter is also very useful. Thank you!

Forum: Plugins
In reply to: [ACF: Better Search] Imports from WP All Imports aren’s searchable
Thread Starter jmaver
(@jmaver)

6 years, 1 month ago
Ok, the slow media library got me, so I spent the time to create this (I am sure) horrible SQL statement to fix the ordering issue for the fields I care about.

I take no responsibility for this, and it may wreck your database. I did mine on a local copy of my database and used Adminer to test.

Here is the code:
```
UPDATE wp_postmeta AS a
INNER JOIN wp_postmeta as b USING (post_id)
SET a.meta_key=(@temp:=a.meta_key), a.meta_key = b.meta_key, b.meta_key = @temp, a.meta_value=(@temp2:=a.meta_value), a.meta_value = b.meta_value, b.meta_value = @temp2
WHERE a.meta_key IN ('field1', 'field2')
 AND b.meta_key = CONCAT("_",a.meta_key) AND (a.meta_id > b.meta_id);
```
Steps if you want to play with it:
1) Get a list of the fields you care about. I exported mine for one field group in ACF->Tools->export, brought it into excel, used a dataquery to extract the field names, then added the commas. You only need the fields without the “_” at the beginning.

2) Make sure you actually have this problem, with the “_” versions of fields coming before the non-“_” versions. You can use this code to test:
```
select a.post_id, a.meta_id, a.meta_key, a.meta_value, b.meta_id, b.meta_key, a.meta_value from wp_postmeta a
INNER JOIN wp_postmeta b
USING (post_id)
where a.meta_key IN ('field1', 'field2')
 and b.meta_key = CONCAT("_",a.meta_key) and (a.meta_id > b.meta_id )
order by a.meta_id ASC
```
3) Get some tests in place first. Search on home page and in media library for the fields. Turn off the new “reverse” checkbox in the test version we just got.

4) Run the update sql

5) Run the tests again and see if they work and if they are fast.
Forum: Plugins
In reply to: [ACF: Better Search] Imports from WP All Imports aren’s searchable

Thread Starter jmaver
(@jmaver)

6 years, 1 month ago

Trying now and timing with a few options:

Results:
https://www.dropbox.com/s/ehb0j6z6k2calbr/ACF%20Search%20Results.png?dl=0

So if I don’t have ACF Better Search, I get no acf fields in results, which makes sense.

If I don’t include the reverse query, then I don’t get imports.

With the reverse query, all works find, although a little slower for search, except in the media library, where is is unbelievably slow.

Is there a way to shut off ACF search for media library queries?

Forum: Plugins
In reply to: [ACF: Better Search] Imports from WP All Imports aren’s searchable
Thread Starter jmaver
(@jmaver)

6 years, 1 month ago
I used C since manual records have the correct ordering and imported have the reverse.
```
      $parts[] = sprintf(
        'LEFT JOIN %s AS b ON (((b.meta_id = a.meta_id + @@auto_increment_increment) OR (b.meta_id = a.meta_id - @@auto_increment_increment)) AND (b.meta_key LIKE CONCAT(\'\_\', a.meta_key)))',
        $this->wpdb->postmeta,
        $this->wpdb->posts
      );
```
Forum: Plugins
In reply to: [ACF: Better Search] Imports from WP All Imports aren’s searchable

Thread Starter jmaver
(@jmaver)

6 years, 1 month ago

I temporarily replaced the sql statement in the plugin’s join.php with the compromise code listed above. Works fine. Perhaps slower, but at least works.

Mateusz, perhaps there would be a config switch added that does this for us? Turn it on, and it uses the compromise, off by default?

Forum: Plugins
In reply to: [ACF: Better Search] Imports from WP All Imports aren’s searchable

Thread Starter jmaver
(@jmaver)

6 years, 1 month ago

Thank you, that makes sense.

I bet when the import happens, it sorts the fields before adding, so “_product_id” comes before “product_id”.

I do see them in the “right” order for the newer, manual ones.

I wish there were a quick sql statement I could run that would reverse them all, or a different way to handle the import in WPAllImports

I don’t know SQL enough to do anything like that. Any chance you do?

Forum: Plugins
In reply to: [ACF: Better Search] Imports from WP All Imports aren’s searchable

Thread Starter jmaver
(@jmaver)

6 years, 1 month ago

That was the version I started with – I cam for the titles not showing up anymore in search, which was the 3.5 bug fixed in 3.5.1. So, the titles came back, but the product ids never did. But the older version works for everything so far.

Forum: Plugins
In reply to: [ACF: Better Search] Imports from WP All Imports aren’s searchable

Thread Starter jmaver
(@jmaver)

6 years, 1 month ago

Update, search works fine rolling back to 3.4.3. So, something in the 3.5.0 update broke something

Forum: Plugins
In reply to: [ACF: Better Search] Imports from WP All Imports aren’s searchable

Thread Starter jmaver
(@jmaver)

6 years, 1 month ago

Info you asked for:
1) Images in here – https://www.dropbox.com/sh/25h8wu3ea1qnc00/AAB1gGVo5etc5cSoqAbBJhuAa?dl=0

2) SQL for request.php: SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_postmeta AS a ON (a.post_id = wp_posts.ID) LEFT JOIN wp_postmeta AS b ON ((b.meta_id = a.meta_id + @@auto_increment_increment) AND (b.meta_key LIKE CONCAT(‘\_’, a.meta_key))) LEFT JOIN wp_posts AS c ON ((c.post_name = b.meta_value) AND (c.post_type = ‘acf-field’) AND ((c.post_content LIKE ‘%:”text”%’) OR (c.post_content LIKE ‘%:”textarea”%’) OR (c.post_content LIKE ‘%:”wysiwyg”%’))) WHERE 1=1 AND wp_posts.ID NOT IN (3837,3838,3862) AND (
(
wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (9)
)
)
) AND (((b.meta_id IS NOT NULL) AND (c.ID IS NOT NULL) AND (a.meta_value LIKE ‘%621%’)) OR ((wp_posts.post_title LIKE ‘%621%’) OR (wp_posts.post_content LIKE ‘%621%’) OR (wp_posts.post_excerpt LIKE ‘%621%’))) AND wp_posts.post_type IN (‘post’, ‘page’, ‘products’) AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘acf-disabled’) GROUP BY wp_posts.ID ORDER BY wp_posts.post_title LIKE ‘{ca4778bc3c33046ab5cad6764eb4c892f719b023b7412fad63f31c79c61a2e92}621{ca4778bc3c33046ab5cad6764eb4c892f719b023b7412fad63f31c79c61a2e92}’ DESC, wp_posts.post_date DESC LIMIT 0, 10

3) Post ID I want to find: 913

Viewing 9 replies - 1 through 9 (of 9 total)