How to build a CodeIgniter active record query with iterated sets of AND conditions containing OR logic?

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.

does $categories->result_array() return a non-empty array? — David Normington
– David Normington, Commented Feb 26, 2014 at 13:47
@DavidNormington not sure what you mean by non-empty array but view my edits, thanks. — learn
– learn, Commented Feb 26, 2014 at 13:57
@DavidNormington It appears to be adding an OR instead of AND in between 'category_string' = '8RfRDWrG5QB7' OR 'published' = '1', could you have a look at my edit where I've added the query? — learn
– learn, Commented Feb 26, 2014 at 14:10
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: $this->db->where("(category_string = ".$category['category_string']." AND published = '1')"); ellislab.com/codeigniter/user-guide/database/… - see where part 4 Custom String. — David Normington
– David Normington, Commented Feb 26, 2014 at 14:18
@DavidNormington That's exactly what it was but I've got the query working now which has made me happy. Feel free to create an answer and I'll accept, Thanks. — learn
– learn, Commented Feb 26, 2014 at 14:29

David Normington · Accepted Answer · 2014-02-26 14:35:50Z

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

answered Feb 26, 2014 at 14:35

David Normington

8478 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Justin Paul Paño · Accepted Answer · 2014-02-26 13:58:25Z

1

One thing you can do to find out where did you go wrong in your query is to use the last_query function of the CI DB driver. It returns the last SQL string that was run. You can then execute that query into your database client and adjust it until you get the result that you want.

$posts = $this->db->get();
$sql = $this->db->last_query();
echo $sql;

answered Feb 26, 2014 at 13:58

Justin Paul Paño

9365 silver badges7 bronze badges

2 Comments

learn Over a year ago

It appears to be adding an OR instead of AND in between 'category_string' = '8RfRDWrG5QB7' OR 'published' = '1', could you have a look at my edit where I've added the query?

Justin Paul Paño Over a year ago

instead of $this->db->or_where() use $this->db->where()

mickmackusa · Accepted Answer · 2025-03-04 22:08:41Z

Your code is suffering the fallout of missing parentheses for logical grouping while building the WHERE clause inside the loop. The typical solution is to encapsulate your where() and or_where() logic with calls of group_start() and group_end().

Also, you are missing an opportunity to improve performance by making a single trip to the database.

If I am not mistaken, you can compile all of the logic into the below query which leverages a JOINed subquery built via get_compiled_select() for security/stability. This refactored script avoids the pre-fetching query as well as the need to build the WHERE clause in a loop.

public function getPostsWithViews(string $user): array
{
    $viewCategoryStrings = $this->db
        ->distinct()
        ->select('category_string') 
        ->where('is_active', 1)
        ->group_start()
            ->where('user_string', $user)
            ->or_where('ip_address', $user)
        ->group_end()
        ->get_compiled_select('views');

    return $this->db
        ->select('*, p.image_name AS post_image, p.random_string AS post_string, p.date_created AS post_creation')
        ->join('users u', 'p.user_id = u.id', 'left')
        ->join("($viewCategoryStrings) v", 'category_string')
        ->order_by('p.date_created', 'DESC')
        ->get('posts p', 25)
        ->result();
}

I am not entirely sure what data is required in the result set, so the parent query's select() call may need some minor tweaking.

See related techniques demonstrated at:

Collectives™ on Stack Overflow

How to build a CodeIgniter active record query with iterated sets of AND conditions containing OR logic?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related