Sort by Book Hierarchy does not exactly repeat book hierarchy

Please see Obtaining support for Views issues. Exporting your view and pasting it here will be especially helpful.

I'll confirm this issue - sorting by Book: Hierarchy does not sort correctly, but only if a book has been re-ordered after the items have been created.

When adding a new book, the book module says, "Pages at a given level are ordered first by weight and then by title." I can't find any way to replicate that ordering with views.

To replicate, create a new book, and give it some children, like so:

* Chapter 1
* Chapter 2
** Paragraph 1
** Paragraph 2
** Paragraph 3
* Chapter 3

Then create a new view, add a "Book: Top Level Book" relationship and mark the relationship as "required". In sort criteria add "Book: Hierarchy" and then preview your view. So far so good - this should display everything correctly sorted.

Then, edit the order of your book and swap paragraph 2 with paragraph 1. This new ordering is correctly reflected by the book module, but the view still has the original ordering. Now move Chapter 2 to the bottom, so that Chapter 3 is above it. Again, book.module correctly shows the ordering, but views doesn't.

This looks like this.

Let's make it easier, and edit those titles so that the order makes sense to us (just like an author might want to swap the order of his chapters), so that it looks like this.

But when viewing this book in a view, it returns the order like this.

* My awesome book
* Chapter 1
* Chapter 3
** Paragraph 2
** Paragraph 1
** Paragraph 3
* Chapter 2

If you've been able to track the changing chapter and paragraph names, you'll see that this order is exactly the same as the initial order - before we moved and renamed nodes.

Taking a peek in the database at the book table, the above book looks like this:

The sql generated by the view looks like:

SELECT node.nid AS nid,
   node.title AS node_title,
   book_menu_links.depth AS book_menu_links_depth,
   book_menu_links.weight AS book_menu_links_weight,
   book_menu_links.p1 AS book_menu_links_p1,
   book_menu_links.p2 AS book_menu_links_p2,
   book_menu_links.p3 AS book_menu_links_p3,
   book_menu_links.p4 AS book_menu_links_p4,
   book_menu_links.p5 AS book_menu_links_p5,
   book_menu_links.p6 AS book_menu_links_p6,
   book_menu_links.p7 AS book_menu_links_p7,
   book_menu_links.p8 AS book_menu_links_p8,
   book_menu_links.p9 AS book_menu_links_p9
 FROM node node 
 LEFT JOIN book book ON node.nid = book.nid
 INNER JOIN node node_book ON book.bid = node_book.nid
 LEFT JOIN menu_links book_menu_links ON book.mlid = book_menu_links.mlid
 WHERE (node.status <> 0) AND (node.type in ('biblio', 'book'))
   ORDER BY book_menu_links_p1 ASC, book_menu_links_p2 ASC, book_menu_links_p3 ASC, book_menu_links_p4 ASC, book_menu_links_p5 ASC, book_menu_links_p6 ASC, book_menu_links_p7 ASC, book_menu_links_p8 ASC, book_menu_links_p9 ASC

This is what the above views query (incorrectly) returns (correct order in green): image

The book_menu_links_p* bits in the sql are the mlid's in the database. So all the "Book: Hierarchy" sort is doing its sort by mlid, but book.module applies more sort criteria than that - at each depth it's ordering the nodes by the weight of the menu item.

When the 'Book navigation' block outputs the correctly sorted list, it does so using menu_tree_all_data()

So it's clear the views "order by" query must be adjusted; I don't know what to, though.

Hmmm...a "hideous patch" from 2009 produces the sql we need here, which gives the correct ordering: http://drupal.org/node/576386#comment-2047044

Right, the above patch works! Re-rolled and attached here.

Apply it, then edit the "Book: Hierarchy" sort criteria, and check the "Sort within each hierarchy level" checkbox.

merlinofchaos, the original patch was called "ungodly-hierarchical-order.patch" - and I expect you to have the same reaction to this one - but it's the only way to produce a proper ordering of a book.

The reason why the patch is so ugly (and why it's the only thing that will work) is because the book hierarchy needs to be sorted at EACH depth.

Thank you so much for the patch!

It took me hours to realize that its a bug in the views module and not a fault by myself! Thanks so much for the patch! Hopefully it gets into the trunk quickly.

Thanks for posting that patch. It really helped. I hope it makes into Views module soon so I don't have to keep maintaining a patched version. I also thought I'd mention that I'm using Drupal 7, not sure if others are, too.

The patch works on D7 views? That's great - because the version I patched is 6.x-2.12 - kinda surprised it works on a later version too.

Thankyou very much... this works marvelously!!!

Re-rolled to apply from views module root (with the new git-ready style, no less: patch -p1 < 817748-views-book-hierarchy-sort-12.patch or git apply the same).

Tested and highly approved. An ugly query is better than broken, and as it is entirely optional and affects nothing else... highly recommend committing.

Re-rolled (for real this time) with "git diff" so that it applies with "git apply".

Same patch applies cleanly to 6.x-2.x, 6.x-3.x, and 7.x-3.x versions, but I made different patch files all the same.

Looks RTBC to me.

Tested, approved, and in use on production-- pillarsdotnet's patch, not the one i rolled and didn't attach, although they are identical ;-)

