StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The functionality you're looking for is called a subquery.</p> <p>In SQL, your CodeIgniter ActiveRecord query roughly translates as:</p> <pre><code>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; </code></pre> <p>To filter out existing followers, We'll add a WHERE clause to that that uses the IN keyword and a subquery:</p> <pre><code>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; </code></pre> <p>The ? indicates a query parameter, which in your case would be the ID of the logged-in user.</p> <p>Converting back to CodeIgniter, you have a couple options:</p> <p>1) Write the NOT IN clause directly into a call to CodeIgniter's "where" function, concatenating the user ID.</p> <pre><code>$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'); </code></pre> <p>2) Break the subquery out into a separate query and use the results in the second query:</p> <pre><code>$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'); </code></pre> <p>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.</p> <p>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.</p> <p>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.</p> <p>Here's a decent reference for querying with CodeIgniter ActiveRecord:</p> <p><a href="http://ellislab.com/codeigniter/user-guide/database/active_record.html" rel="nofollow">http://ellislab.com/codeigniter/user-guide/database/active_record.html</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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