Feature Request - Conditional ORDER BY #2071

Open
radoslavpetranov opened this Issue Dec 26, 2016 · 2 comments

Projects

None yet

2 participants

@radoslavpetranov

When sorting by 2 columns sometimes you may need to add a condition so that if column1 == X you sort by column2 asc and if column1 == Y you sort by column2 desc.

In SQL we achieve this using something like

SELECT * FROM deadlines
ORDER BY  `status` DESC,
CASE 
	WHEN `status` = 'OPEN' THEN `deadline` end asc,
CASE
	WHEN `status` = 'DONE' THEN `deadline` end desc

Correct me if I'm wrong but QueryDSL doesn't seem to support a conditional ORDER BY scenario like this. It would be nice if we could do something like

baseOrderSpecifier.add(deadline.status.eq(DeadlineStatus.OPEN).then(deadline.status.desc()));
baseOrderSpecifier.add(deadline.status.eq(DeadlineStatus.DONE).then(deadline.status.asc()));
@Kernle32DLL

I have never seen that in any valid sql query. Although case statements in order by clauses are valid, I don't think it is possible to change the actual ordering direction like this.

It might be worth looking into CaseBuilders:
http://www.querydsl.com/static/querydsl/4.1.4/apidocs/com/querydsl/core/types/dsl/CaseBuilder.html

@radoslavpetranov
radoslavpetranov commented Jan 3, 2017 edited

Try creating this table with random data

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `condition1` tinyint(1) NOT NULL DEFAULT '0',
  `condition2` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `test`(`id`,`condition1`,`condition2`) values 
(1,1,234), (2,0,823), (3,1,23238), (4,0,40124), (5,0,3), (6,0,402), (7,1,11), (8,0,983), (9,1,37), (10,1,23892), (11,0,73), (12,1,3832), (13,0,1182), (14,0,33), (15,1,8235), (16,1,7172), (17,1,112), (18,1,83783), (19,0,190);

Now run the following query against it

SELECT * FROM test
ORDER BY  `condition1` DESC,
CASE 
	WHEN `condition1` = TRUE THEN `condition2` END ASC,
CASE
	WHEN `condition1` = FALSE THEN `condition2` END DESC

Notice the results. Now change the last DESC to ASC and re-run the query

SELECT * FROM test
ORDER BY  `condition1` DESC,
CASE 
	WHEN `condition1` = TRUE THEN `condition2` END ASC,
CASE
	WHEN `condition1` = FALSE THEN `condition2` END ASC

Notice how the results where condition1 = FALSE just flipped their order. I'm running this against a regular 5.6.12 MySQL database but I always thought that's a standard feature.

My use case is with todos and deadlines. When getting all todos we want to first show all todos that are not done yet. These todos we want to order by deadline asc since we want the oldest ones to be on top as they are most pressing. Then the todos where status = completed we want to order the other way around - starting from the most recent one and ending with the oldest one.

Obviously that's not a show stopper feature but I figured other people may find conditional ordering useful as well. Thanks for looking into this and thanks for the CaseBuilder lead!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment