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

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

One way to do set difference in sql is the EXCEPT keyword. I put up a toy rendering on sqlfiddle here: http://sqlfiddle.com/#!6/2308a/6

SELECT distinct user_id
FROM users
EXCEPT 
SELECT followed_id
FROM follow
WHERE follower_id = <idhere>

The top sql will grab all user_id's in the system. The bottom will grab all of the people the current user is following, and the EXCEPT keyword will apply set difference to give you the all users that the current user has not followed yet.