Left Outer Joins in Rails 3

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



Leave a reply



Submit