CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Rakesh
Rakesh

Reputation: 73

Rails ActiveRecord Join Query With conditions

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.

Upvotes: 0

Views: 9360

Answers (3)

Michał Simka
Michał Simka

Reputation: 134

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.

Upvotes: 0

Rakesh
Rakesh

Reputation: 73

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?

Upvotes: 1

Manoj Sehrawat
Manoj Sehrawat

Reputation: 1303

Try this: 

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

Upvotes: 0

Related Questions