2

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?

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,
));
0

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

  • The first billing_agreement_id will only apply for the current year, because next year is in the or, it will fetch for every billing_agreement_id, so it should be added twice. – Jeroen Jan 16 '16 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.