hows my code..first sort of app, couple of questions

[eluser]Met[/eluser]
hi

here is a function in one of my models for a blog, (inventive).

Code:

function get_posts_in_category($category_slug=NULL)
    {
        if($category_slug==NULL)
        {
            return false;
        }
        
        $this->db->where('cat_slug', $category_slug);
        $query = $this->db->get('category');
        
        if($query->num_rows()!=1)
        {
            return false;
        }
        
        $row = $query->row();
        $cat_id = $row->cat_id;
        
        $this->db->where('category_id', (int)$cat_id);
        $post_ids = $this->db->get('post_categories');
        
        foreach($post_ids->result() as $post)
        {
            $this->db->or_where('id', (int)$post->post_id);
        }
        
        $this->db->select('url_title');
        $posts=$this->db->get('blog_posts');
        
        return $posts->result();
                
    }

I have 3 tables - posts, categories, and "post_categories".

post_categories contains a record for every post and every category - so:

post_id | category_id
1 1
1 2

means "post 1" has categories "1 and 2".

my categories table has a "slug" so I can use text in my URLs rather than an integer.

My logic is:

* pass the category slug to the function "get_posts_in_category".

* Select the category ID from the database where it matches the category_slug

* Select any post_ids that appear in "post_categories" that matches the category_id of the slug.

* select any posts that match these post_ids

* return the required data.

* This seems like quite a lot of work and code for a seemingly simple task.

Is there a fault in my logic? Or could this be better achieved with say a JOIN?


and another question - I've had to use:

(int)$id in my WHERE selection, as CI wraps the value in quotes, so when trying to select integers, CI is actually trying to select a string, so it fails.

is this the correct way of doing it? or should my tables just use strings rather than integers.

thanks

[eluser]cideveloper[/eluser]
I would use a join

Code:

$query = $this->db->query("select * from blog_posts b inner join post_categories p on b.id=p.post_id inner join categories c on p.category_id=c.cat_id where c.cat_slug='" . $category_slug . "'");

and if you are going to use active record to do your joins and need to use an int, setting the optional third parameter of the where() to false "will not try to protect your field or table names with backticks"

[eluser]Eric Barnes[/eluser]
Looks pretty good to me. One change I would make to allow the method to be more flexible is pass either an id or the slug.

Code:

function get_posts_in_category($slug = NULL)
{
    if ($category_slug == NULL)
    {
        return FALSE;
    }

    if ( ! is_numeric($slug))
    {
        $this->db->where('cat_slug', $slug);
        $query = $this->db->get('category');

        if ($query->num_rows() != 1)
        {
            return FALSE;
        }

        $row = $query->row();
        $cat_id = $row->cat_id;
    }
    else
    {
        $cat_id = $slug;
    }

    $this->db->where('category_id', (int) $cat_id);
    $post_ids = $this->db->get('post_categories');

    foreach ($post_ids->result() as $post)
    {
        $this->db->or_where('id', (int) $post->post_id);
    }

    $this->db->select('url_title');
    $posts=$this->db->get('blog_posts');

    return $posts->result();
}

and kudos for not doing the blog_posts query inside the loop. That could be a major slow down.

[eluser]Met[/eluser]
[quote author="progr@mmer" date="1294171391"]I would use a join

Code:

$query = $this->db->query("select * from blog_posts b inner join post_categories p on b.id=p.post_id inner join categories c on p.category_id=c.cat_id where c.cat_slug='" . $category_slug . "'");

and if you are going to use active record to do your joins and need to use an int, setting the optional third parameter of the where() to false "will not try to protect your field or table names with backticks"[/quote]

Ah, thanks for the FALSE parameter - no idea how I missed that.

Yup a join certainly looks cleaner now that you mention it - thanks.

[quote author="Eric Barnes" date="1294171822"]Looks pretty good to me. One change I would make to allow the method to be more flexible is pass either an id or the slug.

[snip]

and kudos for not doing the blog_posts query inside the loop. That could be a major slow down.[/quote]

Good idea, thank you also.