Developing CI Based Forum - WIP

[eluser]XMadMax[/eluser]
Hi srpurdy, seems that you are making a lot of joins whith forum_post, users and profile fields, with same where conditions.

Instead of making one big sql, why not retrieve forum_post data after first sql data is retrieved ?

If you make a select from 10 records, only this 10 records must to be completed with user/profile/count...

Example:

Query1 => result

Code:

foreach($result as $key => $val)
{
          QUERY User & profile data
          QUERY Totals and last post
}

Sometimes make 11 queries is better than only a big one. Think that mysql will cache this small queries, and many of those will be served from cache.

[eluser]srpurdy[/eluser]
Yeah this is what I'm thinking to split it up to 2 queries or more. I don't think it's really needed to completely split it up. The Sub Queries have very little data in there, so Would probably work well as just 2. instead of 1.

[eluser]XMadMax[/eluser]
Hi,

You can do 12 subselects in only one, take a look at : http://stackoverflow.com/questions/56862...l-subquery

Xavier

[eluser]srpurdy[/eluser]
Ah, learn something new everyday. I didn't know you could do that! Thanks that will probably help

Just not sure how that can be done with active record though.

[eluser]Altazar[/eluser]
Hi, could you say when will you publish your forum script? :roll:
I need it for my CI project which uses HMVC structure and IonAuth.
I think it would be nice (and easy) to integrate it with my website, though it's not finished yet, just to see how it looks. I would update it when you finish the admin part. No problem to pay for it (now or later).

Today I've found xenforo.com which has a solution for integration with CI (1.7), and I think about buying it, but I like your forum more. And probably xenforo wouldn't work with HMVC.

[eluser]Altazar[/eluser]
I hope this forum is not dead.
Meanwhile I'm trying to integrate Vanilla and Bonfire.

"In the 5 years I have been active in the CodeIgniter community I have seen at least 10 projects start doing this and all wane into non-existence or inactivity within a few months. - Phil Sturgeon"

[eluser]srpurdy[/eluser]
Hi,

No it's not dead, It's actually pretty likely it will be getting a lot of updates over the next 2 months. As I may have another project that needs forum software. So getting things more complete will be important. This was started as a project to do in my spare time. So that's why it's been a little slow.

Thanks,
Shawn

[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.

[eluser]srpurdy[/eluser]
Yeah I'm going to re-write this lol.

2) not sure what your saying? if I copy paste what you have I get "The URI you submitted has disallowed characters." if I take out the ++ I get a 404 error. Whats the reason to do an extra query? It's going to get the information the same way eitherway. So I'm not sure why this would make any difference.

For example I have to query to find out the forum exists. So whats to stop someone from entering this text anyway. That query is still going to run. Or are you suggesting this based on extra load on the database since it's extracting more information on the second query?

I'm using XSS clean on all fields, and cleaning any html tags out on posts. So no worries there.

[eluser]Aken[/eluser]
It was an example -- I have no idea how your routes or other logic is set up to prevent messing with the URI, but that's my point. Never trust anything that the user can manipulate, and that includes the URI segments (which, btw, I do not believe are covered by XSS clean in any way, if I remember correctly). Also, remember that just because it's protected from shenanigans doesn't mean that it's the proper format you need. If you're expecting a number, and I write "hispurdy", it'll pass XSS stuff all day long, but could throw errors for not being an integer as expected. That's what you want to prevent with data checks / sanitization.

A major benefit of verifying the structure of your URI segment is that, if it is incorrect, you can throw an error without needing to run a query at all. That's obviously way better for performance.

Once it's in the proper format (letters, numbers, dashes, whatever you're allowing), then you can run your query. However, I think it's a good idea to run a single query first, that pulls your basic info for that particular section (its details, permissions, etc.). You don't need to tie that in with another huge query for the threads and posts info, and again, if anything goes wrong, you won't need to run that query anyway (this is tied with #1).