Rails ActiveRecord Join Query With conditions

Question

I have following SQL Query:

SELECT campaigns.* , campaign_countries.points, offers.image
FROM campaigns
JOIN campaign_countries ON campaigns.id = campaign_countries.campaign_id
JOIN countries ON campaign_countries.country_id = countries.id
JOIN offers ON campaigns.offer_id = offers.id
WHERE countries.code = 'US'

This works perfectly well. I want its rails active record version some thing like:

Campaign.includes(campaign_countries: :country).where(countries: {code: "US"})

Above code runs more or less correct query (did not try to include offers table), issue is returned result is collection of Campaign objects so obviously it does not include Points

My tables are:

campaigns --HAS_MANY--< campaign_countries --BELONGS_TO--< countries
campaigns --BELONGS_TO--> offers

Any suggestions to write AR version of this SQL? I don't want to use SQL statement in my code.

score 1 · Answer 1 · answered Aug 23 '13 at 06:11

I some how got this working without SQL but surely its poor man's solution:

in my controller I have:

campaigns = Campaign.includes(campaign_countries: :country).where(countries: {code: country.to_s})
render :json => campaigns.to_json(:country => country)

in campaign model:

  def points_for_country country
    CampaignCountry.joins(:campaign, :country).where(countries: {code: country}, campaigns: {id: self.id}).first
  end

  def as_json options={}
    json = {
      id: id,
      cid: cid, 
      name: name,
      offer: offer,
      points_details: options[:country] ? points_for_country(options[:country]) : ""
    }
  end

and in campaign_countries model:

  def as_json options={}
    json = {
        face_value: face_value,
        actual_value: actual_value,
        points: points
    }
  end

Why this is not good solution? because it invokes too many queries: 1. It invokes query when first join is performed to get list of campaigns specific to country 2. For each campaign found in first query it will invoke one more query on campaign_countries table to get Points for that campaign and country.

This is bad, Bad and BAD solution. Any suggestions to improve this?

Michał Simka · Accepted Answer · 2013-08-23T06:59:46.593

0

If You have campaign, You can use campaign.campaign_countries to get associated campaign_countries and just get points from them.

> campaign.campaign_countries.map(&:points)
=> [1,2,3,4,5]

Similarly You will be able to get image from offers relation.

EDIT:

Ok, I guess now I know what's going on. You can use joins with select to get object with attached fields from join tables.

cs = Campaign.joins(campaign_countries: :country).joins(:offers).select('campaigns.*, campaign_countries.points, offers.image').where(countries: {code: "US"})

You can than reference additional fields by their name on Campaign object

cs.first.points
cs.first.image

But be sure, that additional column names do not overlap with some primary table fields or object methods.

EDIT 2:

After some more research I came to conclusion that my first version was actually correct for this case. I will use my own console as example.

> u = User.includes(:orders => :cart).where(:carts => { :id => [5168, 5167] }).first
> u.orders.length # no query is performed
=> 2
> u.orders.count # count query is performed
=> 5

So when You use includes with condition on country, in campaign_countries are stored only campaign_countries that fulfill Your condition.

edited Aug 23 '13 at 06:59

answered Aug 22 '13 at 19:11

Michał Simka

134
1
5

I think my answer should now satisfy You, but it's not very Railish way I think. – Michał Simka Aug 23 '13 at 06:25
Now (after second edit) I'm pretty sure that this is what are You looking for – Michał Simka Aug 23 '13 at 06:58
I am trying your second edit, I am not sure if it will work, but looks like `cs.first.points cs.first.image` if this works then it will be good. – Rakesh Aug 25 '13 at 17:26
Great!!! The second edit I did not try, I guess it will invoke multiple queries, but yes the first edit is really good. Of course not 100% Railish way, but I guess it should be fine because it involves very basic SQL, which should be compatible to any backend db system. There is one more thing I am concern about, performance of this single line statement. But I guess I will look into it in future :) Thanks! – Rakesh Aug 25 '13 at 17:43

score 0 · Answer 3 · answered Aug 22 '13 at 19:47

0

Try this:

Campaign.joins( [{ :campaign_countries =>  :countries}, :offers]).where('`countries`.`code` = ?', "US")

answered Aug 22 '13 at 19:47

Manoj Sehrawat

1,283
1
10
25

Join gives same result as Include – Rakesh Aug 23 '13 at 04:23
1

Both are different. Checkout [rails-include-vs-joins](http://stackoverflow.com/questions/1208636/rails-include-vs-joins) and [railscast](http://railscasts.com/episodes/181-include-vs-joins) – Manoj Sehrawat Aug 23 '13 at 06:08

Rails ActiveRecord Join Query With conditions

3 Answers3