{"action":"closed","issue":{"url":"https://api.github.com/repos/querydsl/querydsl/issues/1800","repository_url":"https://api.github.com/repos/querydsl/querydsl","labels_url":"https://api.github.com/repos/querydsl/querydsl/issues/1800/labels{/name}","comments_url":"https://api.github.com/repos/querydsl/querydsl/issues/1800/comments","events_url":"https://api.github.com/repos/querydsl/querydsl/issues/1800/events","html_url":"https://github.com/querydsl/querydsl/issues/1800","id":134808853,"number":1800,"title":"Union in subselects","user":{"login":"aldreya","id":15629049,"avatar_url":"https://avatars.githubusercontent.com/u/15629049?v=3","gravatar_id":"","url":"https://api.github.com/users/aldreya","html_url":"https://github.com/aldreya","followers_url":"https://api.github.com/users/aldreya/followers","following_url":"https://api.github.com/users/aldreya/following{/other_user}","gists_url":"https://api.github.com/users/aldreya/gists{/gist_id}","starred_url":"https://api.github.com/users/aldreya/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aldreya/subscriptions","organizations_url":"https://api.github.com/users/aldreya/orgs","repos_url":"https://api.github.com/users/aldreya/repos","events_url":"https://api.github.com/users/aldreya/events{/privacy}","received_events_url":"https://api.github.com/users/aldreya/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":6,"created_at":"2016-02-19T08:58:44Z","updated_at":"2016-04-05T19:15:05Z","closed_at":"2016-04-05T19:15:05Z","body":"I need optimize a dynamic query. This an example, but it has more filters (BooleanExpression)\r\n\r\n```\r\nQTable qtable = QTable.table;\r\n\r\nBooleanExpression pSubQueryFilter = qtable.field4.in(List<Long>).and((date != null) ? qtable.field5.goe(date) : null);\r\n\r\nBooleanExpression pSubQuery =( ((qtable.field1.eq(string1)) .and(qtable .field2.eq(string2)).and(qtable .field3.eq(Boolean.FALSE)))\r\n.or( (qtable.field1.eq(string2)).and(qtable .field2.eq(string1).and(qtable .field3.eq(Boolean.TRUE))))\r\n).and(pSubQueryFilter);\r\n\r\nList<T> list = createQuery(pSubQuery ).list(path);\r\n```\r\n\r\n\r\nThis generates this select, but spend more than 30s in DB because OR clauses. This fields (field1 and field2) have already index in DB.\r\n\r\n```\r\nSELECT * FROM table WHERE\r\n\r\n  (field1= 'string1'  AND field2='string2'  AND field3=0)\r\n\r\n  OR (field1= 'string2'  AND field2='string1'  AND field3=1 ) \r\n\r\n  AND field4 in (1,2,3,4,5,...)  AND field5 > =SYSDATE-365\r\n\r\n  ORDER BY  field5 DESC,  id DESC\r\n```\r\n\r\nI optimized this select to 1s in SQL with this result:\r\n\r\n```\r\nSELECT * FROM table WHERE\r\n\r\n  id IN ( (SELECT id FROM table WHERE field1= 'string1'  AND field2='string2'  AND field3=0)\r\n\r\n  UNION (SELECT id FROM table WHERE field1= 'string2'  AND field2='string1'  AND field3=1 ) )\r\n\r\n  AND field4 in (1,2,3,4,5,...)  AND field5 > =SYSDATE-365\r\n\r\n  ORDER BY  field5 DESC,  id DESC\r\n```\r\n\r\nBut in QueryDsl didn't get improve. I did it this way, because Union didn't work.\r\n\r\n```\r\nBooleanExpression psubq1 = qtable .field1.eq(string1).and(qtable .field2.eq(string2)).and(qtable .field3.eq(Boolean.FALSE));\r\n\r\nBooleanExpression psubq2 = qtable .field1.eq(string2).and(qtable .field2.eq(string1)).and(qtable .field3.eq(Boolean.TRUE));\r\n\r\nExpression<?>[] args = { qtable .id};\r\n\r\nList<Long> resultids = tableRepository.findAllIds(psubq1, args);\r\n\r\nresultids.addAll(tableRepository.findAllIds(psubq2, args));\r\n\r\npSubQuery = qtable .id.in(resultids).and(pSubQueryFilter);\r\n\r\n```\r\n\r\nExists any way to do the last sql with Querydsl with one call to DB?\r\n\r\nThank you\r\n"}}