Rails Activerecord Query Using Inner Join

Rails ActiveRecord query using inner join

You're getting your join math wrong and it's having the effect of creating rows for each user + foo combination. This is how a full join works. The reason for this slip is because you haven't actually joined the bars table to the users table. Normally you have to join with a condition, like in your case bars.user_id=users.id would be a good idea.

That being said, what you want to do instead is determine which users qualify, then load those:

users = User.where('id IN (SELECT DISTINCT user_id FROM bars WHERE bars.foo_id!=?)', 5)

This sub-select, if run on its own, should return simply a list of users without that particular foo. Using this as a WHERE condition should load only those users.

ActiveRecord Join Query and select in Rails

If the column in select is not one of the attributes of the model on which the select is called on then those columns are not displayed. All of these attributes are still contained in the objects within AR::Relation and are accessible as any other public instance attributes.

You could verify this by calling first.client_name:

Project.joins(:client)
       .select('projects.id,projects.name,clients.name as client_name')
       .first.client_name

How to use inner join in Ruby On Rails

In this simple case Rails does not use a join, it joins "in code":

Account.includes(:details).where(:id => current_account_id).first

It will make two separate queries.

If you need a condition for the select, you have to join "by hand" (or through a scope)

Account.joins(:details).where("details.name" => selected_detail).first

This will make an INNER JOIN and return only accounts satistfying the conditions.

Ruby on Rails ActiveRecord query using a join

This should work:

User.joins(:pets).where("pets.name != 'fluffy'")

Also you might want to read the following part (on joins) on the official RoR guidelines.

Rails ActiveRecord subquery on Inner Join

You may send the subquery as a string to the joins method:

subquery =
  TotalQuestion.
    joins(:assessments).
    group('assessments.id').
    select('assessments.id, COUNT(q.id) as total_questions').to_sql

Question.joins("(#{sub_query}) as tq on tq.id=ass.id")

And you can combine it with the other parts of the query:

Question.
    joins(:assessment => {:org_assessments => {:org => :users}}).joins(:answers).
    joins("(#{sub_query}) as tq on tq.id=ass.id").
    where(answers:{:user_id => params[:id]}).
    distinct('answers.question_id').group(['assessments.name']).count()

Rails - deeply nested joins with ActiveRecord

Try this

Store.joins(products: [product_variants: [order_list_items: :consumers]])

Below is the sql query

SELECT "stores".* FROM "stores" 
INNER JOIN "products" 
ON "products"."store_id" = "stores"."id" 
INNER JOIN "product_variants" 
ON "product_variants"."product_id" = "products"."id" 
INNER JOIN "order_list_items" 
ON "order_list_items"."product_variant_id" = "product_variants"."id" 
INNER JOIN "consumers" 
ON "consumers"."order_list_item_id" = "order_list_items"."id";

Rails perform inner join with active record complex query

Adding a few more associations to some of your models should help a bit.

class School < ActiveRecord::Base
  # in addition to existing relations
  has_many :leads, through: :programs
  has_many :cards, through: :leads
end

class Representative < ActiveRecord::Base
  # in addition to existing relations
  has_many :programs, through: :schools
  has_many :leads, through: :programs
  has_many :cards, through: :leads
end

Then, the simple way to get the number of cards that a single school or representative has should be simple.

@school.cards.count
@representative.cards.count

If you'd to get the number of unique cards, i.e. a card could be associated to multiple programs within the same school or representative and you'd like to count each one only once, then use distinct.

@school.cards.distinct.count
@representative.cards.distinct.count

Now, if you're looking to get the number of cards that each school and representative has, that's a bit trickier, but still possible.

@schools = School.select('schools.*, count(cards.id)')
                 .joins(programs: { leads: :card } )
                 .group(:id)

@reps = Representative.select('representative.*, count(cards.id)')
                      .joins(schools: { programs: { leads: :card } } )
                      .group(:id)

If you'd like the unique number of cards for each school or representative, just change count(cards.id) to count(distinct cards.id) in the appropriate query.

---

A few notes about these solutions:

• If a school or representative has zero cards, they will not appear in in the @schools and @representatives variables. In order to make them appear, you'll need to adjust the query a bit and use LEFT JOIN.
• You probably don't even really need the association to :cards in the School and Representative models to get the counts; you could probably just use the :leads association. However, this would make getting distinct a bit more difficult, so that's why I went with :cards.

How to write two inner join for Rails Active Record query

Hey try in this way

categories = Category.where(:id => Category.joins(:coaches).where("coaches.id = ?", 17).group(:parent_id).map(&:parent_id))

---

Related Topics