Marco Marco - 6 months ago 129
SQL Question

Laravel hasManyThrough whereBetween dates

I have models for which I get count of views through users. I have set up those models like this:

public function views()
{
return $this->hasManyThrough('App\View', 'App\User');
}


In my controller I am sending json data to charts, for each of those model's objects like this:

public function barCharts(Request $request){
$modelName = 'App\\'.$request['option'];
$model = new $modelName;

foreach($model->all() as $val){
$modelViews[$val->name] = $val->views->count();
}

return json_encode($modelViews);
}


So for example, my Chain model has 3 chains in DB, and through this function I get number of views for each chain.
That is working all fine, but now I need to get that data, for the time interval which I will get from
$from = $request['from']; $to = $request['to'];


So, basically, what I would need is something like this:

$modelViews[$val->name] = $val->views->whereBetween('created_at', [$from, $to])->count();


I am wondering if there is any elegant solution to it and how to go about this or I will have to use some sql query.

Updated code

I had to also add a table to whereBetween clause since it was giving me an error:


SQLSTATE[23000]: Integrity constraint violation: 1052 Column
'created_at' in where clause is ambiguous


So,this is how it worked in the end for me:

foreach($model->all() as $val){
$modelViews[$val->name] = $val->views()->whereBetween('views.created_at', [$from.' 00:00:00', $to.' 00:00:00'])->count();
}

Answer

You just have a small typo (or misconception) in your code. You are missing the () after $val->views:

$modelViews[$val->name] = $val->views()->whereBetween('created_at', [$from, $to])->count();

When you are calling ->views Laravel already fetches the data from the database. When you are calling ->views() you get a query object which you can modify further.

You will also have to make sure that your dates are formatted something like this: YYYY-mm-dd HH:mm:ss so they match the created_at column.