LEFT OUTER joins in Rails 3
@posts = Post.joins("LEFT OUTER JOIN users ON users.id = posts.user_id").
joins(:blog).select
Outer Join The Rails 3 Way
You need to check that the user id in your discovered_locations table is either equal to the id of the user in question, or is null. This is easily accomplished with the meta_where gem. Given the following models:
class User < ActiveRecord::Base
has_many :discovered_locations
has_many :locations, :through => :discovered_locations
end
class Location < ActiveRecord::Base
has_many :discovered_locations
has_many :users, :through => :discovered_locations
end
class DiscoveredLocation < ActiveRecord::Base
belongs_to :user
belongs_to :location
end
Insert some dummy data, then execute a statement such as this:
Location.includes(:discovered_locations).where(
{:discovered_locations => {:user_id => User.first.id}} |
{:discovered_locations => {:user_id => nil}}
).each do |loc|
puts "#{loc.name} #{loc.discovered_locations.empty? ? 'not visited' : 'visited'}"
end
Rails LEFT OUTER JOIN with Subquery
I was just trying to figure this out and I don't know of any way to do it with pure Rails. But this will do what you want:
Day.joins("LEFT OUTER JOIN (
SELECT * FROM items
WHERE calendar_id IN (1, 4)
) AS items
ON days.sdate = items.sdate
WHERE days.sdate BETWEEN '2015-03-01' AND '2015-03-31'");
I'm not using dates in the query in my code, so I'm not sure if you need the single quotes around the dates or not. But that is the syntax. I think Left joins are very handy, I wish Rails had an actual LEFT JOIN method.
Ruby on Rails: Left Outer Join with Sort
In order to do a LEFT OUTER JOIN try this:
Contact.includes(:recipients).references(:recipients)
Note: This will also preload recipients and add a ton of SQL to the query
If you also want to do the sorting, then you should be able to do so like this:
Contact.includes(:recipients).references(:recipients).group("contacts.id, recipients.id").order("COUNT(recipients.id) ASC")
Rails left_outer_joins multiple conditions
You need to add a condition on items.id
to filter the resulting rows:
languages, items = Language.arel_table, Item.arel_table
conditions = items[:language_id].eq(languages[:id])
.and(items[:version_id].eq('1.0'))
j = languages.outer_join(items)
.on(conditions).join_sources
Language.joins(j)
.where(
items: { id: nil }
)
.where.not(language: { id: 'en'})
SELECT "languages".*
FROM "languages"
LEFT OUTER JOIN "items"
ON "items"."language_id" = "languages"."id"
AND "items"."version_id" = '1.0'
WHERE "items"."id" IS NULL
AND "languages"."id" != ?
You could also use WHERE languages.id NOT IN(subquery)
:
Language.where.not(
id: Language.joins(:items)
.where(items: { version_id: '1.0' })
).where.not(id: 'en')
SELECT "languages".*
FROM "languages"
WHERE "languages"."id" NOT IN (SELECT "languages"."id"
FROM "languages"
INNER JOIN "items"
ON "items"."language_id" =
"languages"."id"
WHERE "items"."version_id" = ?)
AND "languages"."id" != ?
Or NOT EXISTS which may or may not have performance benefits depending on your RDBMS:
Language.where(
Item.select(:id)
.where(
# items.version_id = languages.id
Item.arel_table[:language_id].eq(Language.arel_table[:id])
)
.where(items: { version_id: '1.0'})
.arel
.exists.not
).where.not(id: 'en')
SELECT "languages".*
FROM "languages"
WHERE NOT ( EXISTS (SELECT "items"."id"
FROM "items"
WHERE "items"."language_id" = "languages"."id"
AND "items"."version_id" = ?) )
AND "languages"."id" != ?
LIMIT ?
See Difference between EXISTS and IN in SQL?.
LEFT OUTER JOIN in Rails 4
You can pass a string that is the join-sql too. eg joins("LEFT JOIN StudentEnrollment se ON c.id = se.course_id")
Though I'd use rails-standard table naming for clarity:
joins("LEFT JOIN student_enrollments ON courses.id = student_enrollments.course_id")
Related Topics
Nokogiri/Xpath Namespace Query
Usage of Attr_Accessor in Rails
Altering the Primary Key in Rails to Be a String
Block Definition - Difference Between Braces and Do-End
Checking If a Variable Is Defined
Converting Camel Case to Underscore Case in Ruby
Nokogiri Installation Fails -Libxml2 Is Missing
How to Extract Url Parameters from a Url With Ruby or Rails
What's Different Between Each and Collect Method in Ruby
Group Hashes by Keys and Sum the Values
When to Use 'Self.Foo' Instead of 'Foo' in Ruby Methods
What Does 'Monkey Patching' Exactly Mean in Ruby
How to Get a Single Character Without Pressing Enter
How to Reload the Current Page in Ruby on Rails
Rails Activerecord Perform Group, Sum and Count in One Query