Scope That Has Three Levels Deep Joins

Writing scope, join and order for a Model

Try,

scope :some_scope, joins(mesures: :targets)

Applying two scopes in one ActiveRecord query

You can chain scopes and includes.

scope :rpc, includes(:registry_patient_counts)
scope :msr, includes(measures: :measures_off_targets)

@programs = Program.rpc.msr.where(organization_id: 1).limit(2)

# Equivalent to
@programs = Program.includes(
  [:registry_patient_counts, measures: :measures_off_targets])
  .where(organization_id: 1)
  .limit(2)

ActiveRecord 2 level deep scope

You need:

 has_many :comments
 has_many :users, :through => :comments

ActiveRelation that requires nested joins using scopes

I would probably use something like this. I kept scopes separate for clarity.

Models (renamed PrivateMessage -> Message and Recipient -> MessageCopy):

class User < ActiveRecord::Base
  has_many :sent_messages, :class_name => "Message", :foreign_key => :sender_id
  has_many :sent_message_copies, :through => :sent_messages, :source => :message_copies
  has_many :received_messages, :through => :received_message_copies, :source => :message
  has_many :received_message_copies, :class_name => "MessageCopy", :foreign_key => :recipient_id
end

class Message < ActiveRecord::Base
  belongs_to :sender, :class_name => "User"
  has_many :message_copies
  has_many :recipients, :through => :message_copies
end

class MessageCopy < ActiveRecord::Base
  belongs_to :message
  belongs_to :recipient, :class_name => "User"
  scope :unread, where(:read => false)
  scope :undeleted, where(:deleted => false)
  scope :sent_to, lambda { |recipient| where(:recipient_id => recipient.id) }
end

Schema (migrations would have taken too much space here):

ActiveRecord::Schema.define(:version => 20110503061008) do
  create_table "message_copies", :force => true do |t|
    t.boolean  "read",         :default => false
    t.boolean  "deleted",      :default => false
    t.integer  "message_id"
    t.integer  "recipient_id"
  end
  create_table "messages", :force => true do |t|
    t.string   "title"
    t.integer  "sender_id"
  end
  create_table "users", :force => true do |t|
    t.string   "name"
  end
end

--edit

Example query using joins returning messages

Message.joins(:message_copies).where(:message_copies => {:read => false, :deleted => false, :recipient_id => 3})

Message scope reusing scopes on other model

scope :non_deleted_by_recipient, lambda { |recipient|
  joins(:message_copies).merge(MessageCopy.unread.undeleted.sent_to(recipient))
}

--edit2

This Railscast has nice examples of both joins and scopes:

• http://railscasts.com/episodes/215-advanced-queries-in-rails-3

deeply nested joins in activerecord

For 'eager loading' you use .include; .join is for performing INNER JOIN functionality. It seems likely that for your case you will be using both (the join to get the photo, and the includes to get the additional people). I make this point because .join alone will not perform eager loading (and so it will make the queries afterward when the associated entities are accessed).

If you wish to eager-load nested associations, you can use the syntax outlined in the Ruby on Rails guide at http://guides.rubyonrails.org/active_record_querying.html#eager-loading-multiple-associations

Would something along these lines work?

Photo.joins(:appearances => :person).includes(:appearances => :person).limit(5)

Or do you need to start with the person?

Person.joins(:appearances => :photo).includes(:appearances => :photo => :appearaces => :person).limit(5)

Also, as a final (small) note: you have ":appearances" spelled incorrectly in your last line of code (it states ":appearaces"), which could cause issues.

EDIT:
After some minor testing, it appears that nesting the include associations in the

a => b => c => b => a

form doesn't work. However, the following form does:

a => [b => [c => [b => a]]]

Though, of course, it isn't quite as pretty.

Meaning, using the following code:

Person.includes(:appearances => [:photo => [:appearances => :person]]).find(38)       

The following SQL was generated:

