SoundSoftware Code Site

This gets projects without attachments at all:

select distinct identifier from projects
  where id not in
    (select distinct projects.id from projects, attachments
     where attachments.container_id = projects.id
     and attachments.container_type = 'Project');

Why am I doing this in sql at all? it would be much easier with AR. still, since I'm here:

Projects without attachments at all (revised because attachments may attach to a version) but with changesets, of which the user with the given login is a member:

select distinct projects.identifier from projects, changesets, repositories, members, users
    where members.user_id = users.id
    and users.login = 'login'
    and members.project_id = projects.id
    and projects.id = repositories.project_id
    and changesets.repository_id = repositories.id
    and projects.id not in
      (select distinct projects.id from projects, attachments
       where attachments.container_id = projects.id
       and attachments.container_type = 'Project')
    and projects.id not in
      (select distinct projects.id from projects, attachments, versions
       where attachments.container_id = versions.id
       and attachments.container_type = 'Version'
       and versions.project_id = projects.id)

And projects with attachments attached to versions and with newer changesets, of which a particular user is a member:

select distinct projects.identifier from attachments, versions, projects, changesets, repositories, members, users
    where members.user_id = users.id
    and users.login = 'login'
    and members.project_id = projects.id
    and attachments.container_id = versions.id 
    and attachments.container_type = 'Version'
    and versions.project_id = projects.id
    and changesets.repository_id = repositories.id 
    and repositories.project_id = projects.id 
    and changesets.committed_on > attachments.created_on

Ah no, that just finds commits newer than any attachment for the project.

Crude method in code for listing projects with commits but no attachments, of which user u is a member:

Project.all.select { |p| p.users.member?(u) }.select do |p|
  c = p.repository.latest_changeset.committed_on if
    not p.repository.nil? and not p.repository.latest_changeset.nil?;
  a = p.attachments.map { |a| a.created_on }.compact.sort.last;
  v = p.versions.map { |v| v.attachments.map { |a| a.created_on }.compact.sort.last }.compact.sort.last;
  not c.nil? and a.nil? and v.nil?
end.map { |p| p.identifier }

and projects with commits more recent than the last attachment:

Project.all.select { |p| p.users.member?(u) }.select do |p|
  c = p.repository.latest_changeset.committed_on if
    not p.repository.nil? and not p.repository.latest_changeset.nil?;
  a = p.attachments.map { |a| a.created_on }.compact.sort.last;
  v = p.versions.map { |v| v.attachments.map { |a| a.created_on }.compact.sort.last }.compact.sort.last;
  not c.nil? and
    ((not v.nil? and c > v) or
     (not a.nil? and c > a))
end.map { |p| p.identifier }