如何从SQL中的相邻表中选择另一个表中列出的id？

Question

A little more background on my issue. I have a "Followers" sidebar on my site where users can follow other users. The problem is that currently the users that appear in this sidebar can also include users they already follow. I would like to modify the function so this no longer happens. Here is the model function that I currently use...

public function get_oldest($limit = 10, $user_id)
{
  $this->db
      ->select($this->db->dbprefix('profiles').'.*, g.description as group_name, users.*')
      ->join('groups g', 'g.id = users.group_id')
      ->join('profiles', 'profiles.user_id = users.id', 'left')
      ->group_by('users.id');
  $this->db->order_by('users.created_on', 'ASC');
  $this->db->limit($limit);
  $results = $this->db->get('users')->result_array();

  return $results;
}

Here is the controller function...

function get_oldest() {
  $this->load->model('user_account/user_account_m');

  $limit = $this->attribute('limit');

  $user_id = $this->current_user->id;

  $users = $this->user_account_m->get_oldest($limit, $user_id);
  return $users;
}

The "Follower"(default_follow) table in the sql database has the following structure...

id = the id of the follow event
follower_id = the user_id of the person who is doing the following
followed_id = the user_id of the person being followed

The "Profile"(default_profiles) table in the sql database has the following structure...

user_id
display_name
first_name
last_name

I know I need to use the current user's id in some way to get this to work, I'm just having a hard time figuring out how. Thanks in advance for your help people.

Answer 1

The functionality you're looking for is called a subquery.

In SQL, your CodeIgniter ActiveRecord query roughly translates as:

SELECT profiles.*, g.description as group_name, users.*
INNER JOIN groups g ON g.id = users.group_id
LEFT JOIN profiles ON profiles.user_id = users.id
GROUP BY users.id
ORDER BY users.created_on;

To filter out existing followers, We'll add a WHERE clause to that that uses the IN keyword and a subquery:

SELECT profiles.*, g.description as group_name, users.*
INNER JOIN groups g ON g.id = users.group_id
LEFT JOIN profiles ON profiles.user_id = users.id
WHERE users.id NOT IN (SELECT follower_id FROM default_follow WHERE followed_id = ?)
GROUP BY users.id
ORDER BY users.created_on;

The ? indicates a query parameter, which in your case would be the ID of the logged-in user.

Converting back to CodeIgniter, you have a couple options:

1) Write the NOT IN clause directly into a call to CodeIgniter's "where" function, concatenating the user ID.

$this->db
        ->select($this->db->dbprefix('profiles').'.*, g.description as group_name, users.*')
        ->join('groups g', 'g.id = users.group_id')
        ->join('profiles', 'profiles.user_id = users.id', 'left')
        ->where('users.id NOT IN (SELECT follower_id FROM default_follow WHERE followed_id = '.$user_id.')', NULL, FALSE)
        ->group_by('users.id');

2) Break the subquery out into a separate query and use the results in the second query:

$this->subquery->select('follower_id')
               ->where('followed_id', $user_id);
$followers = $this->subquery->get('default_follow')->result_array();
$this->db
        ->select($this->db->dbprefix('profiles').'.*, g.description as group_name, users.*')
        ->join('groups g', 'g.id = users.group_id')
        ->join('profiles', 'profiles.user_id = users.id', 'left')
        ->where_not_in('users.id', $followers)
        ->group_by('users.id');

Option 1 is nice because it lets the database do all the work, but depending on where $user_id comes from, you might be opening yourself up to a SQL injection attack. You'd want to sanitize that input ahead of time.

Option 2 is safe from SQL injection, but forces PHP to do some of the work. It won't be as fast, especially for users with lots of followers. Some databases have a limit to the number of elements you can include in an explicit IN clause (notably, Oracle limits you to 1000), but it looks like you're using mySQL, which has no such limit.

I wish there was an option three, but CodeIgniter's ActiveRecord functionality doesn't offer native support for subqueries (yet). Given the above options, I'd take option 1 and make sure to protect against SQL injection yourself.

Here's a decent reference for querying with CodeIgniter ActiveRecord:

http://ellislab.com/codeigniter/user-guide/database/active_record.html