The autocomplete selection widget to add an item of content to a pane doesn't use a sort order for the content. This is a problem when you want to add a node whose title is a substring of titles of other nodes. The widget always returns the top node in the list of matches, so if the exact match you're after is the second result, there is no way to reference it.

For example, take the following three nodes in order of node creation: "Test FAQ", "Test node", "Test".

If you want to reference "Test", the autocomplete select list widget always lists "Test FAQ" first and will thus always select "Test FAQ". A work-around is to change the sort order of the view on a case by case basis, but that's not really workable.

This issue is more or less related to #2086839: Autocompletion should always return the exact match if it exists (in exposed filters). I have posted a snippet of custom module code on that issue that fixes the sort order in Panopoly.

Proposed solution

Now that #2334439: Switch "Content Item" widget to use some kind of entity reference field rather than title with autocomplete filter is merged, the "Content Item" View is using an NID filter underneath to actually query for the node after it has been saved. However, this doesn't help with the selection problem when two nodes have the same title!

I propose that:

  1. Use a Views hook to add the NID to the title when the view is being queried for the autocomplete (should be able to detect that with some variation on if (arg(0) == 'autocomplete_filter') { ... })
  2. Use an other Views hook to extract the NID from the title filter, and use that for the NID field (clearing out the title)
  3. Modify the submit handler on the pane configuration from #2334439: Switch "Content Item" widget to use some kind of entity reference field rather than title with autocomplete filter to extract the NID from the title configuration, rather than re-querying the View

And in Panopoly 2.x, let's use a custom content pane for this rather than a View. ;-)

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

mglaman’s picture

Issue summary: View changes
Status: Active » Needs review
FileSize
953 bytes

Here is a patch which alters piece_of_content display to sort content titles by shortest content title for substringed content titles.

dsnopek’s picture

Status: Needs review » Needs work

This could be a good interim fix until we have #2334439: Switch "Content Item" widget to use some kind of entity reference field rather than title with autocomplete filter

However, I'm a little worried about the performance implications. Doing ORDER BY node_title should be fine, because it'll use the index on node.title. But specifying expressions in ORDER BY may mean that the query will have to pull every row on the table, do the expression, and then sort - which could be very slow with a lot of nodes.

So, before merging this, I'd like to see a DESCRIBE run on the query before and after the patch, in order to see if it's doing a full table scan after the patch.

dsnopek’s picture

Title: Content autocomplete widget selection problem » Problems selecting specific "Content item" when titles are the same or similar

More descriptive rename.

dsnopek’s picture

Version: 7.x-1.0-rc5 » 7.x-1.x-dev
Issue summary: View changes
cafuego’s picture

Well, to be fair, a slow query beats a query that doesn't return the results you need ;-)

cafuego’s picture

Not a huge site, ~4000 nodes. I note there isn't in fact an index on node title in Drupal 7. There's a combined index on node title+type and that's not getting used regardless of the extra sort arguments.

DESCRIBE SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created FROM node node WHERE (node.status = '1') AND node.title LIKE '%string%' ORDER BY node_created DESC;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: node
         type: ref
possible_keys: node_status_type
          key: node_status_type
      key_len: 4
          ref: const
         rows: 1832
        Extra: Using where; Using filesort

Actual query time according to mysql client: 0.01s, 84 rows in result.

DESCRIBE SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created FROM node node WHERE (node.status = '1') AND node.title LIKE '%string%' ORDER BY LENGTH(node_title) ASC\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: node
         type: ref
possible_keys: node_status_type
          key: node_status_type
      key_len: 4
          ref: const
         rows: 1832
        Extra: Using where; Using filesort

Actual query time according to mysql client: 0.01s, 84 rows in result.

DESCRIBE SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created FROM node node WHERE (node.status = '1') AND node.title LIKE '%string%' ORDER BY node_title = 'string' DESC, LENGTH(node_title) ASC\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: node
         type: ref
possible_keys: node_status_type
          key: node_status_type
      key_len: 4
          ref: const
         rows: 1832
        Extra: Using where; Using filesort

Actual query time according to mysql client: 0.01s, 84 rows in result.

So totes RTBC as far as I'm concerned - though I can't change the status as I actually wrote the original snippet this patch is based off :-)

dsnopek’s picture

Thanks for doing the DESCRIBE! That's super helpful. :-)

It's really interesting that the first query (with ORDER BY node_created DESC) is "Using filesort" just like the following two. I would have expected that it was just "Using where" because there is an index on node.created.

(For those who don't know, the "Using filesort" means that it's making a temporary table with all the results and then sorting them, which takes more time in linear proportion to the number of results, ie. O(n) time, which can be really slow if there are a lot of results. If an index can be used, then it can take O(log n) time, or logarithmically proportional to the number of results, which is MUCH faster.)

We could actually optimize this original query by adding a new index:

CREATE INDEX node_title_created ON node (title, created);

Which changes the output of DESCRIBE to:

DESCRIBE SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created FROM node node WHERE (node.status = '1') AND node.title LIKE '%string%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: node
         type: ref
possible_keys: node_status_type
          key: node_status_type
      key_len: 4
          ref: const
         rows: 10
        Extra: Using where

Unfortunately, this same technique can't be used to improve the query with ORDER BY LENGTH(node_title) because MySQL can't index the result of an expression. :-/ It has pull all the result into a temporary table, calculate the expression and then sort the whole thing. This could get really slow with a large enough result set.

However, I do agree with this sentiment:

Well, to be fair, a slow query beats a query that doesn't return the results you need ;-)

And now that we've merged #2334439: Switch "Content Item" widget to use some kind of entity reference field rather than title with autocomplete filter, we could potentially make it so that these extra ORDER BY arguments are only added when we're searching by title, and not by nid, which means this slowness would only happen when using the UI to configure the widget and not when rendering it!

So, I'd be fine with merging this, if this code:

+++ b/panopoly_core.module
@@ -282,3 +282,19 @@ function _panopoly_core_rollback_demo_content($classes = array()) {
+    if (!empty($view->exposed_data['title'])) {
+      $query->orderby[] = array('field' => "(node_title = '" . $view->exposed_data['title']. "')", 'direction' => 'DESC');
+    }
+    // Length of matched title sort.
+    $query->orderby[] = array('field' => 'LENGTH(node_title)', 'direction' => 'ASC');

... were changed to only mess with the $query->orderby at all if there was a value for the exposed title filter, and some testing were done to make sure this code wasn't activated when actually rendering the "Content Item" widget!

We should also remove the ORDER BY node_created which it sounds like is in the View?

Thanks!

hfarrier’s picture

I agree this is an issue that needs to be resolved. We use the Single Piece of Content widget to display an article node on our homepage. We had a high-profile, time-sensitive article to post on our home page. I spent way to much time trying to get it to work only to find out that it was because the content had a similar title to another article. The only (quick) solution was to un-publish the article node that we didn't want to feature so that we could select the article that we did want to feature. Otherwise, the widget would not pull in the correct piece of content.

dsnopek’s picture

Priority: Normal » Major

It's been a long time since I looked into this one, but I just verified that I can still reproduce it on a fresh install of Panopoly 1.28. I agree this is a serious bug, so I updated the priority to Major.

mglaman's solution from #1 won't work for the performance reasons laid out in #7. But the proposal in the issue summary looks workable!