Varien Queries AND and OR

Question

I'm having some trouble mapping a SQL query to the "Varien syntax".

So, I have a table where two of the columns are 'week' and 'year'. Now I want to grab a collection of data from that table where I want to go from the "current week number" (pre-calculated) in the current year (2015) AND from week 1 to week 52 on 2016.

For now, I have this query:

$deliveryDeviations = Mage::getModel('godtlevert/delivery_deviation')->getCollection()
           ->AddFieldToSelect('*')
           ->AddFieldToFilter('billing_agreement_id', array('eq' => $billingAgreement->getId()))
           ->AddFieldToFilter(
                array(
                    array('week', array('in' => $totalWeeks)),
                    array('year', array('eq' => $year))
                )
            )
            ->AddFieldToFilter(
                array(
                    array('week', array('from' => '1', 'to' => '52')),
                    array('year', array('eq' => '2016'))
                )
            )

But this doesn't seem to be working as expected since it's placing ORs and I want the following:

WHERE (week IN (35,36,...) AND year = 2015)
OR (week IN (1,2,...,52) AND year = 2016)

Where am I going wrong?

Answer 1

I would recommend using prepared statements.

Because the billing agreement should be added for both selections you could go with the next code.

$collection = Mage::getResourceModel('godtlevert/delivery_deviation_collection');

// Add all fields to select
$collection->addFieldToSelect('*');

// Prepare statement and assign variables     
$collection->getSelect()
        // Add both filter
        ->where('billing_agreement_id = :billing_agreement_id and week in (:currentyear_week) and year = :currentyear')
        ->orWhere('billing_agreement_id = :billing_agreement_id and week in (:nextyear_week) and year = :nextyear')

        // Bind data
        ->bind(array(
    'billing_agreement_id' => $billingAgreement->getId(),

    'currentyear_week' => range(35, 52),
    'currentyear' => 2015,

    'nextyear_week' =>  range(1, 52),
    'nextyear' => 2016,
));

Answer 2

Try this:

<?php
$deliveryDeviations = Mage::getModel('godtlevert/delivery_deviation')
                            ->getCollection()
                            ->AddFieldToSelect('*')
                            ->AddFieldToFilter(
                                'billing_agreement_id',
                                array (
                                    'eq' => $billingAgreement->getId()
                                )
                            );
// quote params
$adapter    = $deliveryDeviations->getSelect()->getAdapter();
$year       = $adapter->quote($year);

$deliveryDeviations->getSelect()
                   ->where("week in (?) AND year = {$year}", $totalWeeks)
                   ->orWhere('week in (?) AND year = 2016', range(1, 52));

Note that Using AND nested within OR is not supported with addFieldToFilter - @fschmengler