Custom permalink request $request_noslash QUERY

  • Hi, i like this plugin.
    But i am a developer and i was in a sitauation where your plugin was messing up links and results. All went ok until we added pages for custom post types trying to overwrite the archive link with a custom link that was exactly like archive one.
    I tried to debug your plugin and i found that this query isn’t quite good:

    $sql = $wpdb->prepare("SELECT $wpdb->posts.ID, $wpdb->postmeta.meta_value, $wpdb->posts.post_type, $wpdb->posts.post_status FROM $wpdb->posts  ".
            "LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id) WHERE ".
            "  meta_key = 'custom_permalink' AND ".
            "  meta_value != '' AND ".
            "  ( LOWER(meta_value) = LEFT(LOWER('%s'), LENGTH(meta_value)) OR "....etc

    Since it return results when it should not.
    This query works for me, and all seems to be ok.
    Maybe, just maybe you should change the query. It’s just an observation so you can update the code better since this is a good plugin.

    $sql = $wpdb->prepare("SELECT DISTINCT(posts.ID) AS ID, posts.post_title as post_title, posts.post_status as post_status, posts.post_type as post_type,
                           TRIM(TRAILING '/' FROM post_meta.meta_value) as meta_value
                    FROM $wpdb->posts AS posts , $wpdb->postmeta AS post_meta
                    WHERE posts.ID = post_meta.post_id
                    AND post_meta.meta_key = 'custom_permalink'
                    AND post_meta.meta_value = '%s'
                    AND posts.post_status != 'trash'
                    AND posts.post_type != 'nav_menu_item'
                    LIMIT 1",$request_noslash);

    …in function custom_permalinks_request($query) { … }

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

  • Hi,

    that query is using the mysql LEFT function and in some situations can generate bad SEO situations.

    Example:
    – we have a page ‘father’ responding at https://mydomain/father/ (good http response code 200)
    – but with all other pages under /father/xxxxx/ -> https://mydomain/father/xxxxx/ the query recuperates the father page and show it with wrong http response code 200 instead 404

    Is there a good reason why that query use the LEFT operator?
    I suggest to use the follow query revisited:

    line 177 (on v 0.9.1)
    or
    line 172 (on v 0.9.2)

     $sql = $wpdb->prepare("SELECT $wpdb->posts.ID, $wpdb->postmeta.meta_value, $wpdb->posts.post_type, $wpdb->posts.post_status FROM $wpdb->posts  ".
      "LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id) WHERE ".
      "  meta_key = 'custom_permalink' AND ".
      "  meta_value != '' AND ".
      "  ( LOWER(meta_value) = LOWER('%s') OR ".
      "    LOWER(meta_value) = LOWER('%s') ) ".
      "  AND post_status != 'trash' AND post_type != 'nav_menu_item'".
      " ORDER BY LENGTH(meta_value) DESC, ".
      " FIELD(post_status,'publish','private','draft','auto-draft','inherit'),".
      " FIELD(post_type,'post','page'),".
      "$wpdb->posts.ID ASC  LIMIT 1",
      $request_noslash,
      $request_noslash."/");

    I 100% agree with this. One of the most annoying things has been the left join. Correct implementation is to serve up a 404.

    Plugin Author Sami Ahmed Siddiqui

    (@sasiddiqui)

    Hey,

    Good Point Guys, I think, it’s a time to change the query. I have created a query by considering the query suggested by @php-tech , I have just made some tweaks in it.

    Can you guys help me out in testing the new query though. Please replace the following lines from:

    $sql = $wpdb->prepare("SELECT $wpdb->posts.ID, $wpdb->postmeta.meta_value, $wpdb->posts.post_type, $wpdb->posts.post_status FROM $wpdb->posts  ".
            "LEFT JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id) WHERE ".
            "  meta_key = 'custom_permalink' AND ".
            "  meta_value != '' AND ".
            "  ( LOWER(meta_value) = LEFT(LOWER('%s'), LENGTH(meta_value)) OR ".
            "    LOWER(meta_value) = LEFT(LOWER('%s'), LENGTH(meta_value)) ) ".
            "  AND post_status != 'trash' AND post_type != 'nav_menu_item'".
            " ORDER BY LENGTH(meta_value) DESC, ".
            " FIELD(post_status,'publish','private','draft','auto-draft','inherit'),".
            " FIELD(post_type,'post','page'),".
            "$wpdb->posts.ID ASC  LIMIT 1",
    $request_noslash,
    $request_noslash."/");

    to:

    $sql = $wpdb->prepare("SELECT p.ID as ID, p.meta_value as meta_value, posts.post_type as post_type, p.post_status as post_status FROM $wpdb->posts AS p, $wpdb->postmeta as pm WHERE p.ID = pm.post_id AND pm.meta_key = 'custom_permalink' AND (pm.meta_value = '%s' OR pm.meta_value = '%s') AND posts.post_status != 'trash' AND posts.post_type != 'nav_menu_item' LIMIT 1", $request_noslash, $request_noslash."/");

    I have not tested this myself till now. I have wrote this query but, i think it may work. I’ll try to check this query by myself too in some later time.

    Thanks for the help in advance.

    Regards,
    Sami

    Plugin Author Sami Ahmed Siddiqui

    (@sasiddiqui)

    Query has been updated on the latest version(1.0). Please have a look guys ( @php-tech @abindelli )

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Custom permalink request $request_noslash QUERY’ is closed to new replies.