No such column users.user_id, trying to implement a friendship relationship for users

Question

I solved the problem, see my answer below for the fully functional relationships, models, and schema.

In testing my friendship model, I'm running into an error:

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: users.user_id: SELECT 1 AS one FROM "users" INNER JOIN "friendships" ON "users"."id" = "friendships"."friend_id" WHERE "friendships"."user_id" = ? AND "users"."user_id" IN (SELECT "friendships"."id" FROM "friendships" WHERE "friendships"."status" = ?) AND "users"."id" = ? LIMIT 1

while trying to access a users pending_friends in an rspec test after creating a friendship. I have the following code that defines a friendship:

class Friendship < ActiveRecord::Base
  belongs_to :user
  belongs_to :friend, :class_name => "User"

  enum status: [:pending, :accepted]
  scope :accepted, -> { where(status: :accepted) }
  scope :pending, -> { where(status: :pending) }
  validates :user, uniqueness: { scope: :friend, message: "This user is already your friend" }

  def decline
    self.destroy
  end

  def accept
    self.update status: :accepted
  end

end

The code below defines what pending_friends should be in my user model:

has_many :friendships
has_many :pending_friends, -> { where(friendships: Friendship.pending) }, 
                      through: :friendships, class_name: "User", source: :friend

I'm unsure why it's trying to access users.user_id... If anyone knows how I can fix this any help would be greatly appreciated, I've been digging into how joins work but I can't seem to find anything helpful.

FYI: This is how I'm creating the friendship:

friendship1 = Friendship.build(user: current_user, friend: other_user)
friendship2 = Friendship.build(user: other_user, friend: current_user)

(and then a save and error checking)

This is my friendship migration:

class CreateFriendships < ActiveRecord::Migration
  def change
    create_table :friendships do |t|
      t.integer :status, :null => false, :default => 0
      t.integer :user_id
      t.integer :friend_id

      t.timestamps null: false
      end
   end
end

What is in Friendship schema? I mean how do you define the foreign keys? — Hieu Pham, Feb 23 '16 at 17:59
@HieuPham Unsure what you mean, is there something I forgot to do? I'm slightly new to this but if you mean the migration I used, I've just posted the code for it at the bottom of my question. This is all I have done so far — Riptyde4, Feb 23 '16 at 18:03
You might want to have a look at http://stackoverflow.com/questions/2168442/many-to-many-relationship-with-the-same-model-in-rails. Doing a join table like this is tricky since a user can be in either the `user_id` or `friend_id` column. — max, Feb 23 '16 at 18:18
Also take a look at the docs for [`ActiveRecord::Enum`](http://edgeapi.rubyonrails.org/classes/ActiveRecord/Enum.html). It will create `accepted` and `pending` scopes for you. Also your where clause should look like this: `where(friendships: { status: :pending })` — max, Feb 23 '16 at 18:23
@max I got it to work with `where(friendships: { status: "pending" })`. The rspec test showed that it was represented as a string and not a symbol even though I'm using an enum of symbols — Riptyde4, Feb 23 '16 at 20:33
Thats strange, when you do `where(friendships: { status: :pending })` it should look at the enum declaration and create a where clause that looks like `WHERE friendships.status = 0`. Its also how its documented, the only reason I can think of why it would not work that way is if you used a string type column instead of an integer for `friendship.status`. — max, Feb 23 '16 at 20:41
Have you tried using the "inverse_of" option for the 'friend' association in Friendship? That option helps ActiveRecord break ties when picking associated tables like this. belongs_to :friend, class_name: "User", inverse_of: :friendships — AndyV, Feb 23 '16 at 21:01
@AndyV First time hearing about inverse_of... Where exactly are you saying I should use that? — Riptyde4, Feb 24 '16 at 00:46
Never mind all, found a solution -> posted as an answer below. @max For some reason, the status is initially saved as "pending" but then when explicitly set to :accepted, the check for "accepted" fails, so I fell back to simply checking for 0 or 1 in my queries and it's working fine — Riptyde4, Feb 24 '16 at 01:26

score 1 · Answer 1 · answered Feb 23 '16 at 22:19

Lets dive into the issue. Note: indenting your SQL on keywords makes it easier to spot what's going on:

SELECT  1 AS one
FROM "users"
INNER JOIN "friendships" ON "users"."id" = "friendships"."friend_id"
WHERE "friendships"."user_id" = ? AND 
      "users"."user_id" IN (
         SELECT "friendships"."id"
         FROM "friendships"
         WHERE "friendships"."status" = ?) AND 
      "users"."id" = ?
LIMIT 1

So... the problem is clearly the phrase: "users"."user_id" IN Users don't have a user_id, users have an id

Friendships have a user_id. So... something is causing your SQL to think you should be fetching out users by user_id, when it should be fetching out friendships that way instead.

has_many :pending_friends, -> { where(friendships: Friendship.pending) }, 
                  through: :friendships, class_name: "User", source: :friend

is obviously the code that's generating this right?

How about splitting the pending part up from the pending-friends part and see if they work separately:

has_many :friendships
has_many :pending_friendships, -> { friendships.merge(Friendship.pending) }

see if that part works? and if not get it working (it's simpler) (Note: I have not tested this code, you might need to do that part differently... try things)

Then you can just add:

has_many :pending_friends, through: :pending_friendships, class_name: "User", source: :friend

`has_many :pending_friendships, -> { friendships.merge(Friendship.pending) }` gives me the error : `Uninitialized constant User::PendingFriendship` in my spec when trying to use it — Riptyde4, Feb 24 '16 at 00:53
yes - you will have to add things like class name and similar to make it actually work :) — Taryn East, Feb 24 '16 at 04:32

Riptyde4 · Answer 2 · 2016-02-24T05:19:01.947

This is what did it for me, thanks to max for the tip on how to format the procs

Friendship migration

class CreateFriendships < ActiveRecord::Migration
  def change
    create_table :friendships do |t|
      t.integer :status, :null => false, :default => 0
      t.integer :user_id
      t.integer :friend_id

      t.timestamps null: false
    end
  end
end

Friendship relationships in the user model

has_many :friendships, :foreign_key => :user_id, dependent: :destroy
has_many :inverse_friendships, :foreign_key => :friend_id, dependent: :destroy,
                    class_name: :Friendship

has_many :pending_incoming_friendships,  
                    -> { where(friendships: { status: 0 }) },
                    :class_name => :Friendship,
                    :foreign_key => :friend_id, :dependent => :destroy

has_many :pending_outgoing_friendships,
                      -> { where(friendships: { status: 0 }) },
                    :class_name => :Friendship,
                    :foreign_key => :user_id, :dependent => :destroy

Then i just check for status: 1 to get the accepted friendships! Unsure why checking for the symbols or using the Friendship.accepted or Friendship.pending scopes are not working properly. It seems that the value is initially set to "pending", a string, but then after i call on my accept function which explicitly sets it to a symbol, it is then a symbol. I chose to use integers as there are only 2 of them and it seems safer with the apparent unpredictability.

No such column users.user_id, trying to implement a friendship relationship for users

I solved the problem, see my answer below for the fully functional relationships, models, and schema.

2 Answers2

Friendship migration

Friendship relationships in the user model