CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
James Ramsfield
James Ramsfield

Reputation: 87

howto get friends of friends using active record?

I have implemented friends relationsships by using the same procedure as in http://railscasts.com/episodes/163-self-referential-association. I want to fetch friends of friends from the same city, but I cannot figure out howto get friends of friends and from the same city.

I tried calling:

User.find_by(first_name: user_name).friendships.friendships(:f_of_f).cities.where(name: "london").pluck(:f)

and it didnt work.

my code is the following:

class User < ActiveRecord::Base
  has_many :friendships
  has_many :friends, through: :friendships
  has_many :inverse_friendships, class_name: 'Friendship', foreign_key: 'friend_id'
  has_many :inverse_friends, through: :inverse_friendships, source: :user
  has_many :cities, through: :user_cities
  has_many :user_cities
  belongs_to :company
  has_many :comments, dependent: :delete_all
  has_many :reviews, dependent: :delete_all
  devise :database_authenticatable, :registerable,
         :recoverable, :rememberable, :trackable, :validatable

  def self.friends_reviews_on_company(user_id, city_id)
    User.find_by(first_name: user_name).friendships.friendships(:f_of_f).cities.where(name: "london").pluck(:f)
  end
end

and city:

class City < ActiveRecord::Base
  has_many :user_cities
  has_many :users, through: :user_cities
end

and user_city:

class UserCity < ActiveRecord::Base
  belongs_to :city
  belongs_to :user
end

and friendship:

class Friendship < ActiveRecord::Base
  belongs_to :user
  belongs_to :friend, class_name: 'User'
end

Error I tried solution:

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "friends_friends_of_friends_join"
LINE 1: ...ndships" "friendships_friends_of_friends_join" ON "friends_f...
                                                             ^
: SELECT  "users".* FROM "users" INNER JOIN "user_cities" ON "user_cities"."user_id" = "users"."id" INNER JOIN "cities" ON "cities"."id" = "user_cities"."city_id" INNER JOIN "friendships" ON "users"."id" = "friendships"."friend_id" INNER JOIN "friendships" "friendships_friends_of_friends_join" ON "friends_friends_of_friends_join"."id" = "friendships_friends_of_friends_join"."friend_id" WHERE "friendships"."user_id" = $1 AND "friendships_friends_of_friends_join"."user_id" = $2 AND "cities"."name" = 'London' LIMIT 1 OFFSET 0
  Rendered search/index.html.erb within layouts/application (8.4ms)
Completed 500 Internal Server Error in 27ms

ActionView::Template::Error (PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "friends_friends_of_friends_join"
LINE 1: ...ndships" "friendships_friends_of_friends_join" ON "friends_f...
                                                             ^
: SELECT  "users".* FROM "users" INNER JOIN "user_cities" ON "user_cities"."user_id" = "users"."id" INNER JOIN "cities" ON "cities"."id" = "user_cities"."city_id" INNER JOIN "friendships" ON "users"."id" = "friendships"."friend_id" INNER JOIN "friendships" "friendships_friends_of_friends_join" ON "friends_friends_of_friends_join"."id" = "friendships_friends_of_friends_join"."friend_id" WHERE "friendships"."user_id" = $1 AND "friendships_friends_of_friends_join"."user_id" = $2 AND "cities"."name" = 'London' LIMIT 1 OFFSET 0):

Upvotes: 0

Views: 251

Answers (1)

Brian Underwood
Brian Underwood

Reputation: 10856

ActiveRecord isn't the neo4j gem, and you're running into one of the big reasons where ActiveRecord is harder.

I think this is about the most efficient you'll get without writing SQL:

# Getting a query for multiple friends, even if we're only expecting one so that we can use `includes` method
friend_user_ids = User.where(first_name: user_name).includes(:friendships).map(&:friendships).flatten.map(&:friend_id)

fof_user_ids = Friendship.find(user_id: friend_user_ids).pluck(:friend_id)

city = City.find(name: 'london')
result = User.find(UserCity.where(user_id: fof_user_ids).where(city_id: city.id).pluck(:user_id))

Alternatively you could do something like this:

city = City.find(name: 'london')   
result_ids = User.where(first_name: user_name)
  .joins('LEFT JOIN friendships ON users.id=friendships.user_id LEFT JOIN friendships AS friendships2 ON friendships.friend_id=friendships2.user_id LEFT JOIN users_cities ON friendships2.friend_id=users_cities.user_id')
  .where('users_cities.city_id = ?', city.id)
  .pluck('friendships2.friend_id')

result = User.find(result_ids)

Upvotes: 1

Related Questions