Model::find() with date range.

Question

Model::find() with date range.

Zach Feb '16

Created Feb '16	Last Reply Feb '16	Replies 3	Views 1455	Votes 0

Zach 17.5k

Feb '16

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"

Boris Delev
4.7k

Accepted
answer

edited Feb '16
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 ...

Zach · Answer 1 · 2016-02-13T17:00:17-07:00

Zach
17.5k

Feb '16

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

huoybb · Answer 2 · 2016-02-13T17:07:16-07:00

You can setup a trait for this, like:(substitue created_at to log_date)

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');
    }
}

Zach · Answer 3 · 2016-02-13T17:47:07-07:00

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.

huoybb · Answer 4 · 2016-02-13T18:55:31-07:00

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)