Person Load (0.3ms)  SELECT `persons`.* FROM `persons` WHERE `persons`.`id` = 38 LIMIT 1
Appearance Load (0.3ms)  SELECT `appearances`.* FROM `appearances` WHERE (`appearances`.person_id = 38)
Photo Load (0.3ms)  SELECT `photos`.* FROM `photos` WHERE (`photos`.`id` = 1904)
Appearance Load (0.3ms)  SELECT `appearances`.* FROM `appearances` WHERE (`appearances`.photo_id = 1904)
Person Load (0.3ms)  SELECT `persons`.* FROM `persons` WHERE (`persons`.`id` IN (38,346))

Eager loading the additional Persons from all Photos that include Person with id #38

Finding values of an array in a loop

try the following

def patient_counts(program)
  sorted = program.patient_counts.sort { |a, b| b.money <=> a.money }
  sorted[0..4]
end

UPDATE: limiting to just 5 records fetched from the db

def patient_counts(program)
  program.registry_patient_counts.limit(5).order('patient_count_percentage DESC')
end

Laravel, getting deep relationships

To find all the tenants in a given building, the easiest method would be to use JOIN clauses.

I have assumed all of your relationships are hasMany inversed by belongsTo.

$tenants = Tenant::select('tenants.*')
    ->join('units', 'units.id', '=', 'tenant.unit_id')
    ->join('blocks', 'blocks.id', '=', 'units.block_id')
    ->join('buildings', 'buildings.id', '=', 'blocks.building_id')
    ->where('buildings.id', 123)
    ->get();

If this is something you'll use more than once, then I'd suggest creating a query scope on your Tenant model.

class Tenant extends Eloquent
{
    // ...

    public function scopeInBuilding($query, $buildingId)
    {
        return $query->select('tenants.*')
            ->join('units', 'units.id', '=', 'tenant.unit_id')
            ->join('blocks', 'blocks.id', '=', 'units.block_id')
            ->join('buildings', 'buildings.id', '=', 'blocks.building_id')
            ->where('buildings.id', $buildingId);
    }

    // ...
}

And you can use it as follows:

$tenants = Tenant::inBuilding(123)->get();

Eloquent with nested whereHas

Update: the PR has been just merged to 4.2, so now it's possible to use dot nested notation in has methods ( ->has('relation1.relation2) ->whereHas('relation1.relation2, .. )

Your question remains a bit unclear or you misunderstand whereHas() method as it is used to filter models (users in this case) and get only those that have related models fitting search conditions.

It seems that you want to find Packages from the context of a given User, so no need to use whereHas method.

Anyway depending on the relations (1-1,1-m,m-m) this can be easy or pretty hard and not very efficient. As I stated, loading nested relations means that for every level of nesting comes another db query, so in this case you end up with 5 queries.

Regardless of the relations you can invert this chain like this, as it will be easier:

---

edit: This is not going to work atm as whereHas() doesn't process dot nested relations!

// given $user and $search:
$packages = Package::where('alias','like',"%$search%")
  ->whereHas('product.membership.club.user', function ($q) use ($user) {
    $q->whereId($user->id);
  })->get();

---

As you can see this is much more readable, still runs 5 queries.
Also this way you get $packages, which is a single Collection of the models you wanted.

While from the context of a user you would get something like this (depending on the relations again):

$user
 |-club
 |  |-membership
 |  |  |-product
 |  |  |  |-packages
 |  |  |-anotherProduct
 |  |  |  |-packages
 |  |  |-yetAnotherProduct
 |  |     |-packages
 |  |-anotherMembership
  .....

You get the point, don't you?

You could fetch the packages from the Collection, but it would be cumbersome. It's easier the other way around.

So the answer to your question would be simply joining the tables:

// Let's assume the relations are the easiest to handle: 1-many
$packages = Package::where('alias','like',"%$search%")
  ->join('products','packages.product_id','=','products.id')
  ->join('memberships','products.membership_id','=','memberships.id')
  ->join('clubs','memberships.club_id','=','clubs.id')
  ->where('clubs.user_id','=',$user->id)
  ->get(['packages.*']); // dont select anything but packages table 

Of course you can wrap it in a nice method so you don't have to write this everytime you perform such search.
Performance of this query will be definitely much better than separate 5 queries shown above. Obviously this way you load only packages, without other related models.

---

Related Topics