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.

Model::find() with date range.

Sorry if this has been asked before. I search for a while and couldn't find an answer that worked.

Basically, I have two datepickers in the UI... From Date and To Date. I want to find entries that belong to that user in that date range.

In my model:

    public function getLogData($from,$to) {
        $user = $this->getDi()->getShared('session')->get('auth');
        $from = gmdate('Y-m-d H:i:s',strtotime($from));
        $to = gmdate('Y-m-d H:i:s',strtotime($to));
        $logs = $this->find([
            "user_id = ".$user->id,
            "log_date BETWEEN ".$from." AND ".$to,
            "order" => "log_date ASC"
        ]);
        $arr = [];
        foreach($logs as $log) {
            $log->log_date = $log->getLogDate();
            $log->total = $this->getLogTotal($log);
            $arr[] = $log;
        }

        return $arr;
    }

No matter what the $from and $to date are, this returns all records for the user. What am I doing wrong?

My find array looks like this with the vars in place:

    [0]=> "user_id = 3"
    [1]=> "log_date BETWEEN 2016-02-11 07:00:00 AND 2016-02-14 07:00:00"
    ["order"]=>    "log_date ASC"


4.3k
Accepted
answer
edited Feb '16

Amm, try this:

$logs = $this->find([
    'conditions' => "user_id = {urser:int} AND log_date BETWEEN {from:str} AND {to:str}",
    'bind' => [
        'user' => $user->id,
        'from' => $from,
        'to' => $to
    ],
    'order' => 'log_date ASC'
]);

And... is it this in controller? U can use just $this->session->auth ...



10.2k

It's in a model, but the conditions worked. Thanks!



4.6k
edited Feb '16

You can setup a trait for this, like:(substitue createdat to logdate)

use Carbon\Carbon;
trait dateRangeTrait
{
    public function getResultsBetween(Carbon $startTime, Carbon $endTime)
    {
        $className = get_class($this);
        /** @var myModel $this */
        return $this->getModelsManager()->createBuilder()
            ->from($className)
            ->where('created_at BETWEEN :start: AND :end:',['start'=>$startTime->toDateTimeString(),'end'=>$endTime->toDateTimeString()])
            ->orderBy('created_at DESC');
    }
}


10.2k

Is there a forum post or tutorial you can point me to that explains how to use traits in Phalcon? I've not seen that before.



4.6k
edited Feb '16

I think, trait has nothing to do with phalcon, it just a PHP feature (PHP 5.5 up, maybe). In your model just use, (myModel extends Phalcon\Mvc\Model)

class Files extends myModel 
{

    use attachableTrait;
    use commentableTrait;
    use taggableTrait;
    use navTrait;
    use revisionableTrait;
    use FileableTrait;
    use LinkableTrait;
    use countForRootClassTrait;
    use RelationshipTrait;
    use StatisticsTrait;
    use dateRangeTrait;
    ... ...

Then you can use this model like this to get a month's files. (Using Carbon gives you more flexibility)

    $month = '2016-02'; //This could be obtained from request
    $startTime = new Carbon\Carbon();
    $startTime->setTimestamp(strtotime($month));
    $endTime = clone $startTime;
    $endTime->addMonth();
    $file = new Files();
    $file->getResultsBetween($startTime,$endTime)