CODEWITHSUNDEEP
mysqlsqlrubyruby-on-rails-3activerecord
oliverwillder
oliverwillder

Reputation: 165

SQL query to select a User's friends via an associated model

My question is: for a given User, what SQL command could be used to select all associated initiators/initiatees for which the associated Relation is confirmed and of a given type?

The Ruby is as follows:

class User < ActiveRecord::Base
  has_many :relations
end

class Relation < ActiveRecord::Base
  belongs_to :initiator, class_name: "User"
  belongs_to :initiatee, class_name: "User"
end

class CreateRelations < ActiveRecord::Migration
  def change
    create_table    :relations do |t|
      t.references  :initiator
      t.references  :initiatee
      t.boolean     :is_confirmed, default: false
      t.integer     :type
    end
  end
end

Upvotes: 0

Views: 82

Answers (1)

Kristj&#225;n
Kristj&#225;n

Reputation: 18813

You're going to run into trouble because Rails expects type to be used for Single Table Inheritance. You also need to tell Rails that the ID on relations is not user_id, which will be the default for has_many. Since you have two directions of relationship, you're going to need to declare both.

  has_many :outgoing_relations, class_name: 'Relation', foreign_key: 'initiator_id'
  has_many :incoming_relations, class_name: 'Relation', foreign_key: 'initiatee_id'

From there, the simplest thing to do is write a method that aggregates the other users:

def friends(params = {})
  outgoing_relations.where(params).includes(:initiatee).map(&:initiatee) +
  incoming_relations.where(params).includes(:initiator).map(&:initiator)
end

> User.first.friends(is_confirmed: true, kind: 0)
=> [#<User id: 2, created_at: "2015-08-28 15:11:12", updated_at: "2015-08-28 15:11:12">]

In straight SQL, you can easily UNION a couple queries to pull the other user IDs that you want, then do what you like with them.

SELECT initiatee_id AS id
FROM relations
WHERE initiator_id = 2
  AND kind = 0
  AND is_confirmed
UNION
SELECT initiator_id AS id
FROM relations
WHERE initiatee_id = 2
  AND kind = 0
  AND is_confirmed
;

 id
----
  1
  3

This is the data I'm running on:

SELECT * FROM users;
 id |         created_at         |         updated_at
----+----------------------------+----------------------------
  1 | 2015-08-28 15:11:10.631187 | 2015-08-28 15:11:10.631187
  2 | 2015-08-28 15:11:12.911575 | 2015-08-28 15:11:12.911575
  3 | 2015-08-28 15:14:27.762946 | 2015-08-28 15:14:27.762946

SELECT * FROM relations;
 id | initiator_id | initiatee_id | is_confirmed | kind
----+--------------+--------------+--------------+------
  1 |            1 |            2 | t            |    0
  2 |            3 |            2 | t            |    0

Upvotes: 1

Related Questions