CODEWITHSUNDEEP
rubyormruby-datamapper
Joel
Joel

Reputation: 2257

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

  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!

Upvotes: 2

Views: 667

Answers (1)

d11wtq
d11wtq

Reputation: 35298

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.

Upvotes: 4

Related Questions