Phalcon DB Criteria in Model ORM

Question

In Phalcon model is there a way to use SQL "IN" condition and other database criteria? For example, i want to execute this query in phalcon

SELECT user_fullname, user_email FROM tbl_users WHERE user_id IN (1,2,3,4,5)

I know you can use query builder but I want it to be ORM. I want a method in my model getUsersByIds($userIds, $columnsToSelect) that will accept an array of userids and columns that you want to fetch in the table.

See my model below

<?php

use Phalcon\Mvc\Model;

class User extends Model
{
/**
 *
 * @var integer
 */
public $user_id;

/**
 *
 * @var string
 */
public $user_email;

/**
 *
 * @var string
*/

public user_phone;
/**
 *
 * @var string
*/
public user_fullname;

public function initialize()
{    $this->hasMany('user_id','Store\Bag','user_id');
}
public function getSource()
{
    return "tbl_user";
}

public function find($parameters = null)
{
    return parent::find($parameters);
}

public function findFirst($parameters = null)
{
    return parent::findFirst($parameters);
}
/**
* I want to execute this query in my model
*  SELECT user_fullname,user_email from tbl_user where user_id IN (1,2,3,4,5);
*/
 public function getUsersByIds($ids=array(), $columns=array())
 {
    if(!is_array($ids))
        $ids = array($ids);
    if(!is_array($columns))
        $columns = array($columns); 

     ...................................
     .........................
      ..............
 }  
}

score 2 · Accepted Answer · edited Sep 01 '16 at 18:39

2

There are two ways of doing this:

Method 1: inWhere('columnName', array('columnValues'))

public function getUsersByIds(array $userIds, $columnsToSelect = '') {
    return self::query()
    ->inWhere('id', $userIds)
    ->columns($columnsToSelect ?: '*')
    ->execute();
}

This one goes in your User model

Method 2: conditions => 'id IN ({ids:array})

User::find(['id IN ({ids:array})', 
             'columns' => $columnsToSelect,
             'bind' => array('ids' => $userIds)]);

I am typing on my mobile, so there might be a typo in there.

edited Sep 01 '16 at 18:39

Nikolay Mihaylov

3,868
8
27
32

answered Sep 01 '16 at 16:07

Timothy

2,004
3
23
29

1

+1 for `{ids:array}`. Just to note: `$columnsToSelect ?: '*'` in your case $columnsToSelect has a default value in the function definition, but if not set it will throw a notice. :) – Nikolay Mihaylov Sep 01 '16 at 19:01
It works! I need the method 1. Thank you very much for all the help – user1149244 Sep 02 '16 at 02:33
1

@user1149244 If Timothy's answer helped you consider accepting it as the right answer. :) – Nikolay Mihaylov Sep 02 '16 at 04:36
I have a follow-up question, is using method 1 safe from SQL injection? – user1149244 Sep 02 '16 at 06:45
1

@user1149244 yes it is safe! – Timothy Sep 02 '16 at 06:56

Phalcon DB Criteria in Model ORM

1 Answers1