Support » Plugin: ACF: Better Search » Imports from WP All Imports aren’s searchable

  • Resolved jmaver

    (@jmaver)


    I have a bunch of products with product IDs that are ACF. Any products that were imported fail to show up in search results, any manually added afterwards succeed.

    So, I exported all the the products in my local test instance, deleted all of them, and then reimported them again. Now none are searchable.

    What can I do to fix the search for imported posts?

Viewing 15 replies - 1 through 15 (of 23 total)
  • Thread Starter jmaver

    (@jmaver)

    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

    Thread Starter jmaver

    (@jmaver)

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

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    @jmaver, can you check version 3.5.1?

    Thread Starter jmaver

    (@jmaver)

    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.

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    This is how your structure looks for one field:

    ---------------------------------------------------------
    | meta_id | post_id | meta_key    | meta_value          |
    ---------------------------------------------------------
    | 7953    | 913     | _product_id | field_5d89bb00fc4a7 |
    | 7954    | 913     | product_id  | 621                 |
    ---------------------------------------------------------

    This is what the default ACF field structure should look like (this is used by the plugin):

    ---------------------------------------------------------
    | meta_id | post_id | meta_key    | meta_value          |
    ---------------------------------------------------------
    | 7953    | 913     | product_id  | 621                 |
    | 7954    | 913     | _product_id | field_5d89bb00fc4a7 |
    ---------------------------------------------------------

    The query uses, among others, the condition:
    ((b.meta_id = a.meta_id + @@auto_increment_increment) AND (b.meta_key LIKE CONCAT(\'\_\', a.meta_key)))

    It could be simpler and then everything would work for you:
    (b.meta_key LIKE CONCAT(\'\_\', a.meta_key))

    But such a query is much slower. By several hundred percent.

    A compromise could be used:
    (((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)))

    But even that is 30% slower. And this is very much on large websites.

    At the moment, I have no solution that would not mean a large decrease in performance. Unfortunately, this is a disadvantage of WordPress, where we have most of the data in the wp_postmeta table.

    Thread Starter jmaver

    (@jmaver)

    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?

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    Unfortunately, I do not have such a solution.

    Gaurang Dabhi

    (@guddu1315)

    Hi I face the similar problem.

    I imported data into custom CPT and searching does not work.
    It works fine for data I added manually. There are around 13k data. So I can’t add them manually. Any solution?

    Thanks.

    kryonos

    (@kryonos)

    HI !
    Same here, I face the same problem…
    Maybe a query to update all the meta switching the ids for a particular key?

    Thread Starter jmaver

    (@jmaver)

    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?

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    Which option did we use in the SQL query? In this, when the results work?

    Option A:
    ((b.meta_id = a.meta_id + @@auto_increment_increment) AND (b.meta_key LIKE CONCAT(\'\_\', a.meta_key)))

    Option B:
    (b.meta_key LIKE CONCAT(\'\_\', a.meta_key))

    Option C:
    (((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)))

    Thread Starter jmaver

    (@jmaver)

    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
          );
    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    @guddu1315 and @kryonos, Can I also ask you for an answer?

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    I have made changes to the plugin, i.e. a new switch on the plugin management screen to enable searching among posts with the reverse order of data in the _postmeta table.

    Can you test this version of the plugin?
    https://wetransfer.com/downloads/17d6ac0eedbfee2bdc36dd313c3e0b4720200327155446/bc51aa1ff44fa7ef48a69af796f600bb20200327155446/09e56e

    kryonos

    (@kryonos)

    @mateuszgbiorczyk I used the version C too
    Thanks!

Viewing 15 replies - 1 through 15 (of 23 total)
  • The topic ‘Imports from WP All Imports aren’s searchable’ is closed to new replies.