Left outer join with conditions on Active Record

Question

How to performe left outer query with conditions. ActiveRecord, as I read, doesn't allow to make what I want. I try this:

Platform.where(url: urls).includes(:campaign_platforms).where.not('campaign_platforms.campaign_id = ?', campaign.id)

and get "Mysql2::Error: Unknown column 'campaign_id' in 'where clause'..."

UPD:

 class Platform < ActiveRecord::Base
   has_many :campaign_platforms
   has_many :campaigns, through: :campaign_platforms
   has_many :posts
   has_many :screenshoots
   belongs_to :platform_category
   belongs_to :user
 end


 class CampaignPlatforms < ActiveRecord::Base
    belongs_to :campaign, dependent: :destroy
    belongs_to :platform, dependent: :destroy
 end


 class Campaign < ActiveRecord::Base
    has_many :campaign_platforms
    has_many :platforms, through: :campaign_platforms
 end

make sure `campaign_id` column exists in your `campaign_platforms` table — Rajdeep Singh, Nov 06 '14 at 06:11
it exist for sure, as I mentioned, this is AR doesn't allow to make such query. — Michael Karavaev, Nov 06 '14 at 06:15
Try: `Platform.joins(:campaign_platforms).where(url: urls).not(campaign_platforms: {campaign: campaign.id})` — Surya, Nov 06 '14 at 07:02
@User089247, Nope, NoMethodError: undefined method `not' for # As I understood from here http://stackoverflow.com/questions/24358805/left-join-in-rails-4 it must be SQL query in joins — Michael Karavaev, Nov 06 '14 at 07:10
Which version of Rails are you using? And what are you trying to do? — Surya, Nov 06 '14 at 07:21
can you try this, Platform.joins("as p left outer join campaign_platforms as cp on p.id = cp.platform_id").where("...") — rajesh023, Nov 06 '14 at 07:22
@User089247 I want to select platforms that exist in Platform table, that have url from urls variable (given by app), but doesn't belongs to given Campaign (throught campaign_platforms). My last non working attempt: Platform.where(url: urls).joins("left join campaign_platforms ON platforms.id = campaign_platforms.platform_id WHERE campaign_platforms.campaign_id != 195") I use Rails 4.1 — Michael Karavaev, Nov 06 '14 at 08:23
Umm.. @misa - try this: `Platform.joins('LEFT OUTER join campaign_platforms ON campaign_platforms.platform_id = platforms.id').where(url: urls).where.not('campaign_platforms.campaign_id = ?', campaign.id)` — Surya, Nov 06 '14 at 09:02

score 4 · Answer 1 · answered Nov 06 '14 at 09:50

ActiveRecord provides you the ability to specify the JOIN conditions in the joins method.

Platform.joins('JOIN campaign_platforms ON platforms.id = campaign_platforms.platform_id JOIN campaigns ON campaigns.id = campaign_platforms.campaign_id').where('campaign_platforms.campaign_id <> ? AND url IN ?', campaign.id, urls)

That means you can change a JOIN to a LEFT JOIN, LEFT OUTER JOIN or whatever you want.

Most of the people tend to use crazy workaround to avoid writing a single fragment of SQL. Don't be scared. ActiveRecord is not intended to completely replace SQL.

score 1 · Answer 2 · edited Sep 10 '16 at 11:35

1

Perhaps you could use eager_load

Platform.where(url:urls).eager_load(:campaign_platforms).where.not('campaign_platforms.campaign_id = ?', campaign.id)

I have tried left_outer_joins but it threw out an undefined method error.

And it works like charm if your want to display all the Campaign which does not have a campaign platform.

edited Sep 10 '16 at 11:35

V-rund Puro-hit

5,518
9
31
50

answered Sep 10 '16 at 09:30

Hunter

3,080
20
23

score 0 · Answer 3 · answered May 18 '17 at 01:48

Soluction

#controller
@search = Box.distinct.select('boxes.*, people.first_name, people.last_name').includes(:street, :block).order('boxes.created_at DESC')
                  .left_outer_joins(orders: [{user: :people}])
                  .page(params[:page]).ransack(params[:q])
 @boxes = @search.result

#view
<% @boxes.each do |box| %>
<tr>
   <td><%= box.number %><%=box.first_name %><%=box.last_name %></td>
</tr>
<% end %>

Left outer join with conditions on Active Record

3 Answers3