{"action":"created","comment":{"url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls/comments/152596274","pull_request_review_id":78481948,"id":152596274,"diff_hunk":"@@ -134,6 +134,29 @@ def get_by_toptier_subtier(toptier_cgac_code, subtier_code):\n             subtier_agency__subtier_code=subtier_code\n         ).order_by('-update_date').first()\n \n+    @staticmethod\n+    def get_by_subtier_only(subtier_code):\n+        \"\"\"\n+        Lookup an Agency record by subtier code only\n+\n+        Useful when data source has an inaccurate top tier code,\n+        but an accurate subtier code.  Will return an Agency\n+        if and only if a single match for the subtier code exists.\n+\n+        Args:\n+            subtier_code: an agency subtier code\n+\n+        Returns:\n+            an Agency instance\n+\n+        \"\"\"\n+        agencies = Agency.objects.filter(\n+            subtier_agency__subtier_code=subtier_code)\n+        if agencies.count() == 1:","path":"usaspending_api/references/models.py","position":22,"original_position":22,"commit_id":"0fe80c9bb8fcfcba35cddd63413fb87ba5050ce6","original_commit_id":"3eb392927b88232d9e3a35fb18f96ee67b2dc75a","user":{"login":"catherinedevlin","id":208054,"avatar_url":"https://avatars0.githubusercontent.com/u/208054?v=4","gravatar_id":"","url":"https://api.github.com/users/catherinedevlin","html_url":"https://github.com/catherinedevlin","followers_url":"https://api.github.com/users/catherinedevlin/followers","following_url":"https://api.github.com/users/catherinedevlin/following{/other_user}","gists_url":"https://api.github.com/users/catherinedevlin/gists{/gist_id}","starred_url":"https://api.github.com/users/catherinedevlin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/catherinedevlin/subscriptions","organizations_url":"https://api.github.com/users/catherinedevlin/orgs","repos_url":"https://api.github.com/users/catherinedevlin/repos","events_url":"https://api.github.com/users/catherinedevlin/events{/privacy}","received_events_url":"https://api.github.com/users/catherinedevlin/received_events","type":"User","site_admin":false},"body":"In this case, the purpose is not to find out whether there are any agencies, but to verify that there is not more than 1 record, so `.exists()` wouldn't tell us that.\r\n\r\nThe Agency table only has 1472 records, so this isn't one of the monster tables where counting is disastrous (especially counting on a filter like this.).  In fact, subtier_code is *almost* uniquely identifying, but there is one jerk subtier code that messes things up and does not uniquely specify an Agency, making this count necessary:\r\n\r\n```\r\ndata_store_api=> select s.subtier_code, count(*)\r\ndata_store_api-> from   subtier_agency s \r\ndata_store_api-> join   agency a using (subtier_agency_id)\r\ndata_store_api-> group by subtier_code\r\ndata_store_api-> having count(*) > 1;\r\n subtier_code | count \r\n--------------+-------\r\n 1152         |     2\r\n(1 row)\r\n\r\n```","created_at":"2017-11-22T15:25:28Z","updated_at":"2017-11-22T15:25:28Z","html_url":"https://github.com/fedspendingtransparency/usaspending-api/pull/707#discussion_r152596274","pull_request_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls/707","author_association":"CONTRIBUTOR","_links":{"self":{"href":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls/comments/152596274"},"html":{"href":"https://github.com/fedspendingtransparency/usaspending-api/pull/707#discussion_r152596274"},"pull_request":{"href":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls/707"}},"in_reply_to_id":152302298},"pull_request":{"url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls/707","id":153174326,"html_url":"https://github.com/fedspendingtransparency/usaspending-api/pull/707","diff_url":"https://github.com/fedspendingtransparency/usaspending-api/pull/707.diff","patch_url":"https://github.com/fedspendingtransparency/usaspending-api/pull/707.patch","issue_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/issues/707","number":707,"state":"open","locked":false,"title":"Surmise agency from subagency when needed","user":{"login":"catherinedevlin","id":208054,"avatar_url":"https://avatars0.githubusercontent.com/u/208054?v=4","gravatar_id":"","url":"https://api.github.com/users/catherinedevlin","html_url":"https://github.com/catherinedevlin","followers_url":"https://api.github.com/users/catherinedevlin/followers","following_url":"https://api.github.com/users/catherinedevlin/following{/other_user}","gists_url":"https://api.github.com/users/catherinedevlin/gists{/gist_id}","starred_url":"https://api.github.com/users/catherinedevlin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/catherinedevlin/subscriptions","organizations_url":"https://api.github.com/users/catherinedevlin/orgs","repos_url":"https://api.github.com/users/catherinedevlin/repos","events_url":"https://api.github.com/users/catherinedevlin/events{/privacy}","received_events_url":"https://api.github.com/users/catherinedevlin/received_events","type":"User","site_admin":false},"body":"bug: https://federal-spending-transparency.atlassian.net/browse/DS-1935 \r\n\r\nPresently, when loading if the loader has no Agency code for the CGAC supplied in rows from the broker, our loaders quietly give up and create the Award with a NULL awarding agency.\r\n\r\nThere are rows in the broker with a legitmate awarding_sub_tier_agency_c, but a bogus awarding_agency_code (CGAC) of 999.  Examples:\r\nSee https://federal-spending-transparency.atlassian.net/browse/DS-1935\r\n\r\nThis script gives the loader a final chance to assign those awards to an agency by using the subtier code.\r\n\r\nTODO:\r\n\r\n- [ ] corresponding changes to fabs_nightly_loader, fpds_nightly_loader\r\n- [ ] script to fix existing records","created_at":"2017-11-16T23:05:14Z","updated_at":"2017-11-22T15:25:28Z","closed_at":null,"merged_at":null,"merge_commit_sha":"48be9ea85893e58a92afab6e72964f91b11af369","assignee":{"login":"shinson","id":5183500,"avatar_url":"https://avatars3.githubusercontent.com/u/5183500?v=4","gravatar_id":"","url":"https://api.github.com/users/shinson","html_url":"https://github.com/shinson","followers_url":"https://api.github.com/users/shinson/followers","following_url":"https://api.github.com/users/shinson/following{/other_user}","gists_url":"https://api.github.com/users/shinson/gists{/gist_id}","starred_url":"https://api.github.com/users/shinson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/shinson/subscriptions","organizations_url":"https://api.github.com/users/shinson/orgs","repos_url":"https://api.github.com/users/shinson/repos","events_url":"https://api.github.com/users/shinson/events{/privacy}","received_events_url":"https://api.github.com/users/shinson/received_events","type":"User","site_admin":false},"assignees":[{"login":"shinson","id":5183500,"avatar_url":"https://avatars3.githubusercontent.com/u/5183500?v=4","gravatar_id":"","url":"https://api.github.com/users/shinson","html_url":"https://github.com/shinson","followers_url":"https://api.github.com/users/shinson/followers","following_url":"https://api.github.com/users/shinson/following{/other_user}","gists_url":"https://api.github.com/users/shinson/gists{/gist_id}","starred_url":"https://api.github.com/users/shinson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/shinson/subscriptions","organizations_url":"https://api.github.com/users/shinson/orgs","repos_url":"https://api.github.com/users/shinson/repos","events_url":"https://api.github.com/users/shinson/events{/privacy}","received_events_url":"https://api.github.com/users/shinson/received_events","type":"User","site_admin":false}],"requested_reviewers":[],"milestone":null,"commits_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls/707/commits","review_comments_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls/707/comments","review_comment_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls/comments{/number}","comments_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/issues/707/comments","statuses_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/statuses/0fe80c9bb8fcfcba35cddd63413fb87ba5050ce6","head":{"label":"fedspendingtransparency:missing_agencies_from_subtier","ref":"missing_agencies_from_subtier","sha":"0fe80c9bb8fcfcba35cddd63413fb87ba5050ce6","user":{"login":"fedspendingtransparency","id":8397762,"avatar_url":"https://avatars0.githubusercontent.com/u/8397762?v=4","gravatar_id":"","url":"https://api.github.com/users/fedspendingtransparency","html_url":"https://github.com/fedspendingtransparency","followers_url":"https://api.github.com/users/fedspendingtransparency/followers","following_url":"https://api.github.com/users/fedspendingtransparency/following{/other_user}","gists_url":"https://api.github.com/users/fedspendingtransparency/gists{/gist_id}","starred_url":"https://api.github.com/users/fedspendingtransparency/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/fedspendingtransparency/subscriptions","organizations_url":"https://api.github.com/users/fedspendingtransparency/orgs","repos_url":"https://api.github.com/users/fedspendingtransparency/repos","events_url":"https://api.github.com/users/fedspendingtransparency/events{/privacy}","received_events_url":"https://api.github.com/users/fedspendingtransparency/received_events","type":"Organization","site_admin":false},"repo":{"id":65394827,"name":"usaspending-api","full_name":"fedspendingtransparency/usaspending-api","owner":{"login":"fedspendingtransparency","id":8397762,"avatar_url":"https://avatars0.githubusercontent.com/u/8397762?v=4","gravatar_id":"","url":"https://api.github.com/users/fedspendingtransparency","html_url":"https://github.com/fedspendingtransparency","followers_url":"https://api.github.com/users/fedspendingtransparency/followers","following_url":"https://api.github.com/users/fedspendingtransparency/following{/other_user}","gists_url":"https://api.github.com/users/fedspendingtransparency/gists{/gist_id}","starred_url":"https://api.github.com/users/fedspendingtransparency/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/fedspendingtransparency/subscriptions","organizations_url":"https://api.github.com/users/fedspendingtransparency/orgs","repos_url":"https://api.github.com/users/fedspendingtransparency/repos","events_url":"https://api.github.com/users/fedspendingtransparency/events{/privacy}","received_events_url":"https://api.github.com/users/fedspendingtransparency/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/fedspendingtransparency/usaspending-api","description":"An RESTful API for U.S. federal spending data.","fork":false,"url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api","forks_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/forks","keys_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/keys{/key_id}","collaborators_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/teams","hooks_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/hooks","issue_events_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/issues/events{/number}","events_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/events","assignees_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/assignees{/user}","branches_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/branches{/branch}","tags_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/tags","blobs_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/git/refs{/sha}","trees_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/git/trees{/sha}","statuses_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/statuses/{sha}","languages_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/languages","stargazers_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/stargazers","contributors_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/contributors","subscribers_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/subscribers","subscription_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/subscription","commits_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/commits{/sha}","git_commits_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/git/commits{/sha}","comments_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/comments{/number}","issue_comment_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/issues/comments{/number}","contents_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/contents/{+path}","compare_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/compare/{base}...{head}","merges_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/merges","archive_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/downloads","issues_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/issues{/number}","pulls_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls{/number}","milestones_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/milestones{/number}","notifications_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/labels{/name}","releases_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/releases{/id}","deployments_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/deployments","created_at":"2016-08-10T15:39:45Z","updated_at":"2017-11-10T17:42:31Z","pushed_at":"2017-11-22T15:06:20Z","git_url":"git://github.com/fedspendingtransparency/usaspending-api.git","ssh_url":"git@github.com:fedspendingtransparency/usaspending-api.git","clone_url":"https://github.com/fedspendingtransparency/usaspending-api.git","svn_url":"https://github.com/fedspendingtransparency/usaspending-api","homepage":"","size":45112,"stargazers_count":49,"watchers_count":49,"language":"Python","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":13,"mirror_url":null,"archived":false,"open_issues_count":7,"forks":13,"open_issues":7,"watchers":49,"default_branch":"dev"}},"base":{"label":"fedspendingtransparency:dev","ref":"dev","sha":"ee746cf4d8568f58d2829f014a39feac058aaa8b","user":{"login":"fedspendingtransparency","id":8397762,"avatar_url":"https://avatars0.githubusercontent.com/u/8397762?v=4","gravatar_id":"","url":"https://api.github.com/users/fedspendingtransparency","html_url":"https://github.com/fedspendingtransparency","followers_url":"https://api.github.com/users/fedspendingtransparency/followers","following_url":"https://api.github.com/users/fedspendingtransparency/following{/other_user}","gists_url":"https://api.github.com/users/fedspendingtransparency/gists{/gist_id}","starred_url":"https://api.github.com/users/fedspendingtransparency/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/fedspendingtransparency/subscriptions","organizations_url":"https://api.github.com/users/fedspendingtransparency/orgs","repos_url":"https://api.github.com/users/fedspendingtransparency/repos","events_url":"https://api.github.com/users/fedspendingtransparency/events{/privacy}","received_events_url":"https://api.github.com/users/fedspendingtransparency/received_events","type":"Organization","site_admin":false},"repo":{"id":65394827,"name":"usaspending-api","full_name":"fedspendingtransparency/usaspending-api","owner":{"login":"fedspendingtransparency","id":8397762,"avatar_url":"https://avatars0.githubusercontent.com/u/8397762?v=4","gravatar_id":"","url":"https://api.github.com/users/fedspendingtransparency","html_url":"https://github.com/fedspendingtransparency","followers_url":"https://api.github.com/users/fedspendingtransparency/followers","following_url":"https://api.github.com/users/fedspendingtransparency/following{/other_user}","gists_url":"https://api.github.com/users/fedspendingtransparency/gists{/gist_id}","starred_url":"https://api.github.com/users/fedspendingtransparency/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/fedspendingtransparency/subscriptions","organizations_url":"https://api.github.com/users/fedspendingtransparency/orgs","repos_url":"https://api.github.com/users/fedspendingtransparency/repos","events_url":"https://api.github.com/users/fedspendingtransparency/events{/privacy}","received_events_url":"https://api.github.com/users/fedspendingtransparency/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/fedspendingtransparency/usaspending-api","description":"An RESTful API for U.S. federal spending data.","fork":false,"url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api","forks_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/forks","keys_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/keys{/key_id}","collaborators_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/teams","hooks_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/hooks","issue_events_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/issues/events{/number}","events_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/events","assignees_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/assignees{/user}","branches_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/branches{/branch}","tags_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/tags","blobs_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/git/refs{/sha}","trees_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/git/trees{/sha}","statuses_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/statuses/{sha}","languages_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/languages","stargazers_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/stargazers","contributors_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/contributors","subscribers_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/subscribers","subscription_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/subscription","commits_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/commits{/sha}","git_commits_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/git/commits{/sha}","comments_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/comments{/number}","issue_comment_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/issues/comments{/number}","contents_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/contents/{+path}","compare_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/compare/{base}...{head}","merges_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/merges","archive_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/downloads","issues_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/issues{/number}","pulls_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls{/number}","milestones_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/milestones{/number}","notifications_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/labels{/name}","releases_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/releases{/id}","deployments_url":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/deployments","created_at":"2016-08-10T15:39:45Z","updated_at":"2017-11-10T17:42:31Z","pushed_at":"2017-11-22T15:06:20Z","git_url":"git://github.com/fedspendingtransparency/usaspending-api.git","ssh_url":"git@github.com:fedspendingtransparency/usaspending-api.git","clone_url":"https://github.com/fedspendingtransparency/usaspending-api.git","svn_url":"https://github.com/fedspendingtransparency/usaspending-api","homepage":"","size":45112,"stargazers_count":49,"watchers_count":49,"language":"Python","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":13,"mirror_url":null,"archived":false,"open_issues_count":7,"forks":13,"open_issues":7,"watchers":49,"default_branch":"dev"}},"_links":{"self":{"href":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls/707"},"html":{"href":"https://github.com/fedspendingtransparency/usaspending-api/pull/707"},"issue":{"href":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/issues/707"},"comments":{"href":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/issues/707/comments"},"review_comments":{"href":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls/707/comments"},"review_comment":{"href":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/pulls/707/commits"},"statuses":{"href":"https://api.github.com/repos/fedspendingtransparency/usaspending-api/statuses/0fe80c9bb8fcfcba35cddd63413fb87ba5050ce6"}},"author_association":"CONTRIBUTOR"}}
{"actor":{"display_login":"catherinedevlin"}}