{"action":"created","issue":{"url":"https://api.github.com/repos/tgriesser/bookshelf/issues/879","labels_url":"https://api.github.com/repos/tgriesser/bookshelf/issues/879/labels{/name}","comments_url":"https://api.github.com/repos/tgriesser/bookshelf/issues/879/comments","events_url":"https://api.github.com/repos/tgriesser/bookshelf/issues/879/events","html_url":"https://github.com/tgriesser/bookshelf/issues/879","id":102250570,"number":879,"title":"Is there a better way to do this?","user":{"login":"emaddoma","id":12914515,"avatar_url":"https://avatars.githubusercontent.com/u/12914515?v=3","gravatar_id":"","url":"https://api.github.com/users/emaddoma","html_url":"https://github.com/emaddoma","followers_url":"https://api.github.com/users/emaddoma/followers","following_url":"https://api.github.com/users/emaddoma/following{/other_user}","gists_url":"https://api.github.com/users/emaddoma/gists{/gist_id}","starred_url":"https://api.github.com/users/emaddoma/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/emaddoma/subscriptions","organizations_url":"https://api.github.com/users/emaddoma/orgs","repos_url":"https://api.github.com/users/emaddoma/repos","events_url":"https://api.github.com/users/emaddoma/events{/privacy}","received_events_url":"https://api.github.com/users/emaddoma/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-08-20T22:19:40Z","updated_at":"2015-08-21T03:43:46Z","closed_at":null,"body":"I have a Postgres table with a column (area) defined as geography(Polygon,4326) and for convenience's sake I'd like to always return a representation of the column as GeoJSON, which is achieved with the PostGIS function ST_AsGeoJSON(area) but I currently have to do a select with a knex.raw on every query to the table.  I'm wondering if there's a more efficient way to achieve the same result.\r\n\r\nAn example of the select\r\n```js\r\nGeofence.query(function(q) {\r\n\tq.select('*',knex.raw('ST_AsGeoJSON(area) as \"areaAsGeoJson\"'));\r\n\tq.where('account_id', req.params.master_account_id);\r\n\tq.orderBy('name');\r\n}).fetchAll({}).then(function(geofences) {\r\n\tres.json(geofences);\r\n}).catch(function(err) {\r\n\tnext(err);\r\n});\r\n```\r\n\r\nMy model module\r\n```js\r\nvar bookshelf = require('bookshelf').db;\r\n\r\nmodule.exports = bookshelf.model('Geofences', bookshelf.Model.extend({\r\n\t// DB TABLE\r\n\ttableName: 'geofences',\r\n\r\n\t// PROPERTIES THAT SHOULD NOT BE EXPOSED\r\n\thidden: ['area'],\r\n\r\n\t// INIT\r\n\tinitialize: function(){\r\n\t\t// CONVERT  to object\r\n\t\tif(this.attributes.areaAsGeoJson){\r\n\t\t\tthis.attributes.geojson = JSON.parse('{\"type\": \"Feature\",\"geometry\":'+this.attributes.areaAsGeoJson+'}');\r\n\t\t\tthis.attributes.areaAsGeoJson = undefined;\r\n\t\t}\r\n\t},\r\n\r\n\t// RELATIONSHIPS\r\n\taccount: function() {\r\n\t\treturn this.belongsTo('Account', 'account_id');\r\n\t}\r\n}));\r\n```"},"comment":{"url":"https://api.github.com/repos/tgriesser/bookshelf/issues/comments/133270726","html_url":"https://github.com/tgriesser/bookshelf/issues/879#issuecomment-133270726","issue_url":"https://api.github.com/repos/tgriesser/bookshelf/issues/879","id":133270726,"user":{"login":"rhys-vdw","id":822105,"avatar_url":"https://avatars.githubusercontent.com/u/822105?v=3","gravatar_id":"","url":"https://api.github.com/users/rhys-vdw","html_url":"https://github.com/rhys-vdw","followers_url":"https://api.github.com/users/rhys-vdw/followers","following_url":"https://api.github.com/users/rhys-vdw/following{/other_user}","gists_url":"https://api.github.com/users/rhys-vdw/gists{/gist_id}","starred_url":"https://api.github.com/users/rhys-vdw/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rhys-vdw/subscriptions","organizations_url":"https://api.github.com/users/rhys-vdw/orgs","repos_url":"https://api.github.com/users/rhys-vdw/repos","events_url":"https://api.github.com/users/rhys-vdw/events{/privacy}","received_events_url":"https://api.github.com/users/rhys-vdw/received_events","type":"User","site_admin":false},"created_at":"2015-08-21T03:43:46Z","updated_at":"2015-08-21T03:43:46Z","body":"Have you looked at #873? And acquainted yourself with [knex-postgis](https://github.com/jfgodoy/knex-postgis)? I don't really know what PostGIS is, but I get the idea\r\n\r\nI'd look at doing something like this:\r\n\r\n```js\r\nclass GeoJson {\r\n  constructor(attrs) {\r\n    this.attributes =  attrs instanceof GeoJson\r\n      ? _.clone(attrs.attributes)\r\n      : _.clone(attrs);\r\n  }\r\n  toJSON() {\r\n    // Called when serializing your instance.\r\n    return this.attributes;\r\n  }\r\n  toQuery() {\r\n    return knex.raw('<whatever you need here>', this.attributes);\r\n  }\r\n}\r\n\r\n// Override your parse/format like so.\r\nbookshelf.Model = bookshelf.Model.extend({\r\n  parse() {\r\n    return super.parse(_.mapValues((value, column) => {\r\n      Type = this.types && this.types[column];\r\n       return Type ? new Type(value) : value;\r\n    }));\r\n  }\r\n\r\n  format() {\r\n    return super.format(_.mapValues((value, attribute) =>\r\n      value.toQuery ? value.toQuery() : value;\r\n    ));\r\n  }\r\n});\r\n\r\nbookshelf.model('Geofences', bookshelf.Model.extend({\r\n  tableName: 'geofences',\r\n  types: { geojon: GeoJson }\r\n});\r\n```\r\n\r\nNow you can define types with `toQuery` that will automatically generate their desired output before being inserted into the database. `toJson` will be called before serializing, so you can present the data for the user.\r\n\r\nIf you still want to do something like this:\r\n\r\n```js\r\n    // INIT\r\n    initialize: function(){\r\n        // CONVERT  to object\r\n        if(this.attributes.areaAsGeoJson){\r\n            this.attributes.geojson = JSON.parse('{\"type\": \"Feature\",\"geometry\":'+this.attributes.areaAsGeoJson+'}');\r\n            this.attributes.areaAsGeoJson = undefined;\r\n        }\r\n    },\r\n```\r\n\r\nLook at the [virtuals plugin](http://bookshelfjs.org/#Plugins-Virtuals). It's designed for this kind of thing.\r\n\r\n```js\r\n  virtuals: {\r\n    areaAsGeoJson: function(value) {\r\n      this.set('geojson', {type: 'Feature', geometry: JSON.parse(value)});\r\n    }\r\n  }\r\n```\r\n\r\nMight take some playing around, but this is the basic approach I'd take."}}