Bound parameters in QueryBuilder

Question

Bound parameters in QueryBuilder

Omnispex Jan '18

Created Jan '18	Last Reply Jan '18	Replies 2	Views 430	Votes 1

Omnispex 1.3k

Jan '18

Hello,

Consider something like this:

            $query=$this->modelsManager->createBuilder()
                ->from('Articles')
                ->innerJoin('Categoryarticles','Articles.id=Categoryarticles.articleid')
                ->innerJoin('Categories','Categoryarticles.categoryid=Categories.id')
                ->where('Articles.languagekey=:languagekey:')
                 ->where('Categories.languagekey=:languagekey:')
                ->andWhere('Categories.id>:id:',array('id' => $id));           

            $totals=$query             
                ->columns(array('COUNT(DISTINCT Articles.id) AS totals'))
                ->getQuery()
                ->setUniqueRow(true)
                ->execute(array('languagekey' => $this->languagekey(), 'id' => $id);

In here I use parameter languagekey twice, adding it in execute I have to bound a value only one time. It's for me much easier than passing the same parameter & value agan and again in the query construction

Now I use a Paginator using the QueryBuilder, something like

            $paginator=new QueryBuilder(
                array(
                    "builder" => $builder,
                    "limit" => 10,
                    "page" => $page
                )
            );

My question: How can I passing parameters in query execution in QueryBuilder?

1

Emilio Degiovanni · Answer 1 · 2018-01-25T14:31:23-07:00

Hi @omnispex you can't do that but you can make your own paginator adapter when you can set parameters like this

class MyPaginator extends \Phalcon\Paginator\Adapter\QueryBuilder {

    private $params = [];
    public function __construct(array $config) {
        private $params = $config['params'] ?? [];

        parent::__construct($config);
    }

    public function getPaginate() {
        if ($this->params) {
            $this->_builder->setBindParams($this->params);
        }

        return parent::getPaginate();
    }
}

Good luck

Wojciech Ślawski · Answer 2 · 2018-01-26T03:11:08-07:00

PDO extension and db engines when not emulating prepares requries diffrent names and doesn't allow tu use same parameter in binding more than once. To enable it use:

PDO::ATTR_EMULATE_PREPARES => true

In options of db adapter. Keep in mind it's not a good idea and this should be defintely false, beacause it's faster when database engine is doing binding.

Omnispex · Answer 3 · 2018-01-26T10:38:19-07:00

Thank you for your reply, In short you would recommend something like?

        $query=$this->modelsManager->createBuilder()
            ->from('Articles')
            ->innerJoin('Categoryarticles','Articles.id=Categoryarticles.articleid')
            ->innerJoin('Categories','Categoryarticles.categoryid=Categories.id')
            ->where('Articles.languagekey=:languagekey:',array('languagekey' => $this->languagekey()))
            ->andWhere('Categories.languagekey=:languagekey:',array('languagekey' => $this->languagekey()))
            ->andWhere('Categories.trash=:trash:',array('trash' =>$this->trash()));

This is what I have in an older app and it works fine with paginator, but less easier to maintain with many duplicate parameters.