Querying a table using data from another table

Question

I have a custom table with the following columns:

entity_id Primary int(10) UNSIGNED AUTO_INCREMENT
order_id int(14) UNSIGNED
order_increment_id int(20) UNSIGNED
link varchar(255) utf8_general_ci

There can be multiple rows for the same order.

I am retrieving all orders in a certain state, and need to then retrieve all the rows in my custom table, filtering on order IDs that the collection of orders contains.

I get the orders like this:

    $collection = Mage::getModel('sales/order')->getCollection();
    $collection->addFieldToFilter('status', 'picking_in_progress');

How do I go about using the orders in $collection to retrieve the records in my custom table that match the orders in $collection?

I want to display the results on a grid, and will use data from both tables ie the customer name and surname and date of order creation from $collection, and the link column from my custom table.

Answer 1

What I would do is first create an array containing the order increment Ids:

$ordersIds = array();
foreach($collection as $order) {
    $orderIds[] = $order->getIncrementId();
}

Once you've got this create a new collection of your custom entity and filter it like this:

->addFieldToFilter('order_increment_id', array('in', $orderIds))