Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Below this section is my original answer; I mis-read the question and thought the OP only wanted a list of users who had RECEIVED messages from the specified user; I didn't notice the part about also users who SENT messages to the specified user as well. Here is my update. I'll leave the original answer below in case it helps.</p> <p>In this version of the answer, I would alter the join and the where clause so that the query examines either the <code>sender_id</code> or <code>recipient_id</code> column:</p> <pre><code>User.joins("INNER JOIN messages ON (messages.sender_id = users.id OR messages.recipient_id = users.id)") .where("messages.sender_id = ? OR messages.recipient_id = ?", current_user.id, current_user.id).uniq </code></pre> <p>Apologies for the misunderstanding.</p> <hr> <p><strong>ORIGINAL ANSWER:</strong></p> <p>In order to get all the users you've contacted, I am thinking the easiest approach is to do this through the normal active record query methods. First, set up the <code>has_many</code> in your <code>User</code> model like so:</p> <p>In your <code>User</code> model</p> <pre><code>has_many :messages_sent, class_name: "Message", foreign_key: :sender_id has_many :messages_received, class_name: "Message", foreign_key: :recipient_id </code></pre> <p>And then, you can simply query using the relationship like so:</p> <pre><code>User.joins(:messages_received).where(:messages=&gt;{sender_id: current_user.id}).uniq </code></pre> <p>Note: replace <code>current_user</code> with whatever you use to store the target user.</p> <p><strong>Edit</strong></p> <p>Someone asked why this is better than using the direct <code>:messages_sent</code> relationship. The advantage this approach has is that it is all done on the database level, where proper indexing can do its job. If you have a very large table, and this user has sent a large number of messages, using the active record <code>has_many</code> relationship will require you to iterate over a large collection, attempting to find unique users.</p> <p>Just to add a bit more clarification, this query should render an SQL query that looks something like this:</p> <pre><code>SELECT users.* FROM users INNER JOIN messages ON messages.recipient_id = users.id WHERE messages.sender_id = 1 </code></pre> <p>In plain English, this is basically saying to the database: "Please give me all the users who RECEIVED a message SENT by user #1".</p>
    singulars
    1. This table or related slice is empty.
    1. POLast User Contacted
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPaul Richter
    1. USPaul Richter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POLast User Contacted
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COYeah that was me! I deleted the comment because I didn't read the question correctly! Your answer is still missing a way to get users that either sent or received messages to the target user, but i'm not sure that's possible in one query without writing SQL
      singulars
      1. PO
      1. USSlicedpan
    2. CO@Slicedpan No problem, it was a fair question. Hmm I'm not quite sure what you mean (re: second part of your comment). The active record query methods are being invoked on the `User` model. Ultimately, this will generate a query that looks like this: `SELECT users.* FROM users INNER JOIN messages ON messages.recipient_id = users.id WHERE messages.sender_id = 1`. As you can see (unless I'm really missing something), you do indeed get back a list of users who received messages from the target user.
      singulars
      1. PO
      1. USPaul Richter
    3. COI think the query you need is `SELECT users.* FROM users INNER JOIN messages AS r_msg ON r_msg.recipient_id = users.id INNER JOIN messages AS s_msg ON s_msg.sender_id = users.id WHERE s_msg.recipient_id = 1 OR r_msg.recipient_id = 1;` i.e. all users who received a message from user 1 or sent a message to user 1
      singulars
      1. PO
      1. USSlicedpan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload