ActiveRecord where_in() with array

Question

I have tried a few different approaches to this problem with no solutions so far. I am receiving this error message:

Unknown column 'Array' in 'where clause'

SELECT * FROM (Articles) WHERE id IN (Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array, Array)

Here is my model code:

function getStarNews($user_id) {
    $this->db->select('id');
    $this->db->where('user_id', $user_id);
    $query = $this->db->get('table1');

    foreach ($query->result_array() as $id)
    {
        //echo $id['id']."<br>"; //displays all of the correct ids I am trying to pass to the where_in()
    }
    $id = $query->result_array();

    $this->db->where_in('id', $id); //pass array of 'id' from above query
    $data = $this->db->get('Articles');

    return $data->result_array();
}

If I alter the id array to contain only 1 value then the where_in() clause works fine and outputs the row of data matching the single 'id'.

I've looked all over stack and google for help in using where_in() and I think I have everything correct or have tried a few different methods for passing an array correctly.

Thanks for the help.

EDIT: In the end I will be outputting this data with my controller using:

$this->output->set_output(json_encode($data));

For testing purposes I was just skipping the JSON and trying to output the data with PHP from the model directly.

Solution

The array you try to pass is a multi dimensional array. Instead try this:

$ids = array();
foreach ($query->result_array() as $id)
    {
        $ids[] = $id['id'];
    }

$this->db->where_in('id', $ids);

You can not flatten the query->result_array() without iteration. But if you need to handle this kind of queries a lot in your application, and if you have >= PHP 5.3 installed, you could put the following function in a Codeigniter helper file (or somewhere else suitable) to help you flattening arrays:

function flatten(array $array) {
    $return = array();
    array_walk_recursive($array, function($a) use (&$return) { $return[] = $a; });
    return $return;
}

And in your case use it like this:

    $ids = flatten($query->result_array());
    $this->db->where_in('id', $ids); 

Comments

[Twhyler]

Im not currently running the query inside the foreach loop so I think that would just extract the first id out of the array and then passing that to the where_in() instead of passing the array to the where_in()

[Twhyler]

That worked perfectly, thank you. I was just in the chat and had mentioned that using a foreach to place the items into another array as a solution, I was just missing the $ids = array(); Thank You!'

[Michael Krikorev]

You dont't have to put the query in the loop, just build an array thats not associative.

[Twhyler]

Just for the sake of argument, is there any alternative way from converting an associative array to a flat array? Or instead of getting an associative array from the '$this->db->get(), just return a flat array??

[Michael Krikorev]

Your welcome.. probably even more elegant way is to use $id = array_values($query->result_array()) to get an array of only the values.. edited my answer with example..

[Twhyler]

That answers the question I had in the comment above, thanks again!

[Twhyler]

Using the shorter method without the loop doesn't work for me, not sure why. I get the same error as before where the where_in()has ARRAY as the values. Any idea?

[Michael Krikorev]

Hmm.. not sure why. Haven't worked with Codeigniter for a while.. I'll update my answer when I get a chance to test it..

[Michael Krikorev]

seems like the results is a multi dimensional array that is not possible to flatten without iteration: ellislab.com/forums/viewthread/147384/#718443

[Twhyler]

From what I've gathered its because its still an associative array at that point. Someone recommended data mapping to me as an alternative to active record because of problems similar to this.