{"action":"created","issue":{"url":"https://api.github.com/repos/slick/slick/issues/1161","labels_url":"https://api.github.com/repos/slick/slick/issues/1161/labels{/name}","comments_url":"https://api.github.com/repos/slick/slick/issues/1161/comments","events_url":"https://api.github.com/repos/slick/slick/issues/1161/events","html_url":"https://github.com/slick/slick/issues/1161","id":87976037,"number":1161,"title":"[Feature] Composable SQL Strings","user":{"login":"schmitch","id":1641401,"avatar_url":"https://avatars.githubusercontent.com/u/1641401?v=3","gravatar_id":"","url":"https://api.github.com/users/schmitch","html_url":"https://github.com/schmitch","followers_url":"https://api.github.com/users/schmitch/followers","following_url":"https://api.github.com/users/schmitch/following{/other_user}","gists_url":"https://api.github.com/users/schmitch/gists{/gist_id}","starred_url":"https://api.github.com/users/schmitch/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/schmitch/subscriptions","organizations_url":"https://api.github.com/users/schmitch/orgs","repos_url":"https://api.github.com/users/schmitch/repos","events_url":"https://api.github.com/users/schmitch/events{/privacy}","received_events_url":"https://api.github.com/users/schmitch/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/slick/slick/labels/1%20-%20Ready","name":"1 - Ready","color":"c7def8"},{"url":"https://api.github.com/repos/slick/slick/labels/improvement","name":"improvement","color":"009800"},{"url":"https://api.github.com/repos/slick/slick/labels/topic:plainsql","name":"topic:plainsql","color":"fef2c0"}],"state":"open","locked":false,"assignee":null,"milestone":{"url":"https://api.github.com/repos/slick/slick/milestones/20","html_url":"https://github.com/slick/slick/milestones/3.2.0","labels_url":"https://api.github.com/repos/slick/slick/milestones/20/labels","id":1269007,"number":20,"title":"3.2.0","description":"","creator":{"login":"szeiger","id":54262,"avatar_url":"https://avatars.githubusercontent.com/u/54262?v=3","gravatar_id":"","url":"https://api.github.com/users/szeiger","html_url":"https://github.com/szeiger","followers_url":"https://api.github.com/users/szeiger/followers","following_url":"https://api.github.com/users/szeiger/following{/other_user}","gists_url":"https://api.github.com/users/szeiger/gists{/gist_id}","starred_url":"https://api.github.com/users/szeiger/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/szeiger/subscriptions","organizations_url":"https://api.github.com/users/szeiger/orgs","repos_url":"https://api.github.com/users/szeiger/repos","events_url":"https://api.github.com/users/szeiger/events{/privacy}","received_events_url":"https://api.github.com/users/szeiger/received_events","type":"User","site_admin":false},"open_issues":34,"closed_issues":1,"state":"open","created_at":"2015-08-24T09:02:58Z","updated_at":"2015-09-23T08:51:32Z","due_on":null,"closed_at":null},"comments":14,"created_at":"2015-06-13T10:04:22Z","updated_at":"2015-09-23T08:55:06Z","closed_at":null,"body":"Due to the nature of some SQL queries it will be a great addition to slick, to have composable SQL Strings, which means that you could now generate some where parts dynamically without loosing some security feature's of SQL interpolated strings.\r\n\r\nWith this feature it would now be possible to write a query like that without loosing sql injection prevention or other security features of slick:\r\n\r\nExample:\r\n```\r\ndef (username: Option[String], id: Option[Long]) = {\r\n    val q0 = List()\r\n    val q1 = if (username.isDefined) sql\"username = $username\" :: q0 else q0\r\n    val q2 = if (id.isDefined) sql\"id > $id\" :: q1 else q1\r\n   val where = sql\"WHERE ${q2.mkstring(\" AND \")}\"\r\n   db.run(sql\"SELECT * FROM table #$where;\".as[TableRow])\r\n}\r\n```\r\n\r\nAnother example would be an update or an insert which will add some rows on demand:\r\n```\r\ndef (username: Option[String], id: Option[Long]) = {\r\nval userQuery = if(username.isDefined) sql\"username = $username\" else sql\"\"\r\nval idQuery = if(id.isDefined) sql\"id = $id\" else sql\"\"\r\ndb.run(sql\"UPDATE table SET first_name = 'Mustermann', #$userQuery, #$idQuery\")\r\n}\r\n```\r\n\r\nThis API would require significant input so I think for keeping backwards compatibility there would be need for some keywords, other than ```sql\"\"```. Also it should work to have a better api than just concat a List of queries with mkstring, so there would be the need for doing a ```.and``` or ```.or``` or when we look at the update part there is a comma in between.\r\n\r\nWould be great to here some input on this. And espacially to have some input how to solve the most common problems with this feature, like dynamic SET parts of a update query or mixing dynamic with static parts and dynamic where filter."},"comment":{"url":"https://api.github.com/repos/slick/slick/issues/comments/142533396","html_url":"https://github.com/slick/slick/issues/1161#issuecomment-142533396","issue_url":"https://api.github.com/repos/slick/slick/issues/1161","id":142533396,"user":{"login":"schmitch","id":1641401,"avatar_url":"https://avatars.githubusercontent.com/u/1641401?v=3","gravatar_id":"","url":"https://api.github.com/users/schmitch","html_url":"https://github.com/schmitch","followers_url":"https://api.github.com/users/schmitch/followers","following_url":"https://api.github.com/users/schmitch/following{/other_user}","gists_url":"https://api.github.com/users/schmitch/gists{/gist_id}","starred_url":"https://api.github.com/users/schmitch/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/schmitch/subscriptions","organizations_url":"https://api.github.com/users/schmitch/orgs","repos_url":"https://api.github.com/users/schmitch/repos","events_url":"https://api.github.com/users/schmitch/events{/privacy}","received_events_url":"https://api.github.com/users/schmitch/received_events","type":"User","site_admin":false},"created_at":"2015-09-23T08:55:06Z","updated_at":"2015-09-23T08:55:06Z","body":"yes after reading these comments I think that there is really no need for new operators.\r\nAs said that was just a draft"}}