0

I have a statement that would be really easy to write out (see below) but I can't seem to figure it out for Magento. The statement is:

SELECT * FROM addresses
WHERE
    $postcode_from BETWEEN (pc_from AND pc_to)
    OR $postcode_to BETWEEN (pc_from AND pc_to)
    OR (
        $postcode_from <= pc_from
        AND $postcode_to >= pc_to
    )

Is this going to be possible or am I going to have to write out the statement and manually feed it to Magento?

EDIT*

Here's what I've got currently. It works but is messy:

$select = $read->select()->from($table);
$select->where(
    $read->quoteInto(" ( ? >= postcode_from", $upload['postcode_from']) .
    $read->quoteInto(" AND ? <= postcode_to )", $upload['postcode_from']) .
    $read->quoteInto(" OR ( ? >= postcode_from ", $upload['postcode_to']) .
    $read->quoteInto(" AND ? <= postcode_to )", $upload['postcode_to']) .
    $read->quoteInto(" OR ( ? <= postcode_from ", $upload['postcode_from']) .
    $read->quoteInto(" AND ? >= postcode_to )", $upload['postcode_to'])
);

$selection = $read->fetchAll($select);

EDIT

For anyone confused as to what I am trying to achieve:

I have pc_from_1 and pc_to_1. these are stored in the database.

I also have pc_from_2 and pc_to_2. These are from user input.

I want to check if any of the following occurs:

// Where this
      pc_from_1           pc_to_1
         |-------------------|
pc_from_2       pc_to_2
   |--------------|

//or this
      pc_from_1           pc_to_1
         |-------------------|
                 pc_from_2            pc_to_2
                    |--------------------|

// Or this
      pc_from_1           pc_to_1
         |-------------------|
pc_from_2                         pc_to_2
   |---------------------------------|
// occurs
1

You should be able to use this:

<?php
$postcode_from = 'your_from_number';
$postcode_to = 'your_to_number';
$collection = Mage::getResourceModel('customer/address_collection');
$collection
    ->addFieldToFilter('postcode', array('gteq' => $postcode_from))
    ->addFieldToFilter('postcode', array('lteq' => $postcode_to))
    ;

foreach($collection as $item)
{
   echo $item->getPostcode()."<br>";
}

This should give you list of postcode between the range.

UPDATE

To have multiple OR conditions:

$collection->addFieldToFilter(
        'postcode',
        array(
            array('gteq' => $postcode_from),
            array('lteq' => $postcode_to)
        ),
        array(
            array('lteq' => $postcode_from), 
            array('gteq' => $postcode_to)  
        )
    );

Check this for ref.

  • Thanks for your answer but this only covers one of the BETWEENs. I need to check both BETWEENs and the final lteq and gteq statements too but wwith OR conditions between each one – Wildcard27 May 23 '16 at 1:38
  • Check my update. – Adarsh Khatri May 23 '16 at 1:50
  • I have just updated my answer to show what I am trying to achieve. I hope this makes more sense now – Wildcard27 May 23 '16 at 1:53
1

The condition you described can be simplified to pc_from_1 >= pc_from 2 OR pc_to_1 <= pc_to_2. This way you don't need to check for all three cases separately.

You can combine conditions on different fields with OR, using the following syntax:

$collection->addFieldToFilter(
    ['postcode_from', 'postcode_to'],
    [['gteq' => $upload['postcode_from'], ['lteq' => $upload['postcode_to']]);
  • First parameter: array of fields
  • Second parameter: array of conditions

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.