Open Technologist Open Technologist - 3 months ago 10
MySQL Question

WHERE Equivalents for SQL 'BETWEEN' and 'IN' in Agile Toolkit ATK4 DSQL

I need help in ATK4 DSQL where i would like to retrieve only a specific
set of rows from my Employee Model Table class, using a Dynamic SQL.

can i generate an SQL BETWEEN or an IN clause in my WHERE statement
such that it would 'somehow' look like these constructs:

SQL 'IN':

SELECT id,fnm,lnm,bp FROM emp WHERE id IN (1,3,5,7,9,11);

$model=$this->add('Model_Employee');
$set=array(1,3,5,7,9,11);
$list=$mode->dsql()
->field('id')
->field('fnm') // first name
->field('lnm') // last name
->field('bp') // basic pay
->where('id in (%l)',$set) // only 1,3,5,7,9 & 11
->do_getAll();


SQL 'BETWEEN':

SELECT id,fnm,lnm,bp FROM emp WHERE bp BETWEEN 3000 AND 5000;

$model=$this->add('Model_Employee');
$min=3000;$max=5000;
$range=$mode->dsql()
->field('id')
->field('fnm') // first name
->field('lnm') // last name
->field('bp') // basic pay
->where('bp between %i and %i',$min,$max)
->do_getAll();


thanks!

Answer

Updated for current version of DSQL: http://git.io/dsql

From the documentation:

$query -> where('time', $query->expr(
    'between [] and []',
    [$from, $to]
));

Old answer: in Agile Toolkit up to 4.1.* "where" is very limited to expressions. You would need to write it like this:

->where('bp between '.
    $this->api->db->escape($min).' and '.
    $this->api->db->escape($max));
Comments