Complex database query with multiple ids

Question

I have 3 models which are connected like this:

class FooTemplate < ActiveRecord::Base
    has_many :foos
end

class Foo < ActiveRecord::Base
    belongs_to :foo_template
    belongs_to :bar
end

class Bar < ActiveRecord::Base
    has_many :foos
end

Inside the Barmodel I want a method that finds all Bars that have Foos that belong to each FooTemplate referenced by the ids in the array:

def find_by_foo_templates(foo_template_ids)
    # what goes here?
end

The parameter is an array which has always the following form:

[""]
["", "1", "2"]

The array always contains an empty string even if no ids are submitted.

I hope you understand what I am trying to do.

Update

Let me show you an example:

Bar: 1
  Foo: 1
    FooTemplate: 1
  Foo: 2
    FooTemplate: 2

Bar: 2
  Foo: 3
    FooTemplate: 2
  Foo: 4
    FooTemplate: 3

Bar: 3
  Foo: 5
    FooTemplate: 3
  Foo: 6
    FooTemplate: 4

This shall be 3 Bars each with 2 independent Foos. And the Foos have some "overlapping" FooTemplates.

Now the desired results for some lists:

["1"] schould only return Bar 1, because it's the only one whose Foos have a FooTemplate 1.
["2"] should return Bar 1 & 2, because they both have a Foo with a FooTemplate 2
["2", "3"] should return only Bar 2, because it's the only one which has a Foo with FooTemplate 2 AND a Foo with FooTemplate 3
["1", "4"] should return nothing because there is no Bar whose Foos have FooTemplates 1 AND 4

Update 2

I have found a solution that works, but it uses reject and it produces some more database queries:

class Bar < ActiveRecord::Base
    has_many :foos
    has_many :foo_templates, through: :foos

    def self.find_by_foo_template_ids(foo_template_ids)
        ids = foo_template_ids.map { |id| id.to_i }

        joins(foos: :foo_template).uniq.where(foos: { foo_template_id: ids }).reject do |bar|
            !(bar.foo_template_ids & ids == ids)
        end
    end
end

And this returns an array, but I would like to have an ActiveRecord::Relation to perform additional queries on it.

Marek Lipka · Accepted Answer · 2013-07-01 21:05:51Z

2

def self.find_by_foo_templates(foo_template_ids)
  joins(:foos => :foo_template).where(['foo_templates.id in (?)', foo_template_ids.reject!(&:empty?)])
end

answered Jul 1, 2013 at 21:05

Marek Lipka

51.3k7 gold badges90 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

stevecross Over a year ago

This returns multiple Bars. I just want a Bar to appear once and the Foos that belong to a Bar that is returned must belong to all FooTemplates that are in the list.

MrTheWalrus · Accepted Answer · 2013-07-01 22:08:32Z

0

N.B. This is almost certainly PostGres only. It also feels like a bit of a hack. But I think it will work.

def self.find_by_foo_templates(foo_template_ids)
  joins(:foos => :foo_template).
  group('bars.id').
  having("array_agg(foo_templates.id) @> string_to_array(?, ',')::int[]", foo_template_ids.join(','))
end

This should aggregate the ids of the FooTemplates that go with the Foos associated with each returned Bar, and return only the Bars for which this contains all the input ids.

answered Jul 1, 2013 at 22:08

MrTheWalrus

9,7102 gold badges44 silver badges67 bronze badges

3 Comments

the Tin Man Over a year ago

Where does PostgreSQL come from? The OP doesn't mention using it.

stevecross Over a year ago

I am using SQLite 3 at the moment.

MrTheWalrus Over a year ago

@theTinMan It also didn't mention not using PostGresSQL (well, until now), and it's pretty common in the Rails world, so I figured I'd provide the solution I was aware of. I don't know how to do this in SQLite off the top of my head, but it may at least help someone else with a similar problem.

Collectives™ on Stack Overflow

Complex database query with multiple ids

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related