{"action":"created","issue":{"url":"https://api.github.com/repos/jOOQ/jOOQ/issues/5003","labels_url":"https://api.github.com/repos/jOOQ/jOOQ/issues/5003/labels{/name}","comments_url":"https://api.github.com/repos/jOOQ/jOOQ/issues/5003/comments","events_url":"https://api.github.com/repos/jOOQ/jOOQ/issues/5003/events","html_url":"https://github.com/jOOQ/jOOQ/issues/5003","id":128825564,"number":5003,"title":"Add plain SQL versions of select(String), groupBy(String), orderBy(String)","user":{"login":"nkoterba","id":2508944,"avatar_url":"https://avatars.githubusercontent.com/u/2508944?v=3","gravatar_id":"","url":"https://api.github.com/users/nkoterba","html_url":"https://github.com/nkoterba","followers_url":"https://api.github.com/users/nkoterba/followers","following_url":"https://api.github.com/users/nkoterba/following{/other_user}","gists_url":"https://api.github.com/users/nkoterba/gists{/gist_id}","starred_url":"https://api.github.com/users/nkoterba/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/nkoterba/subscriptions","organizations_url":"https://api.github.com/users/nkoterba/orgs","repos_url":"https://api.github.com/users/nkoterba/repos","events_url":"https://api.github.com/users/nkoterba/events{/privacy}","received_events_url":"https://api.github.com/users/nkoterba/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/jOOQ/jOOQ/labels/C:%20Functionality","name":"C: Functionality","color":"0000aa"},{"url":"https://api.github.com/repos/jOOQ/jOOQ/labels/P:%20Medium","name":"P: Medium","color":"ffaaaa"},{"url":"https://api.github.com/repos/jOOQ/jOOQ/labels/T:%20Enhancement","name":"T: Enhancement","color":"cccccc"}],"state":"open","locked":false,"assignee":null,"milestone":{"url":"https://api.github.com/repos/jOOQ/jOOQ/milestones/119","html_url":"https://github.com/jOOQ/jOOQ/milestones/Version%203.8.0","labels_url":"https://api.github.com/repos/jOOQ/jOOQ/milestones/119/labels","id":1094772,"number":119,"title":"Version 3.8.0","description":"","creator":{"login":"lukaseder","id":734593,"avatar_url":"https://avatars.githubusercontent.com/u/734593?v=3","gravatar_id":"","url":"https://api.github.com/users/lukaseder","html_url":"https://github.com/lukaseder","followers_url":"https://api.github.com/users/lukaseder/followers","following_url":"https://api.github.com/users/lukaseder/following{/other_user}","gists_url":"https://api.github.com/users/lukaseder/gists{/gist_id}","starred_url":"https://api.github.com/users/lukaseder/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lukaseder/subscriptions","organizations_url":"https://api.github.com/users/lukaseder/orgs","repos_url":"https://api.github.com/users/lukaseder/repos","events_url":"https://api.github.com/users/lukaseder/events{/privacy}","received_events_url":"https://api.github.com/users/lukaseder/received_events","type":"User","site_admin":false},"open_issues":369,"closed_issues":179,"state":"open","created_at":"2015-05-04T10:29:41Z","updated_at":"2016-01-27T16:56:50Z","due_on":null,"closed_at":null},"comments":8,"created_at":"2016-01-26T13:50:21Z","updated_at":"2016-01-27T16:58:46Z","closed_at":null,"body":"So I noticed that one of the options of `DSL.condition()` allows creating a custom where clause using either a `SQL` object or a `String` object.  I'm assuming this is to allow custom, complex `WHERE` clauses that may be difficult to construct with the DSL or not known ahead of time.  Internally, I'm assuming that when the final SQL string or command is generated, it just inserts this `WHERE` clause into the correct place in the query string.\r\n\r\nDoes Jooq support custom clauses, again either as a `SQL` or `String` objects for the other parts of a query?  Namely, the select, orderBy, and groupBy clauses?  I don't see similar functionality for these parts of a query.\r\n\r\nI recognize that `WHERE` clauses can be easily combined and chained so allowing a `condition` to be created with custom sql fits nicely into the builder pipeline.  The `from` method in Jooq DSL also supports a `SQL` or `String` object.\r\n\r\nSince the `groupBy()`, `orderBy()`, and `select()` methods all force developers to \"move on\" to the next step, I would think that allowing custom SQL here would also be possible.\r\n\r\nOf course, allowing users to define custom SQL means they could write incorrect syntax and/or mismatched columns (e.g. adding a groupBy clause that contains columns not in the select clause).  But such issues would hopefully appear quickly as the SQL processor in the datastore would throw errors.\r\n\r\nIn short, I think it would be useful (at least in our use cases) to be able to do:\r\n```java\r\ndslContext\r\n  .select(\"id, count(*)\")\r\n  .from(TABLE)\r\n  .where(DSL.condition(<custom sql>))\r\n  .groupBy(DSL.group(<custom sql>))\r\n  .orderBy(TABLE.ID)\r\n\r\n// OR\r\n\r\n  dslContext\r\n  .select(TABLE.NAME, TABLE.NAME.count())\r\n  .from(\"table\")\r\n  .where(TABLE.ID.ge(20))\r\n  .groupBy(TABLE.NAME)\r\n  .orderBy(DSL.order(<custom sql>))\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/jOOQ/jOOQ/issues/comments/175743469","html_url":"https://github.com/jOOQ/jOOQ/issues/5003#issuecomment-175743469","issue_url":"https://api.github.com/repos/jOOQ/jOOQ/issues/5003","id":175743469,"user":{"login":"lukaseder","id":734593,"avatar_url":"https://avatars.githubusercontent.com/u/734593?v=3","gravatar_id":"","url":"https://api.github.com/users/lukaseder","html_url":"https://github.com/lukaseder","followers_url":"https://api.github.com/users/lukaseder/followers","following_url":"https://api.github.com/users/lukaseder/following{/other_user}","gists_url":"https://api.github.com/users/lukaseder/gists{/gist_id}","starred_url":"https://api.github.com/users/lukaseder/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lukaseder/subscriptions","organizations_url":"https://api.github.com/users/lukaseder/orgs","repos_url":"https://api.github.com/users/lukaseder/repos","events_url":"https://api.github.com/users/lukaseder/events{/privacy}","received_events_url":"https://api.github.com/users/lukaseder/received_events","type":"User","site_admin":false},"created_at":"2016-01-27T16:58:46Z","updated_at":"2016-01-27T16:58:46Z","body":"Hmm, thanks for following up. That's indeed a bit confusing (and wrong). I've registered an issue to fix the Javadoc: https://github.com/jOOQ/jOOQ/issues/5004.\r\n\r\nThis restriction was a nice idea, but we never implemented it this way, as a plain SQL table declaration cannot be taken out of its enclosing context to find out what fields are available.\r\n\r\nBesides, as you may have noticed, `from(String)` simply delegates to `DSL.table(String)`, so the restriction isn't enforced, there."}}