Put another collection as the values of "WHERE ... IN (...)"

Question

I have three collections and want to find out the mutually existing record between them according to one of the column. My current solution is to put another collection to "IN" in the query, like:

$second_collection = $model->getCollection()
                           ->addFieldToFilter("in"=>$another_collection);

However, the value returned by $another_collection->getData() is not an acceptable array to "addFieldToFilter" function. So I use "another_collection->getColumnValues('field')" to return an array. But 500 internal server error occurs. How can I do?

Code of getting collection:

$model = Mage::getModel('model/table');
$first_collection = $model->getCollection()
                         ->addFieldToSelect('target_field')
                         ->addFilter('filter_field', $filter_value1);
$second_collection = $model->getCollection()
                         ->addFieldToSelect('target_field')
                         ->addFilter('filter_field', $filter_value2)
                         ->addFieldToFilter('target_field', 'in'=>$first_collection->getColumnValues('target_field'));
$third_collection = $model->getCollection()
                          ->addFieldToSelect('target_field')
                          ->addFilter('filter_field', $filter_value3)
                          ->addFieldToFilter('target_field', 'in'=>$second_collection->getColumnValues('target_field'));

Thank you in advance.

Answer 1

The First problem is in method addFieldToFilter

addFieldToFilter('target_field', 'in'=>$second_collection->getColumnValues('target_field'));

must be

addFieldToFilter('my_field', ['in' => 'my_condition'])

The second parameter must be an array

You can add a SQL query in IN with something like this:

addFieldToFilter('my_field', ['in' => new Zend_Db_Expr($collection->getSelectSql(true))])