We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Beginners

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:',



85.5k
Accepted
answer

probably you need this https://docs.phalcon.io/en/latest/api/Phalcon_Db_RawValue.html

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



498

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



85.5k

give me a sec to try it



85.5k

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`);


498
in reply to Izo

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`);


85.5k

hmm i really dont know ...

open another thread hopefully someone else have an idea.



145.0k
edited Jul '16

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