1

When you have in a column a list of items such as:

Title1
Title2
...
Title10
Title11

and you filter on Title (alphabetically), the output is like:

Title1
Title10
Title11
Title2

I want it to sort in the natural way, so that Title 10 and 11 will come on the end. I've tried to use $listOrder = natsort($listOrder); but this has no effect.

Is there a way to achieve this? Possibly in the SQL query?

  • If you are wanting do this sort in the query AND all leading characters are the same, then you can sort by the column's CHAR_LENGTH(), then the second order condition is alphabetical. If you clarify these conditions to be true of your project data, please show your query so that I can post this advice as a detailed/specific answer. If the leading portion of the string is variable, another technique will be required. Please express how your data varies. – mickmackusa Dec 22 '18 at 10:21
  • Is the numeric portion always at the end of the title value? Is there always a space before the number? Please include some more realistic sample data. – mickmackusa Dec 22 '18 at 11:06
  • Thx for answer. I've not yet tested it, but will reply first to your answer. In most cases I will follow the rule as given in the example. I've e.g. use cases as Chapter 1 - Title of chapter, Chapter 2 - Title of chapter, ..., Chapter 11 - Title of chapter, Chapter 12 - Title of chapter. Or instead of Chapter, I use e.g. Appendix. So the leading part can be variable in length, but I normally use a space before the number. And normally I use the character - between the first part and the second part of the title of a set of articles, a set of documents, ... – Franky Dec 22 '18 at 12:17
  • If you are to have any hope of consistent ordering, you must ensure that your values all conform to a single predictable format. If the number is always the last non-whitespace substring before the first - in the value, then it can be done. Fortunately Appendix comes before Chapter. Before I consider writing up an answer, please offer at least 10 different values (including the most challenging possibilities), and include your expected order in the output. An SQLFiddle demo will make your question super attractive for volunteers to answer. – mickmackusa Dec 22 '18 at 12:31
  • 1
    The sets will always be a set of Chapters, or a set of Appendix, or a set of Documents, so they are not mixed up with each other. So the first part of a set is always the same with the number added. E.g. in a module with Chapters within a Joomla Category. In an other module it could be Appendixes from a document category. – Franky Dec 22 '18 at 12:36
0

For the record, php's natsort() returns true or false so your $listOrder will always be overwritten as true (unless something errs).


If the leading portion of your selection of titles is identical and the number is the earliest character(s) to change, then you can use LOCATE() OR INSTR() on the hyphen as the primary order condition and then the full value as the secondary condition in your ORDER BY clause.

SQL Demo:

Schema:

CREATE TABLE `titles` (
  `title` varchar(255)
);

INSERT INTO `titles` VALUES
('Chapter 1 - Enter the Dragon'),
('Chapter 2 - The Dragon Is In Da House'),
('Chapter 3 - Firebreather'),
('Chapter 4 - Of Course You\'re A Girl Dragon'),
('Chapter 5 - Spread Your Wings'),
('Chapter 6 - A Visit From Smaug'),
('Chapter 7 - Draggin\' On'),
('Chapter 8 - The Egg'),
('Chapter 9 - Hatched'),
('Chapter 10 - More Fire'),
('Chapter 11 - OMG, Is The Dragon Still Here?'),
('Chapter 12 - The Dragon Has Left The Building');

Query:

SELECT LOCATE('-', title) AS position_of_hyphen, title FROM titles ORDER BY LOCATE('-', title), title

Joomla ORDER BY Syntax:

->order("LOCATE('-', title), title");
//                           ^^^^^- order ASC using full value if any ties to break
//       ^^^^^^^^^^^^^^^^^^- order ASC using the position of the earliest hyphen

Result Set:

| position_of_hyphen | title                                         |
| ------------------ | --------------------------------------------- |
| 11                 | Chapter 1 - Enter the Dragon                  |
| 11                 | Chapter 2 - The Dragon Is In Da House         |
| 11                 | Chapter 3 - Firebreather                      |
| 11                 | Chapter 4 - Of Course You're A Girl Dragon    |
| 11                 | Chapter 5 - Spread Your Wings                 |
| 11                 | Chapter 6 - A Visit From Smaug                |
| 11                 | Chapter 7 - Draggin' On                       |
| 11                 | Chapter 8 - The Egg                           |
| 11                 | Chapter 9 - Hatched                           |
| 12                 | Chapter 10 - More Fire                        |
| 12                 | Chapter 11 - OMG, Is The Dragon Still Here?   |
| 12                 | Chapter 12 - The Dragon Has Left The Building |

p.s. For anyone else that doesn't have a reliable marker like - to anchor the sort logic to... The second occurring space can be found by starting the search after the position of the first space with LOCATE(' ', title, 9). If you don't know the position of the spaces in advance, but you know you want to find the position of the second space, you could use: LOCATE(' ', title, LOCATE(' ', title) + 1)

p.p.s. You can get away with a single sort condition if you extract the whole numeric value between the first and second space and cast the substring as an integer. https://www.db-fiddle.com/f/ahk4r9YzVbrWzaCS99W8LT/2 ORDER BY CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(title, ' ', 2), ' ', -1) AS UNSIGNED) but then we are venturing into more and more function calls.

  • @Franky in my demo, if you remove the first ORDER BY clause and run the query, you will see that your issue is replicated. My solution puts things in the right order for you. If this solution is not suitable for your project, please clarify the quality/format of your actual data. – mickmackusa Dec 22 '18 at 22:03
  • ...and please improve the clarity of your question so that I can upvote it. – mickmackusa Dec 22 '18 at 22:22
  • Thx for demo. I've experimented with it, and this is clear to me. The second option - search for second occurring space - seems more reliable to me, because a user can choose his own marker or separator. I've not yet tested this in a Joomla component or module, I hope I can do this tomorrow. I will then give feedback again. – Franky Dec 23 '18 at 13:02
  • I've tested some code in Joomla, with Component Contact. Made some contacts in a category: Contact 1 - Name, Contact 2 - Name, Contact 10 - Name, Contact 11 - Name, Contact 20 - Name, Contact 30 - Name. In following file (see on: github.com/joomla/joomla-cms/blob/staging/components/…), I've modified on line 217 the query. – Franky Dec 25 '18 at 10:15
  • If I use $query->order("LOCATE('-', a.name), a.name"); the listing is correct in the natural way and in ASC. If I use the second option $query->order("LOCATE(' ', a.name, LOCATE(' ', a.name) + 1)"); the listing is NOT correct; Contact 1 - Name, Contact 2 - Name, Contact 10 - Name, Contact 20 - Name, Contact 11 - Name, Contact 30 - Name. A suggestion could be to let administrator with a variable choose his own separator, and to use this in the first query? The third option I've not yet tested. – Franky Dec 25 '18 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.