Answer by Mitchell McKenna for How to write this statement using PHP & CodeIgniter

September 28 2010, 3:14am

There are actually a couple places we can optimize this. 1) In the initial query do a JOIN with the user table so we don't have to query again multiple times just for each name:

$readers = $this->db->select('id', 'first_name', 'last_name')->from('books_users')->join('users', 'books_users.id =users.id', 'left')->where('book_id' => $isbn);

2) Additionally, we can avoid iterating over the entire (potentially large) result set just to see if you read it, by doing a separate query:

$you_read = $this->db->get_where('books_users', array('book_id' => $isbn, 'user_id' => $user->id));