magento 'in' conditionals?

I have a field in a database table that stores values something like 1,2,3,4 And i load a collection like following

  $arrayVar[] = '1';
  $arrayVar[] = '4';
  $collection->addFieldToFilter('field_name', array('in' => array($arrayVar)));

now, the above collection should be able to return a field containing values 1,2,3,4 but it doesn't because the query generated by magento collection is

  Select * from table where (field_name in ('1','4'));

The correct query i need is the following, without quotes:

   Select * from table where (field_name in (1,4));

Does anyone has any suggestion how should i get read of the unwanted quotes?

Many thanks in advance

Solution

Short Answer: No, and there's no reason to (if I'm wrong on the second count, more detail in your post may help someone else solve your problem)

I think something may be lost in the translation, so apologies in advance if this misses the mark.

You can't remove those quotes from the query because MySQL (or whatever database engine you're using) needs those quotes.

If you pass in a integer to the filtering function, Magento will behave as you want it to. The following

    $c = Mage::getModel('cms/page')->getCollection()
    ->addFieldToFilter('page_id', array('in'=>array(1,2,3)));

    echo $c->getSelect();

will give you a SQL query that looks like this

SELECT `main_table`.* FROM `cms_page` AS `main_table` WHERE (entity_id IN(1, 2, 3))

However, once you use a string the database generation code needs to surround those paramaters in quotes. Consider the following

Which generates SQL that looks like this.

SELECT `main_table`.* FROM `cms_page` AS `main_table` WHERE (page_id IN('one', 'two', 'three'))

Without the quoting behavior, SQL like the following would be generated,

 WHERE (page_id IN(one, two, three))

which is invalid SQL.

The key to this is there's not efficient or safe way for the database generating code to tell if a string is a numeric string like 1, or a word like one. So it quotes all strings.

It also shouldn't matter, as MySQL has magic such that the following queries behave the same (this might not be true if you're using EE and some other database drivers)

SELECT `main_table`.* FROM `cms_page` AS `main_table` WHERE (page_id IN(1, 2, 3))
SELECT `main_table`.* FROM `cms_page` AS `main_table` WHERE (page_id IN('1', '2', '3'))

If you're hell-bent on removing those quotes, you'll need to cast any variables down to int or float yourself.