Used to answer some Popolo questions on Poplus list.

events.yml

- id: foyle-2006
  start_date: "2007-01-01"
  end_date: "2010-12-31"

- id: foyle-2010
  start_date: "2011-01-01"
  end_date: "2014-12-31"

- id: foyle-2014
  start_date: "2015-01-01"
  end_date: "2018-12-31"

- id: northern-ireland-2003
  start_date: 2003-11
  end_date: 2007-03

- id: northern-ireland-2007
  start_date: 2007-03
  end_date: 2011-03

- id: northern-ireland-2011
  start_date: 2011-05
  end_date: 2016-05

- id: house-of-commons-2005
  start_date: 2005-05
  end_date: 2010-05

- id: house-of-commons-2010
  start_date: 2010-05
  end_date: 2015-05

- id: european-parliament-2014
  start_date: 2014-05
  end_date: 2019-05

Gemfile

source 'https://rubygems.org'

gem 'data_mapper'
gem 'dm-postgres-adapter'

load_mongodb.rb

require 'rubygems'
require 'bundler/setup'

require 'yaml'

require 'moped'

connection = Moped::Session.new(['localhost'], database: 'popolo_test')

YAML.load_file(File.join(File.expand_path('./events.yml', __dir__))).each do |hash|
  connection[:events].insert(hash)
end

YAML.load_file(File.join(File.expand_path('./memberships.yml', __dir__))).each do |hash|
  hash[:person_id] = 'joe-bloggs'
  connection[:memberships].insert(hash)
end

load_postgresql.rb

require 'rubygems'
require 'bundler/setup'

require 'yaml'

require 'data_mapper'

DataMapper.setup(:default, 'postgres://localhost/popolo_test')

class Event
  include DataMapper::Resource

  property :id, Text, key: true

  property :start_date, Text
  property :end_date, Text

  has n, :memberships, child_key: :legislative_session_id
end

class Membership
  include DataMapper::Resource

  property :id, Serial

  property :person_id, Text
  property :organization_id, Text
  property :on_behalf_of_id, Text
  property :post_id, Text

  property :start_date, Text
  property :end_date, Text
  property :role, Text

  belongs_to :legislative_session, 'Event'
end

DataMapper.auto_migrate!

YAML.load_file(File.join(File.expand_path('./events.yml', __dir__))).each do |hash|
  Event.create(hash)
end

YAML.load_file(File.join(File.expand_path('./memberships.yml', __dir__))).each do |hash|
  hash[:person_id] = 'joe-bloggs'
  Membership.create(hash)
end

# List organizations, terms and memberships
def format(date)
  Date.parse(date.size == 10 ? date : "#{date}-01").strftime('%b %Y')
end

Membership.all.group_by(&:organization_id).each do |organization_id,memberships|
  puts organization_id || '<null>'
  memberships.group_by(&:legislative_session).each do |legislative_session,memberships|
    results = []
    memberships.each do |membership|
      result = if membership.on_behalf_of_id
        membership.on_behalf_of_id.upcase
      else
        'Independent'
      end
      result << ' '
      if membership.start_date && (membership.start_date != legislative_session.start_date || membership.end_date && membership.end_date != legislative_session.end_date)
        result << "("
        if membership.role
          result << "#{membership.role} "
        end
        if membership.start_date == legislative_session.start_date
          result << "start"
        else
          result << membership.start_date
        end
        result << " → "
        if membership.end_date == legislative_session.end_date
          result << "end"
        elsif membership.end_date
          result << membership.end_date
        end
        result << ")"
      elsif membership.role
        result << "#{membership.role} "
      end
      results << result
    end
    puts "#{format(legislative_session.start_date)}-#{format(legislative_session.end_date)} term: #{results.join('; ')}"
  end
end

memberships.yml

# To avoid repetition, assume "person_id: joe-bloggs” appears on all memberships.



# Foyle District Council

  #  Dec 2006-Dec 2010 term: OUP [Mayor 2007-01-01 → 2007-12-31]
- organization_id: foyle-district-council
  legislative_session_id: foyle-2006
  start_date: 2007-01-01
  end_date: 2007-12-31
  on_behalf_of_id: oup
  role: mayor
  post_id: foyle-mayor # optional

  #  Dec 2010-Dec 2014 term: OUP (start → 2013-07-12)
- organization_id: foyle-district-council
  legislative_session_id: foyle-2010
  start_date: 2011-01-01
  end_date: 2013-07-12
  on_behalf_of_id: oup

  #  Dec 2010-Dec 2014 term: Independent (2013-07-12 → end)
- organization_id: foyle-district-council
  legislative_session_id: foyle-2010
  start_date: 2013-07-13
  end_date: 2014-12-31
  on_behalf_of_id: 
  # See http://www.popoloproject.com/appendices/data-patterns.html#unknown-or-null
  # for a discussion of distinguishing unknown values from null values

  #  Dec 2014-Dec 2018 term: TUP candidate; not elected
