search

mysociety / pombola

GNU Affero General Public License v3.0
65 stars 42 forks source link

NG: Mismatches between Place and Parliamentary session #2033

Open tmtmtmtm opened 8 years ago

tmtmtmtm commented 8 years ago

a Place can have an optional Parliamentary Session that it applies to. However there is no constraint that such a Place can only be associated with Memberships that also relate to that Parliamentary Session:

 id  |         legal_name          |                     organisation                     | orgid |             area              | house_id |              name              
-----+-----------------------------+------------------------------------------------------+-------+-------------------------------+----------+--------------------------------
  74 | ALIYU IBRAHIM GEBI          | GOVT COLLEGE BAUCHI                                  |   334 | Bauchi                        |      120 | House of Representatives 2011-
  80 | AMINU RIBADU HAMMAN-TUKUR   | T.C Bauchi                                           |   347 | Bauchi                        |      120 | House of Representatives 2011-
 143 | CHUKWUEMEKA EMMANUEL NWOGBO | Federal Polytecnic Bauchi                            |   414 | Bauchi                        |      120 | House of Representatives 2011-
 143 | CHUKWUEMEKA EMMANUEL NWOGBO | Abubakar Tafawa Balewa University                    |   250 | Bauchi                        |      120 | House of Representatives 2011-
 197 | GOJE MOHAMMED DANJUMA       | Bauchi House of Assembly                             |   200 | Bauchi                        |      120 | House of Representatives 2011-
 227 | IBRAHIM NUHU KAYARDA LAWAL  | Abubakar Tafawa Balewa University                    |   250 | Bauchi                        |      120 | House of Representatives 2011-
 289 | MAKARFI AHMED M             | Senate                                               |   121 | Kaduna North                  |      120 | House of Representatives 2011-
 305 | MOHAMMED JATAU AUWAL        | Bauchi House of Assembly                             |   200 | Bauchi                        |      120 | House of Representatives 2011-
 394 | SAMUEL OLUSEGUN WILLIAMS    | TSC Bauchi                                           |   746 | Bauchi                        |      120 | House of Representatives 2011-
 440 | USMAN BELLO KUM             | BAUCHI STATE COLLEGE FOR LEGAL STUDIES, BAUCHI STATE |   773 | Bauchi                        |      120 | House of Representatives 2011-
 441 | USMAN NENADI ESTHER         | Senate                                               |   121 | Kaduna South                  |      120 | House of Representatives 2011-
 471 | Adegbenga Sefiu Kaka        | National Assembly                                    |   147 | OGUN EAST                     |      121 | Senate 2011-
 472 | PWAJOK GYANG NYAM SHOM      | PDP                                                  |   545 | PLATEAU NORTH                 |      121 | Senate 2011-
 714 | ABDULRAZAK SAÁD NAMDAS      | Media and Public Affairs Committee (Reps)            |    73 | Mayo Belwa/Ganye/Jada /Toungo |      120 | House of Representatives 2011-
 931 | JIMOH ABDULRAHEEM OLAJIDE   | All Progressives Congress (APC)                      |   835 | LAGOS CENTRAL                 |      121 | Senate 2011-

via

SELECT mp.id, mp.legal_name, o.name AS organisation, o.id AS orgid, a.name AS area, ps.house_id, ps.name
  FROM core_person mp
  LEFT JOIN core_position p ON mp.id = p.person_id
  LEFT JOIN core_positiontitle t ON p.title_id = t.id
  LEFT JOIN core_place a ON a.id = p.place_id
  LEFT JOIN core_placekind pk ON a.kind_id = pk.id
  LEFT JOIN core_organisation o ON p.organisation_id = o.id
  LEFT JOIN core_parliamentarysession ps ON a.parliamentary_session_id = ps.id
 WHERE ps.house_id IS NOT NULL AND ps.house_id != o.id 
   AND NOT (o.id = 203 AND ps.house_id = 120)
 ORDER BY mp.id, mp.legal_name;

(The AND NOT (o.id = 203 AND ps.house_id = 120) is a temporary workaround to there currently being two separate Orgs for the House of Representatives — https://github.com/mysociety/pombola/issues/1974. Interestingly, a constraint on the area here might have prevented that problem, or brought it to light earlier…)

tmtmtmtm commented 8 years ago

As a slight aside: this generally seems like a very odd data model to me, forcing us to replicate every constituency for every parliamentary session. See also https://github.com/mysociety/pombola/issues/2032

It seems like it would be significantly simpler and more flexible to simply let Places have a start_date and end_date.