Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

How to use left() in condition ?

My query is like this:

$childrens = \Page::find(array ( 'conditions'=>'left(ancestors,1) = :id:', 'bind'=>array('id'=>$this->id) ));

My 'ancestors' column contains rows data:

6

6,7

6,7,8

And I want to select 3 of above

=> Error message: Syntax error, unexpected token LEFT, near to '(ancestors,1) = :id:',



78.9k
Accepted
answer

probably you need this https://docs.phalconphp.com/en/latest/api/Phalcon_Db_RawValue.html

but i dont know that left conditions, i havent used it :-)



316

Sorry, I miss click "Accepted answer" for the above answer

I mean how to build this query ex:

select * from page where left(date,4) = '2015';

If in the database we have: date 2016-01-15 2015-02-16



78.9k

give me a sec to try it



78.9k

this seems to be working for me :


        $di = \Phalcon\Di::getDefault();

        $db = $di->get('db');

        $sql = $db->query("SELECT * FROM `delete_it` WHERE LEFT(dat, 4) = 2015");

        $res = $sql->fetchAll();

    CREATE TABLE `delete_it` (
      `id` int(11) NOT NULL,
      `dat` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `delete_it` (`id`, `dat`) VALUES
    (1, '2016-07-14 10:37:21'),
    (2, '2016-07-14 10:37:24'),
    (3, '2015-07-14 10:40:37');

    ALTER TABLE `delete_it`
      ADD PRIMARY KEY (`id`);


316

Thanks for your answer.

But how can we use the condition in find() method

\Page::find(array ( 'conditions'

Can we do it in phalcon ? Another framework like cakephp is no problem with this.

this seems to be working for me :


      $di = \Phalcon\Di::getDefault();

       $db = $di->get('db');

       $sql = $db->query("SELECT * FROM `delete_it` WHERE LEFT(dat, 4) = 2015");

       $res = $sql->fetchAll();

  CREATE TABLE `delete_it` (
    `id` int(11) NOT NULL,
    `dat` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  INSERT INTO `delete_it` (`id`, `dat`) VALUES
  (1, '2016-07-14 10:37:21'),
  (2, '2016-07-14 10:37:24'),
  (3, '2015-07-14 10:40:37');

  ALTER TABLE `delete_it`
    ADD PRIMARY KEY (`id`);


78.9k

hmm i really dont know ...

open another thread hopefully someone else have an idea.

edited Jul '16

Wtf, use YEAR() ? I just suppose that LEFT() in PHQL is not supported.