{"action":"opened","issue":{"url":"https://api.github.com/repos/octobercms/october/issues/1432","labels_url":"https://api.github.com/repos/octobercms/october/issues/1432/labels{/name}","comments_url":"https://api.github.com/repos/octobercms/october/issues/1432/comments","events_url":"https://api.github.com/repos/octobercms/october/issues/1432/events","html_url":"https://github.com/octobercms/october/issues/1432","id":106539538,"number":1432,"title":"BelongsToMany to BelongsToMany via table with deferred bindings chunk iteration fails if primary key is named id","user":{"login":"tschallacka","id":8638425,"avatar_url":"https://avatars.githubusercontent.com/u/8638425?v=3","gravatar_id":"","url":"https://api.github.com/users/tschallacka","html_url":"https://github.com/tschallacka","followers_url":"https://api.github.com/users/tschallacka/followers","following_url":"https://api.github.com/users/tschallacka/following{/other_user}","gists_url":"https://api.github.com/users/tschallacka/gists{/gist_id}","starred_url":"https://api.github.com/users/tschallacka/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tschallacka/subscriptions","organizations_url":"https://api.github.com/users/tschallacka/orgs","repos_url":"https://api.github.com/users/tschallacka/repos","events_url":"https://api.github.com/users/tschallacka/events{/privacy}","received_events_url":"https://api.github.com/users/tschallacka/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-09-15T11:50:01Z","updated_at":"2015-09-15T11:50:01Z","closed_at":null,"body":"Basically what the title says:\r\n\r\nWhen you have a belongstomany to a belongs to many\r\n\r\nhotelroom\r\n\r\n    public $belongsToMany = ['pricerules' =>\r\n    \t\t\t\t\t\t\t['ExitControl\\PriceManager\\Models\\PriceRule',\r\n    \t\t\t\t\t\t\t\t'table'=>'hotel_room_to_pricerule',\r\n    \t\t\t\t\t\t\t\t'key' => 'hotel_room_id',\r\n    \t\t\t\t\t\t\t    'otherKey' => 'pricerule_id'\r\n    \t\t\t\t\t\t\t],\r\n    ];\r\n\r\npricerule\r\n\r\n    public $belongsToMany = ['hotelrooms' =>\r\n    \t\t\t\t\t\t\t['ExitControl\\HotelManager\\Models\\HotelRoom',\r\n    \t\t\t\t\t\t\t\t'table'=>'hotel_room_to_pricerule',\r\n    \t\t\t\t\t\t\t\t'otherKey' => 'hotel_room_id',\r\n    \t\t\t\t\t\t\t    'key' => 'pricerule_id'\r\n    \t\t\t\t\t\t\t],\r\n    \t\t\r\n    ];\r\n\r\nWith deferred bindings\r\n\r\n     $this->vars['value'] = $this->getRelationObject()\r\n    \t\t\t\t\t\t\t\t->withDeferred($this->sessionKey)\r\n    \t\t\t\t\t\t\t\t->orderBy('created_at','desc');\r\n\r\nand you do chunk iteration\r\n\r\n    $value->chunk(25,function($values) use($saturation, $count,$indexrules, $rules) {\r\n          foreach($values as $val) {\r\n          .....\r\n          }\r\n     });\r\n\r\nand if the $primaryKey is named **id** in your table, you'll run into an erro that there is a duplicate entry of the field **id** in the temp table.\r\n\r\n\r\n    exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The column 'id' was specified multiple times for 'temp_table'.\r\n\r\nand this is the query it generates\r\n\r\n    SELECT * \r\n    FROM   ( \r\n                 SELECT    *, \r\n                           Row_number() OVER (ORDER BY [created_at] DESC) AS row_num \r\n                 FROM      [price_rules] \r\n                 LEFT JOIN [hotel_room_to_pricerule] \r\n                 ON        [price_rules].[id] = [hotel_room_to_pricerule].[pricerule_id] \r\n                 WHERE     ( \r\n                                     [hotel_room_to_pricerule].[hotel_room_id] = 2 \r\n                           OR        EXISTS \r\n                                     ( \r\n                                            SELECT * \r\n                                            FROM   [deferred_bindings] \r\n                                            WHERE  slave_id = price_rules.id \r\n                                            AND    [master_field] = pricerules \r\n                                            AND    [master_type] = exitcontrol\\hotelmanager\\models\\hotelroom\r\n                                            AND    [session_key] = tpyqf1z92winmzhxuoz31np2bu1mafq0nlxapvaz\r\n                                            AND    [is_bind] = 1)) \r\n                 AND       NOT EXISTS \r\n                           ( \r\n                                  SELECT * \r\n                                  FROM   [deferred_bindings] \r\n                                  WHERE  slave_id = price_rules.id \r\n                                  AND    [master_field] = pricerules \r\n                                  AND    [master_type] = exitcontrol\\hotelmanager\\models\\hotelroom\r\n                                  AND    [session_key] = tpyqf1z92winmzhxuoz31np2bu1mafq0nlxapvaz\r\n                                  AND    [is_bind] = 0 \r\n                                  AND    id > isnull( \r\n                                         ( \r\n                                                SELECT max(id) \r\n                                                from   deferred_bindings \r\n                                                WHERE  slave_id = price_rules.id \r\n                                                AND    master_field = pricerules \r\n                                                AND    master_type = exitcontrol\\hotelmanager\\models\\hotelroom\r\n                                                AND    session_key = tpyqf1z92winmzhxuoz31np2bu1mafq0nlxapvaz\r\n                                                AND    is_bind = 1 ), 0))) AS temp_table \r\n    WHERE  row_num BETWEEN 26 AND    50\r\n\r\nAs you can see it uses id too for the deferred bindings and the [price_rules].[id]\r\n\r\nI managed to solve this by rebranding the primaryKey to rule_id in the database, but i'd prefer it if somehow this duplicate id entry could be resolved ;-)\r\n\r\nOr else leave this out there or mentioned in the documentation clearly that this niche case where you use AND deferred binding AND chunk iteration can cause issues if your primary keys are named id instead of a unique name among the set of joins."}}