CODEWITHSUNDEEP
sqlruby-on-rails
Joe Essey
Joe Essey

Reputation: 3527

How to use joins in a scope?

I've got a page that uses multiple filters and everything works ok except for one filter. The filter that doesn't work needs to check which foos do not have any bars. That is to say no bar record will have a foo_id matching the foos I wish to display.

For this example, let's do a filter on room and on foos with no_bars

I've got something like this in scopes (foo.rb):

  scope :not_validated, where("mark_validated = false")
  scope :not_located, where("grid_location_id IS NULL")
  scope :no_bars, joins("LEFT JOIN bars ON foos.id = bars.foo_id where").where("bars.id IS NULL")

  scope :room_1, where("room_id = 1")
  scope :room_2, where("room_id = 2")

I have a filter parial:

<%= dropdown_tag filter_button_name("Room", "room") do %>
    <li><%= multi_filter_link "room", "room_1"%></li>
    <li><%= multi_filter_link "room", "room_2" %></li>
<% end %>

<%= dropdown_tag filter_button_name("Filter By", "filter") do %>
    <li><%= multi_filter_link "filter", "not_validated" %></li>
    <li><%= multi_filter_link "filter", "not_located" %></li>
    <li><%= multi_filter_link "filter", "no_bars" %></li>
<% end %>

When I try to view the foos with the no_bars filter, I get the following error:

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (bars.id IS NULL)' at line 1: SELECT `foos.*` FROM `foos` LEFT JOIN bars ON foos.id = bars.foo_id where WHERE (bars.id IS NULL)

Upvotes: 0

Views: 73

Answers (1)

JoseTeixeira
JoseTeixeira

Reputation: 1306

scope :no_bars, joins("LEFT JOIN bars ON foos.id = bars.foo_id where").where("bars.id IS NULL")

I think you want:

scope :no_bars, joins("LEFT JOIN bars ON foos.id = bars.foo_id").where("bars.id IS NULL")

Upvotes: 1

Related Questions