[eluser]Aken[/eluser]
[quote author="srpurdy" date="1356876689"]This is the one below bit of a monster
Code:
function get_posts_by_forum($limit,$offset)
{
$uri = $this->uri->segment(4);
$posts_by_forum = $this->db
->where('forums.forum_url_name', $this->uri->segment(3))
->where('forum_topics.forum_id = forums.forum_id')
->where('forum_permissions.forum_id = forums.forum_id')
//->where('forum_posts.is_topic', 'Y')
->select('
forum_permissions.allow_html,
forum_permissions.allow_bbcode,
forum_permissions.allow_topic,
forum_permissions.allow_reply,
forum_permissions.allow_edit,
forum_permissions.allow_delete,
forum_permissions.allow_attachments,
forum_permissions.allow_sticky,
forum_topics.post_title,
forum_topics.topic_id,
forum_topics.post_text,
forum_topics.sticky,
forum_topics.topic_views,
forums.public,
forums.forum_id,
(select users.first_name from forum_posts,users,profile_fields where forum_topics.topic_id = forum_posts.topic_id AND forum_posts.approved = "Y" AND forum_posts.user_id = users.id AND users.id = profile_fields.user_id ORDER BY forum_posts.post_date ASC LIMIT 1) as first_name,
(select users.last_name from forum_posts,users,profile_fields where forum_topics.topic_id = forum_posts.topic_id AND forum_posts.approved = "Y" AND forum_posts.user_id = users.id AND users.id = profile_fields.user_id ORDER BY forum_posts.post_date ASC LIMIT 1) as last_name,
(select profile_fields.display_name from forum_posts,users,profile_fields where forum_topics.topic_id = forum_posts.topic_id AND forum_posts.approved = "Y" AND forum_posts.user_id = users.id AND users.id = profile_fields.user_id ORDER BY forum_posts.post_date ASC LIMIT 1) as display_name,
(select profile_fields.nickname from forum_posts,users,profile_fields where forum_topics.topic_id = forum_posts.topic_id AND forum_posts.approved = "Y" AND forum_posts.user_id = users.id AND users.id = profile_fields.user_id ORDER BY forum_posts.post_date ASC LIMIT 1) as nickname,
(select count(forum_posts.topic_id) from forum_posts where forum_posts.topic_id = forum_topics.topic_id) as total_posts_count,
(select count(forum_is_post.topic_id) from forum_is_post where forum_is_post.topic_id = forum_topics.topic_id) as total_posts,
(select forum_posts.post_id from forum_posts where forum_posts.topic_id = forum_topics.topic_id ORDER BY forum_posts.post_date DESC LIMIT 1) as post_date_order,
(select users.first_name from forum_posts,users,profile_fields where forum_topics.topic_id = forum_posts.topic_id AND forum_posts.approved = "Y" AND forum_posts.user_id = users.id AND users.id = profile_fields.user_id ORDER BY forum_posts.post_date DESC LIMIT 1) as last_first_name,
(select users.last_name from forum_posts,users,profile_fields where forum_topics.topic_id = forum_posts.topic_id AND forum_posts.approved = "Y" AND forum_posts.user_id = users.id AND users.id = profile_fields.user_id ORDER BY forum_posts.post_date DESC LIMIT 1) as last_last_name,
(select profile_fields.display_name from forum_posts,users,profile_fields where forum_topics.topic_id = forum_posts.topic_id AND forum_posts.approved = "Y" AND forum_posts.user_id = users.id AND users.id = profile_fields.user_id ORDER BY forum_posts.post_date DESC LIMIT 1) as last_display_name,
(select profile_fields.nickname from forum_posts,users,profile_fields where forum_topics.topic_id = forum_posts.topic_id AND forum_posts.approved = "Y" AND forum_posts.user_id = users.id AND users.id = profile_fields.user_id ORDER BY forum_posts.post_date DESC LIMIT 1) as last_nickname,
(select forum_posts.topic_id from forum_posts,users,profile_fields where forum_topics.topic_id = forum_posts.topic_id AND forum_posts.approved = "Y" AND forum_posts.user_id = users.id AND users.id = profile_fields.user_id ORDER BY forum_posts.post_date DESC LIMIT 1) as last_topic_id,
(select users.id from forum_posts,users,profile_fields where forum_topics.topic_id = forum_posts.topic_id AND forum_posts.approved = "Y" AND forum_posts.user_id = users.id AND users.id = profile_fields.user_id ORDER BY forum_posts.post_date DESC LIMIT 1) as last_user_id,
(select forum_posts.post_date from forum_posts,users,profile_fields where forum_topics.topic_id = forum_posts.topic_id AND forum_posts.approved = "Y" AND forum_posts.user_id = users.id AND users.id = profile_fields.user_id ORDER BY forum_posts.post_date DESC LIMIT 1) as last_post_date,
')
->from('forums,forum_topics,forum_permissions')
->order_by('forum_topics.sticky', 'desc')
->order_by('post_date_order', 'desc')
->limit($limit,$offset)
//->join('forum_posts', 'forum_posts.topic_id = forum_topics.topic_id')
//->order_by('post_date_order', 'desc')
->get();
return $posts_by_forum;
}
[/quote]
Uh, yeah. Fix that.
1) Good tip to remember: don't repeat data that doesn't change. Your forum permissions and stuff will be the same for every single post row, so it should be retrieved by itself.
2) You're using $this->uri->segment() right in your WHERE statement.
Bad! You should use this to figure out if the forum actually exists first, and always make sure any user-manipulated input is checked and sanitized properly. I know the DB does that to prevent SQL injection, but what happens if that segment is "jkdJFDAJ98401-fa_++.htm"?
3) It's been touched on, but you're doing a ton of subqueries on the same data with the same conditions. I'm guessing you probably don't need any subqueries at all, and can use standard joins to match the info you need.