- legislative_session_id: foyle-2014
  on_behalf_of_id: tup
  role: candidate
  post_id: foyle-candidate-123
  # Candidacy needs more thought. I’m using the YourNextMP model here of referring
  # to a post but not an organization. I think Candidacy should just be a new
  # class, with different semantics from Membership.



# Northern Ireland Assembly

  #  Nov 2003–Mar 2007 term: OUP
- organization_id: northern-ireland-assembly
  legislative_session_id: northern-ireland-2003
  start_date: 2003-11
  end_date: 2007-03
  on_behalf_of_id: oup

  #  Mar 2007–Mar 2011 term: OUP
- organization_id: northern-ireland-assembly
  legislative_session_id: northern-ireland-2007
  start_date: 2007-03
  end_date: 2011-03
  on_behalf_of_id: oup

  #  May 2011–May 2016 term: OUP (start → 2013-07-12)
- organization_id: northern-ireland-assembly
  legislative_session_id: northern-ireland-2011
  start_date: 2011-05
  end_date: 2013-07-12
  on_behalf_of_id: oup

  #  May 2011–May 2016 term: Independent (2013-07-12 → 2013-10-01)
- organization_id: northern-ireland-assembly
  legislative_session_id: northern-ireland-2011
  start_date: 2013-07-12
  end_date: 2013-10-01
  on_behalf_of_id: 

  #  May 2011–May 2016 term: TUP (2013-10-10 →)
- organization_id: northern-ireland-assembly
  legislative_session_id: northern-ireland-2011
  start_date: 2013-10-01
  on_behalf_of_id: tup



# House of Commons

  #  May 2005–May 2010 term: OUP
- organization_id: house-of-commons
  legislative_session_id: house-of-commons-2005
  start_date: 2005-05
  end_date: 2010-05
  on_behalf_of_id: oup

  #  May 2010–May 2015 term: OUP (start → 2013-07-12)
- organization_id: house-of-commons
  legislative_session_id: house-of-commons-2010
  start_date: 2010-05
  end_date: 2013-07-12
  on_behalf_of_id: oup

  #  May 2010–May 2015 term: Independent (2013-07-12 → 2013-10-15)
- organization_id: house-of-commons
  legislative_session_id: house-of-commons-2010
  start_date: 2013-07-12
  end_date: 2013-10-15
  on_behalf_of_id: 

  #  May 2010–May 2015 term: UKIP (2013-10-15 → 2014-05-25 [resigned])
- organization_id: house-of-commons
  legislative_session_id: house-of-commons-2010
  start_date: 2013-10-15
  end_date: 2014-05-25
  on_behalf_of_id: ukip
  # To add the “[resigned]", see the thread about reasons for start/end dates.



# European Parliament

  #  May 2014–May 2019 term: AECR (independent)
- organization_id: european-parliament
  legislative_session_id: european-parliament-2014
  start_date: 2014-05
  on_behalf_of_id: aecr
  # I don’t know what a "AECR independent” means, so I’m just modeling “AECR”.

query.js

// Which terms did members not complete?
var criteria = {},
    identity_map = {},
    ids;

var groups = db.memberships.aggregate([{
  $match: criteria
}, {
  $group: {
    _id: {
      person_id: '$person_id',
      legislative_session_id: '$legislative_session_id',
    },
    end_date: {$max: {$ifNull: ['$end_date', '?']}}
  }
}]).toArray();

ids = groups.map(function (group) {
  return group._id.legislative_session_id;
});

db.events.find({id: {$in: ids}}, {id: 1, end_date: 1}).forEach(function (e) {
  identity_map[e.id] = e;
});

groups.filter(function (group) {
  return group.end_date < identity_map[group._id.legislative_session_id].end_date;
});

// Which terms did members not start?
var criteria = {},
    identity_map = {},
    ids;

var groups = db.memberships.aggregate([{
  $match: criteria
}, {
  $group: {
    _id: {
      person_id: '$person_id',
      legislative_session_id: '$legislative_session_id',
    },
    start_date: {$min: {$ifNull: ['$start_date', '']}}
  }
}]).toArray();

ids = groups.map(function (group) {
  return group._id.legislative_session_id;
});

db.events.find({id: {$in: ids}}, {id: 1, start_date: 1}).forEach(function (e) {
  identity_map[e.id] = e;
});

groups.filter(function (group) {
  return group.start_date > identity_map[group._id.legislative_session_id].start_date;
});

query.sql

-- Which terms did members not complete?
SELECT m.person_id, e.id
  FROM memberships m
  INNER JOIN events e
    ON m.legislative_session_id = e.id
  GROUP BY m.person_id, e.id
  HAVING MAX(COALESCE(m.end_date, '?')) < e.end_date;

-- Which terms did members not start?
SELECT m.person_id, e.id
  FROM memberships m
  INNER JOIN events e
    ON m.legislative_session_id = e.id
  GROUP BY m.person_id, e.id
  HAVING MIN(COALESCE(m.start_date, '?')) > e.start_date;