DataMapper filter records by association count

With the following model, I'm looking for an efficient and straightforward way to return all of the Tasks that have 0 parent tasks (the top-level tasks, essentially). I'll eventually want to return things like 0 child tasks as well, so a general solution would be great. Is this possible using existing DataMapper functionality, or will I need to define a method to filter the results manually?

class Task
  include DataMapper::Resource

  property :id,    Serial
  property :name , String, :required => true

  #Any link of type parent where this task is the target, represents a parent of this task 
  has n, :links_to_parents, 'Task::Link', :child_key => [ :target_id ], :type => 'Parent'
  #Any link of type parent where this task is the source, represents a child of this task
  has n, :links_to_children, 'Task::Link', :child_key => [ :source_id ], :type => 'Parent'

  has n, :parents, self,
    :through => :links_to_parents,
    :via => :source

  has n, :children, self,
    :through => :links_to_children,
    :via => :target

  def add_parent(parent)
    parents.concat(Array(parent))
    save
    self
  end

  def add_child(child)
    children.concat(Array(child))
    save
    self
  end
开发者_C百科
  class Link
    include DataMapper::Resource

    storage_names[:default] = 'task_links'

    belongs_to :source, 'Task', :key => true
    belongs_to :target, 'Task', :key => true
    property :type, String
  end

end

I would like to be able to define a shared method on the Task class like:

def self.without_parents
   #Code to return collection here
end

Thanks!

DataMapper falls down in these scenarios, since effectively what you're looking for is the LEFT JOIN query where everything on the right is NULL.

SELECT tasks.* FROM tasks LEFT JOIN parents_tasks ON parents_tasks.task_id = task.id WHERE parents_tasks.task_id IS NULL

You parents/children situation makes no different here, since they are both n:n mappings.

The most efficient you'll get with DataMapper alone (at least in version 1.x) is:

Task.all(:parents => nil)

Which will execute two queries. The first being a relatively simple SELECT from the n:n pivot table (WHERE task_id NOT NULL), and the second being a gigantic NOT IN for all of the id's returned in the first query... which is ultimately not what you're looking for.

I think you're going to have to write the SQL yourself unfortunately ;)

EDIT | https://github.com/datamapper/dm-ar-finders and it's find_by_sql method may be of interest. If field name abstraction is important to you, you can reference things like Model.storage_name and Model.some_property.field in your SQL.