query inside model with dynamic condition

Question

query inside model with dynamic condition

nimdasx Sep '13

Created Sep '13	Last Reply Sep '13	Replies 1	Views 2037	Votes 1

nimdasx 4.5k

Sep '13

hi all developer i have query like this ini zend framework 1

function atDaftarJoinPeran($peran_id,$modul_id=null,$parent=null,$enable=null,$app_id=null) {
    $s = $this->select();
    $s
        ->setIntegrityCheck(false)
        ->from($this)
        ->join('cr_hak_akses','
            (cr_hak_akses.MODUL_ID=cr_menu.MODUL_ID or cr_hak_akses.MODUL_ID=\'all\') and
            (cr_hak_akses.KONTROLER_ID=cr_menu.KONTROLER_ID or cr_hak_akses.KONTROLER_ID=\'all\') and
            (cr_hak_akses.AKSI_ID=cr_menu.AKSI_ID or cr_hak_akses.AKSI_ID=\'all\')
        ',null)
        ->order('MENU_ORDER')
        ->where('cr_hak_akses.PERAN_ID=?',$peran_id)
        ->where('cr_hak_akses.FLAG_AKSES=?','1')
    ;
    if ($parent) {
        $s->where('PARENT_MENU_ID = ?',$parent);
    } else {
        $s->where('PARENT_MENU_ID IS NULL');
    }
    if ($modul_id) {
        $s->where('cr_menu.MODUL_ID=?',$modul_id);
    }
    if ($enable) {
        $s->where('MENU_ENABLE=?',$enable);
    }
    if ($app_id) {
        $s
            ->joinLeft('cr_modul','cr_modul.MODUL_ID=cr_menu.MODUL_ID',null)
            ->where('cr_modul.app_id=?',$app_id)
        ;
    }

    $rs = $this->fetchAll($s);
    return $rs;
}

how can i convert that query into phalcon and place it in model not in controller.

in zend i can make query with multi condition simply by inject $s->where clause or $s->join before query execution (fetch).

thanks before

1

rebelmiles · Answer 1 · 2013-09-30T03:01:10-07:00

See https://docs.phalcon.io/en/latest/reference/phql.html#creating-queries-using-the-query-builder

Basically

$s = $this->getModelsManager()->createBuilder();

And use $s->andWhere()

nimdasx · Answer 2 · 2013-09-30T23:35:05-07:00

thanks for the answer,

i mean how can i split into multiple statement, not in 1 statement

this is single statement and work

        $user = $this->modelsManager->createBuilder()
            ->from('Sistem\Model\CrUser')
            ->where('user_aktif = :x:', array('x' => 1))
            ->getQuery()
            ->execute();
        foreach ($user as $x) {
            echo $x->user."<br>";
        }

this is multi statement and doesnt work

        //this is not working
        $user = $this->modelsManager->createBuilder()->from('Sistem\Model\CrUser');
        $user->where('user_aktif = :x:', array('x' => 1));
        $user->getQuery()->execute();
        foreach ($user as $x) {
            echo $x->user."<br>";
        }

how can i make multi statement query builder work?

thanks before

nimdasx · Answer 3 · 2013-09-30T23:52:23-07:00

ok i make it work

        $builder = $this->modelsManager->createBuilder();
        $builder->where('user_aktif = :x:', array('x' => 1));
        $builder->orWhere('peran_id = :y:',array('y'=>'opr'));
        $builder->from('Sistem\Model\CrUser');

        $user = $builder->getQuery()->execute();
        foreach ($user as $x) {
            echo $x->user."<br>";
        }

before "from" must splitted

thanks