Database Error when used with Show Single Variations

  • Resolved jamesbirchmore

    (@jamesbirchmore)


    4 years, 7 months ago

    There appears to be a compatibility issue with the Relevanssi search plugin and the Show Single Variations plugin. https://iconicwp.com/products/woocommerce-show-single-variations/

    When Show Single Variations is active, Relevanssi no longer works. It produces a database error with the following query

    SELECT COUNT(DISTINCT(relevanssi.doc))
    FROM ***_relevanssi AS relevanssi
    WHERE relevanssi.term = ‘*******’
    AND relevanssi.doc NOT IN (
    SELECT DISTINCT(tr.object_id)
    FROM ***_term_relationships AS tr
    WHERE tr.term_taxonomy_id IN (6,9))
    AND relevanssi.doc NOT IN (
    SELECT DISTINCT(tr.object_id)
    FROM ***_term_relationships AS tr
    WHERE tr.term_taxonomy_id IN (498))
    AND relevanssi.doc NOT IN (SELECT ID
    FROM ***_posts
    WHERE post_parent IN ())
    AND ( relevanssi.doc IN (
    SELECT DISTINCT(posts.ID)
    FROM ***_posts AS posts
    WHERE posts.post_type IN (‘product’, ‘product_variation’) ) )

    With the following error message

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘)) AND ( relevanssi.doc IN ( SELECT DISTINCT(posts.ID) FROM ***_posts AS ‘ at line 8

    With the Show Single Variation plugin deactivated Relevanssi works as expected.

    I have also sent this error report to the IconicWP developers incase its an issue on their end.

    Any further info needed please let me know.

    Thanks,

    James

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author Mikko Saari

    (@msaari)

    There’s an empty WHERE post_parent IN ()) clause there. If you disable Show Single Variation, does that go away? What kind of post_parent parameter does Show Single Variation add to WP_Query?

    Thread Starter jamesbirchmore

    (@jamesbirchmore)

    Yes, that goes away when Show Single Variation is disabled

    SELECT COUNT(DISTINCT(relevanssi.doc))
    FROM ***_relevanssi AS relevanssi
    WHERE relevanssi.term = ‘*******’
    AND relevanssi.doc NOT IN (
    SELECT DISTINCT(tr.object_id)
    FROM ***_term_relationships AS tr
    WHERE tr.term_taxonomy_id IN (6,9))
    AND relevanssi.doc NOT IN (
    SELECT DISTINCT(tr.object_id)
    FROM ***_term_relationships AS tr
    WHERE tr.term_taxonomy_id IN (498))
    AND ( relevanssi.doc IN (
    SELECT DISTINCT(posts.ID)
    FROM ***_posts AS posts
    WHERE posts.post_type IN (‘product’) ) )

    I believe it adds a post_parent__not_in to the WP_Query

    Plugin Author Mikko Saari

    (@msaari)

    Hmm… something’s wrong with that then. Can you check the exact value of the post_parent__not_in parameter it adds? Relevanssi should be able to ignore the empty value. This was buggy in Relevanssi before, but was fixed many versions ago in November 2018, so I doubt you’re running Relevanssi that old.

    Thread Starter jamesbirchmore

    (@jamesbirchmore)

    I believe it’s just passing in a single string value

    string(5) "18765"

    Using the latest version of the plugin Version 4.7.2.1

    I have referred the plugin developers to this thread, so hopefully they can fill in the rest.

    Plugin Author Mikko Saari

    (@msaari)

    Thought that might be the case – according to the Codex the post_parent__not_in value should be an array and that’s what Relevanssi expects. It should be array( '18765' ).

    If the Show Single Variation developers will fix that, good, but if they won’t, you can always add a filter function on relevanssi_modify_wp_query to change the parameter to an array before Relevanssi sees it.

    Thread Starter jamesbirchmore

    (@jamesbirchmore)

    Thank you for your help with this. The plugin developers have identified the issue, and resolved it.

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Database Error when used with Show Single Variations’ is closed to new replies.