从Codeigniter查询中获取正确的数据？

Question

I'm currently trying to get an array from my database using codeigniter active record which something I do often but I'm currently having a problem getting the correct data.

There is 4 records which should be getting returned to my controller from my model. At the moment all records from the table are being returned.

I'm using a foreach loop within my query as the queries where/or_where clause can be many values. This is where the problem lies as I think it bypasses the foreach and get's all records without looking at the where clause.

My question is regarding what seems to be the problem with the 2nd example?

WORKING

The following code gets the desired 4 records which should be returned:

public function get_most_relevant($user){

    $categories = $this->get_users_most_common_categories($user);

    $this->db->order_by('posts.date_created','DESC');
    $this->db->limit(25);
    $this->db->select('*');
    $this->db->from('posts');
    $this->db->select('posts.image_name as post_image');
    $this->db->select('posts.random_string as post_string');
    $this->db->select('posts.date_created as post_creation');
    $this->db->join('users', 'posts.user_string = users.random_string','left'); 
    $this->db->where('category_string', 'cCU8oEQHYLWP');
    $this->db->or_where('category_string', '8RfRDWrG5QB7');

    $posts = $this->db->get();

    return $posts;

}

NOT WORKING

The following code gets all records from the table:

It appears to be bypassing the foreach and going straight to the get().

public function get_most_relevant($user){

    $categories = $this->get_users_most_common_categories($user);
    $i = 0;

    $this->db->order_by('posts.date_created','DESC');
    $this->db->limit(25);
    $this->db->select('*');
    $this->db->from('posts');
    $this->db->select('posts.image_name as post_image');
    $this->db->select('posts.random_string as post_string');
    $this->db->select('posts.date_created as post_creation');
    $this->db->join('users', 'posts.user_string = users.random_string','left'); 

    foreach($categories->result_array() as $category){
        if($i == 0){
            $this->db->where( array('category_string' => $category['category_string'], 'published' => '1') );
        }else{
            $this->db->or_where( array('category_string' => $category['category_string'], 'published' => '1') );
        }   
        $i++;
    }

    $posts = $this->db->get();

    return $posts;

}

$this->get_users_most_common_categories($user) code

public function get_users_most_common_categories($user){

    $categories = $this->db->query("SELECT category_string, COUNT(category_string) AS category_occurence FROM views WHERE user_string = '".$user."' OR ip_address = '".$user."' GROUP BY category_string ORDER BY category_occurence DESC");

    return $categories;

}

$this->get_users_most_common_categories($user) return

Array
(
[0] => Array
    (
        [category_string] => cCU8oEQHYLWP
        [category_occurence] => 3
    )

[1] => Array
    (
        [category_string] => 8RfRDWrG5QB7
        [category_occurence] => 1
    )

)

QUERY OUTPUT

SELECT *, posts.image_name as post_image, posts.random_string as post_string, posts.date_created as post_creation FROM (posts) LEFT JOIN users ON posts.user_string = users.random_string WHERE category_string = 'cCU8oEQHYLWP' AND published = '1' OR category_string = '8RfRDWrG5QB7' OR published = '1' ORDER BY posts.date_created DESC LIMIT 25

Thanks for your help in advance, it is much appreciated.

Answer 1

It may be a bug. You could try writing that part of the query as a string which you can pass to where and or_where:

foreach($categories->result_array() as $category){
    $query = "(category_string = ".$category['category_string']." AND published = '1')";

    if($i == 0){
        $this->db->where($query);
    }else{
        $this->db->or_where($query);
    }   
    $i++;
} 

http://ellislab.com/codeigniter/user-guide/database/… - see where part 4 Custom String