{"action":"created","issue":{"url":"https://api.github.com/repos/tgriesser/bookshelf/issues/1350","repository_url":"https://api.github.com/repos/tgriesser/bookshelf","labels_url":"https://api.github.com/repos/tgriesser/bookshelf/issues/1350/labels{/name}","comments_url":"https://api.github.com/repos/tgriesser/bookshelf/issues/1350/comments","events_url":"https://api.github.com/repos/tgriesser/bookshelf/issues/1350/events","html_url":"https://github.com/tgriesser/bookshelf/issues/1350","id":172494948,"number":1350,"title":"Knex and Bookshelf give two different results","user":{"login":"miketerpak","id":6426486,"avatar_url":"https://avatars.githubusercontent.com/u/6426486?v=3","gravatar_id":"","url":"https://api.github.com/users/miketerpak","html_url":"https://github.com/miketerpak","followers_url":"https://api.github.com/users/miketerpak/followers","following_url":"https://api.github.com/users/miketerpak/following{/other_user}","gists_url":"https://api.github.com/users/miketerpak/gists{/gist_id}","starred_url":"https://api.github.com/users/miketerpak/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/miketerpak/subscriptions","organizations_url":"https://api.github.com/users/miketerpak/orgs","repos_url":"https://api.github.com/users/miketerpak/repos","events_url":"https://api.github.com/users/miketerpak/events{/privacy}","received_events_url":"https://api.github.com/users/miketerpak/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2016-08-22T16:21:33Z","updated_at":"2016-08-22T17:32:52Z","closed_at":"2016-08-22T17:32:52Z","body":"I have a User mode and a UserSettings model.  The settings model references a table with a foreign key `user_id`.  The table is initialized with the defaults for each setting (default settings have user_id = NULL) so that when a user builds their settings object, any setting that isn't set by the user can reference the default row instead.  My query for that is as follows:\r\n\r\n`select distinct ON (type) \"type\", \"description\", \"options\", \"user_id\" from \"user_settings\" where (\"user_settings\".\"user_id\" = '1' or \"user_id\" is null) and \"type\" in ('type1', 'type2') order by \"type\" asc, \"user_id\" ASC NULLS LAST;`\r\n\r\nRunning this query in the pg console, as well as building it through knex, results in the correct response of 1 row for each type provided, returning null user_id rows where needed.  However, running the same exact query through a bookshelf.Model#query using fetchAll results in two of the exact same models of type `type1`.  My code where I test both queryes is as follows:\r\n\r\n    fetchSettings: function({ filter, type, types, types_like } = {}) {\r\n    (function() {\r\n        let qb = knex('user_settings');\r\n        qb.distinct(knex.raw('ON (type) \"type\", \"description\", \"options\", \"user_id\"'));\r\n        qb.where(_qb => {\r\n            _qb.where('user_settings.user_id', '385');\r\n            _qb.orWhereNull('user_id');\r\n        });\r\n        qb.whereIn('type', ['notify-user-payment-payment-declined', 'notify-user-news-story']);\r\n\r\n        qb.orderBy('type');\r\n        qb.orderBy('user_id', knex.raw('ASC NULLS LAST'));\r\n    console.log('Knex Query:')\r\n    console.log(qb.toString())\r\n    console.log('');\r\n        qb.then(r => {\r\n            console.log('Knex Results:')\r\n            console.log(r);\r\n            console.log('');\r\n        })\r\n    })();\r\n        return UserSettings\r\n            .query(qb => {\r\n                qb.distinct(knex.raw('ON (type) \"type\", \"description\", \"options\", \"user_id\"'));\r\n                qb.where(_qb => {\r\n                    _qb.where('user_settings.user_id', this.get('id'));\r\n                    _qb.orWhereNull('user_id');\r\n                });\r\n                \r\n                if (type) {\r\n                    qb.where('type', type);\r\n                } else if (types) {\r\n                    qb.whereIn('type', types);\r\n                } else if (types_like) {\r\n                    qb.whereRaw('\"type\" LIKE ?', [types_like]);\r\n                }\r\n\r\n                qb.orderBy('type');\r\n                qb.orderBy('user_id', knex.raw('ASC NULLS LAST'));\r\n    console.log('Bookshelf query:')\r\n            })\r\n            .fetchAll({debug:true})\r\n            .then(settings_coll => {\r\n    console.log('Bookshelf Results: ')\r\n    console.log(settings_coll.models)\r\n                this.relations.settings = settings_coll;\r\n                return settings_coll;\r\n            });\r\n    },`\r\n\r\nThe resulting console logs are as follows:\r\n\r\n    Knex Query:\r\n    select distinct ON (type) \"type\", \"description\", \"options\", \"user_id\" from \"user_settings\" where (\"user_settings\".\"user_id\" = '385' or \"user_id\" is null) and \"type\" in ('notify-user-payment-payment-declined', 'notify-user-news-story') order by \"type\" asc, \"user_id\" ASC NULLS LAST\r\n\r\n    Knex Results:\r\n    [ anonymous {\r\n        type: 'notify-user-news-story',\r\n        description: null,\r\n        options: {},\r\n        user_id: '385' },\r\n      anonymous {\r\n        type: 'notify-user-payment-payment-declined',\r\n        description: null,\r\n        options:\r\n         { send_sms: false,\r\n           send_push: false,\r\n           send_email: false,\r\n           send_fresco: false },\r\n        user_id: '385' } ]\r\n\r\n\r\n    Bookshelf query:\r\n    { method: 'select',\r\n      options: {},\r\n      timeout: false,\r\n      cancelOnTimeout: false,\r\n      bindings:\r\n       [ '385',\r\n         'notify-user-payment-payment-declined',\r\n         'notify-user-news-story' ],\r\n      __knexQueryUid: '9119e60d-0b00-4130-ab73-7b7dcceea197',\r\n      sql: 'select distinct ON (type) \"type\", \"description\", \"options\", \"user_id\" from \"user_settings\" where (\"user_settings\".\"user_id\" = ? or \"user_id\" is null) and \"type\" in (?, ?) order by \"type\" asc, \"user_id\" ASC NULLS LAST' }\r\n\r\n    Bookshelf Results:\r\n    [ ModelBase {\r\n        attributes:\r\n         { type: 'notify-user-payment-payment-declined',\r\n           description: null,\r\n           options: [Object],\r\n           user_id: '385' },\r\n        _previousAttributes:\r\n         { type: 'notify-user-payment-payment-declined',\r\n           description: null,\r\n           options: [Object],\r\n           user_id: '385' },\r\n        changed: {},\r\n        relations: {},\r\n        cid: 'c14',\r\n        id: '385',\r\n        _events: { saving: [Function], saved: [Function] },\r\n        _eventsCount: 2 },\r\n      ModelBase {\r\n        attributes:\r\n         { type: 'notify-user-payment-payment-declined',\r\n           description: null,\r\n           options: [Object],\r\n           user_id: '385' },\r\n        _previousAttributes:\r\n         { type: 'notify-user-payment-payment-declined',\r\n           description: null,\r\n           options: [Object],\r\n           user_id: '385' },\r\n        changed: {},\r\n        relations: {},\r\n        cid: 'c14',\r\n        id: '385',\r\n        _events: { saving: [Function], saved: [Function] },\r\n        _eventsCount: 2 } ]"},"comment":{"url":"https://api.github.com/repos/tgriesser/bookshelf/issues/comments/241488356","html_url":"https://github.com/tgriesser/bookshelf/issues/1350#issuecomment-241488356","issue_url":"https://api.github.com/repos/tgriesser/bookshelf/issues/1350","id":241488356,"user":{"login":"vellotis","id":308074,"avatar_url":"https://avatars.githubusercontent.com/u/308074?v=3","gravatar_id":"","url":"https://api.github.com/users/vellotis","html_url":"https://github.com/vellotis","followers_url":"https://api.github.com/users/vellotis/followers","following_url":"https://api.github.com/users/vellotis/following{/other_user}","gists_url":"https://api.github.com/users/vellotis/gists{/gist_id}","starred_url":"https://api.github.com/users/vellotis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vellotis/subscriptions","organizations_url":"https://api.github.com/users/vellotis/orgs","repos_url":"https://api.github.com/users/vellotis/repos","events_url":"https://api.github.com/users/vellotis/events{/privacy}","received_events_url":"https://api.github.com/users/vellotis/received_events","type":"User","site_admin":false},"created_at":"2016-08-22T17:32:52Z","updated_at":"2016-08-22T17:32:52Z","body":"```sql\r\nselect distinct ON (type) \"type\",\r\n                          \"description\",\r\n                          \"options\",\r\n                          \"user_id\"\r\nfrom   \"user_settings\"\r\nwhere  ( \"user_settings\".\"user_id\" = '385'\r\n          or \"user_id\" is null )\r\n       and \"type\" in ( 'notify-user-payment-payment-declined',\r\n                       'notify-user-news-story' )\r\norder  by \"type\" asc,\r\n          \"user_id\" ASC NULLS LAST\r\n```\r\n```sql\r\nselect distinct ON (type) \"type\",\r\n                          \"description\",\r\n                          \"options\",\r\n                          \"user_id\"\r\nfrom   \"user_settings\"\r\nwhere  ( \"user_settings\".\"user_id\" = ? -- binding: '385'\r\n          or \"user_id\" is null )\r\n       and \"type\" in ( ?, -- binding: ''notify-user-payment-payment-declined''\r\n                       ? ) -- binding: 'notify-user-news-story'\r\norder by \"type\" asc,\r\n         \"user_id\" ASC NULLS LAST\r\n```\r\nI don't see any other differences than bindings are used."}}
{"actor":{"display_login":"vellotis"}}