Class method problem with bidirectional self-referential associations

I'm trying to create an app where users ('current_user') rate the compatibility between two other users ('user1' and 'user2'). They can rate compatibility either positively or negatively: rating two users "compatible" creates two resources of the same class (a 'positive_connection' and an 'inverse_positive_connection' - for bidirectionality), and rating them "incompatible" also creates two resources('negative_connection' and 'inverse_negative_connection'). So there are models for positive_connection, negative_connection and user.

Each rating resource belongs_to the user who created it, but also to the users connected by it. It's important to have both positive and negative ratings.

This is my problem: on every user (@user) page I want to display separate lists of:

1. users that are overall_positively_connected_to(@user) (i.e. positive_connections.count > negative_ratings.count), and

2. users that are overall_negatively_connected_to(@user) (i.e. negative_connections.count > positive_ratings.count).

What I can't seem to do is write a class method that pulls out only those users who are net-rated "compatible" or "incompatible"

From reading Michael Hartl's rails tutorial (I'm completely new to all this), I think I need to write something like this in the User model:

class User < ActiveRecord::Base


def self.that_are_compatible_with

     User.overall_positively_connected_to(user)
end
.
.
.

EDIT

Starting with absolutely no knowledge of SQL queries at all, I wrote these two class methods 开发者_如何学Gofor finding users that are negatively and positively connected to @user (respectively):

.
.
.
  def self.with_neg_connections_to(user)
   joins(:negative_connections).
   where(:negative_connections => {:user_a_id => user})
  end

  def self.with_pos_connections_to(user)
   joins(:positive_connections).
   where(:positive_connections => {:user_a_id => user})
  end

But that's not much help. What I need is a method that gets the users overall_positively_connected_to(user). I presume the method would involve two joins, and go something like this:

  def self.overall_positively_connected_to(user)
    joins(:positive_connections).joins(:negative_connections).
    where((:negative_connections => {:user_a_id => user}).count > (:positive_connections => {:user_a_id => user}).count)
  end

But here I get completely stuck: it's obviously not right. I can't find other examples like it anywhere...

Any help whatsoever on this would be great as I have no clue when it comes to SQL queries. Let me know if more code needed. Thanks in advance!

Having struggled with this for a few days, I decided that the best way of going about solving the problem is probably to change the models round - either by adding an additional "connection" model and having users vote positively or negatively on each connection, or by slimming down to a single "connection" model where the positive or negative character of each connection is marked by a +1/-1.

A couple of alternatives can be found in answers to this question.

If I understand the problem correctly, one of things that makes this all complicated is that the target user could be in either User1 or User2. That is, if we are after all the connections for the User with ID=4 then, we could have User1 = 4 or User2 = 4... is that right?

If so, then assuming that your positive_connections and negative_connections models/tables have fields like:

• ID: the primary key
• ReportingUser: the user making the connection
• UserA: the first user to connect
• UserB: the second user to connect

Then the following (rather messy) SQL would give you a summary of both the positive and negative connections for each user:

set @user = 2;

SELECT DISTINCT
    @user TargetUser,
    u.ID as OtherUser,
    COALESCE(qryPositive.PositiveConnections, 0) as PositiveConnections,
    COALESCE(qryNegative.NegativeConnections, 0) as NegativeConnections,
    case
      when COALESCE(qryPositive.PositiveConnections, 0) > COALESCE(qryNegative.NegativeConnections, 0)
      then 'positive'
      when COALESCE(qryPositive.PositiveConnections, 0) < COALESCE(qryNegative.NegativeConnections, 0)
      then 'negative'
      else 'neutral'
    end as Status
FROM
    users u
LEFT JOIN
    (
        -- The number of positive connections between the @user and each other user
        SELECT
            @user TargetUser,
            a.OtherUser,
            COUNT(*) as PositiveConnections
        FROM
        (
            -- The positive_connections rows with our @user in it
            -- and the other user that the connection associates them with
            select -- pc.ID as pcID, pc.UserA, pc.UserB,
            case
              when pc.UserA = @user then pc.UserB
              else pc.UserA
            end as OtherUser
            from positive_connections pc
            where
            (
              -- Check both columns
              (pc.UserA = @user)
              or
              (pc.UserB = @user)
            )
        ) a
        GROUP BY
            OtherUser
    ) qryPositive
    on qryPositive.OtherUser = u.ID

LEFT JOIN
    (
        -- The number of negative connections between the @user and each other user
        SELECT
            @user TargetUser,
            b.OtherUser,
            COUNT(*) as NegativeConnections
        FROM
        (
            -- The negative_connections rows with our @user in it
            -- and the other user that the connection associates them with
            select -- pc.ID as pcID, pc.UserA, pc.UserB,
            case
              when pc.UserA = @user then pc.UserB
              else pc.UserA
            end as OtherUser
            from negative_connections pc
            where
            (
              -- Check both columns
              (pc.UserA = @user)
              or
              (pc.UserB = @user)
            )
        ) b
        GROUP BY
            OtherUser
    ) qryNegative
    on qryNegative.OtherUser = u.ID

WHERE
    u.ID <> @user

That takes care of the SQL (I can post the sample data I used if you like). Now within rails, it would definitely be a good idea to separate this out into separate class methods in your model, as you have started doing.

As far as building the queries using rails, this page is a pretty good resource. http://guides.rubyonrails.org/active_record_querying.html

Is this the kind of thing you are looking for? If so, then we can work on building the active record queries. (I have a feeling I may have misunderstood the problem though...)