If there's question as to why this is needed i can attach before/after screenshots.

Bump. Patch still applies.

+        $menu_links = $this->query->queue_table('menu_links', NULL, $join);

I'm uncomfortable with this. queue_table() isn't meant to be used outside of the query object. I am concerned that there are situations this could fail, particularly if there are relationships involved.

This probably needs to use add_table().

Like this?

+++ b/handlers/views_handler_sort_menu_hierarchy.incundefined
@@ -3,16 +3,46 @@
+        $menu_links = $this->add_table($join, 'menu_links');

But it should be $this->query->add_table

Can you please rerole the patch and test it for both branches?

Re-rolled; not yet tested.

No, the views-order_by_weight_and_title-817448-20.patch (#20) doesn't work. I've got some error MYSQL on 6.x.2-12

It add "ASC, ASC" at the end and MYSQL doesn't like it ;-)

I've patched back the #13 and it's work on production. (Without relationship)

So #13 works in practice but not in theory, while #20 works in theory but not in practice. Would the module authors please decide which they prefer?

(grin!)

I can confirm that Views 6.x-2.12 does not sort by Book: Hierarchy, but does appear correctly in Book | "edit order and titles". For me, I added a new page, which I subsequently re-ordered thus:

Book: Hierarchy shows the added order, and not the sorted order.

Using patch from #13. Subscribing for updates to this thread.

Relatively new to Drupal. I am "fearful" of doing something to core.

Is the patch mentioned in #13 "the" way to prevent this sorting error? I have a local test version and a virtual remote version of Drupal. I have searched, but am not confident that I am reading up-to-date information on how to go about applying a patch (I've never done it). I am on Windows. Do I have to know how to use Drush?

Thank you for help / advice!
Kevin

Is the patch mentioned in #13 "the" way to prevent this sorting error?

No. Unfortunately we're still waiting for input from the views maintainers.

Do I have to know how to use Drush?

(shudder!) I certainly hope not. (grin!)

See http://drupal.org/patch/apply for the currently recommended patch guidelines.

Some Windows-specific information can be found here.

pillarsdotnet: Thank you! Sorry I made you shudder -- or, I suppose that was me doing it! (I actually tried for about an hour or so, a few weeks ago, and "shuddering" is a pretty accurate description of my experience.)

Your first link was helpful. The second link did not function.

So, at this point, are you recommending I apply the patch? Or, do you think (if it's not a critical need), that I should wait for an update to Views? ... or, instructions via this thread?

Again, thanks!

The second link did not function.

Sorry; fixed that.

So, at this point, are you recommending I apply the patch?

If you do apply the patch in #13, make sure you can un-apply it later.

Or, do you think (if it's not a critical need), that I should wait for an update to Views? ... or, instructions via this thread?

If it's not a critical need, you should always wait for the official fix. Of course, definitions of the phrase "critical need" vary wildly.

pillarsdotnet: Thank you; you've been very helpful. Kevin

Hi, I'm still experiencing this bug, and I'm waiting for an official support in core of view 2.X, because I don't want to deal with core's patch for production sites. Is there any chance to see it in next views 2.X release ?
Thx for your time !

Let's move it to 7.x-3.x and fix it there first:

It's indeed awesome that people hijack issues, then things like http://drupal.org/node/817748#comment-4596520 happens and the patch doesn't get in for a long time.

@dway
Well, first someone has to write a patch for 6.x, which isn't available yet, maybe will in the future. Then this will be part of the next release, though i really don't like to commit anything to 6.x-2.x, even bug-fix only, because you know things can always break something else so the fixes go into 6.x-3.x only. Maybe consider to update to 6.x-3.x when the patch is created for 6.x-3.x.

Triggering the testbot.

I have tested both patched on book content views against the 7.x-dev branch. The patch in #13 seems to work. I know this has been marked with 'Needs maintainer review' but have been asked to do this in views office hours.

Thanks for the review. I had a look at the patch changed it to use add_table internally and committed it to both 6.x-3.x and 7.x-3.x

Thanks for all the hard work on this issue.

#13 works great with Views 2, D6.
Please commit for this platform!

Thanks

patch #13 was working great in D7 but now fails with the latest release of Views (7.x-3.5).

The error it gives is:
patching file handlers/views_handler_sort_menu_hierarchy.inc
Hunk #1 FAILED at 3.

Just a short notice for the ones who find this thread on Google - if sort by book hierarchy isn't working for you, try with "Sort within each hierarchy level" option, for me it solved the problem with D7 and Views 3.

FYI, Patch #13 still works with Views 2.16.

cheers, Ronald

This only produces all of the links on one level. How are they sitting under the appropriate levels?