1

I need help with query or way to do with single query in yii2.

Application is in yii2 , working on admin dashboard. I need to show the Avg days of all products in stock from today date that has been in system which are enabled (in short average age of inventory that is in stock).

NOTE : Just a single avg total required for column to show count on dashboard as a result.

I have a table with column stock_date , that stores in DateTime format Y-m-d H:i:s.

Formula

  1. ( STOCK_DATE - TODAY DATE ) will give you age of single product in system.
  2. Total SUM of column `stock_date' / Total number of products that are enabled will give you average days.

Table columns:

  • inventory_id (primary key)
  • status (1 for enabled)
  • stock_date (datetime)

stock_date column screenshot

$AvgStatsCount = Inventory::find()
    ->select('sum(stock_date ) as stockdate')
    ....
    ->all();

Anyone know how to do that in right way ?

Posting Answer :  working code for me : 

$query = new Query; 
$query->from('mytable')->where(['status' => 1]); 
$AvgStatsCount = $query->average('DATEDIFF(stock_date, CURRENT_DATE())');
Vikram
  • 35
  • 1
  • 10

1 Answers1

0

For this i recommend to use yii\db\Query instead of your model class that extends from ActiveRecord. With Query you can use methods like average, min and max that take a column name or SQL expression string as param and return the max, min or average value from the query.

To get the difference of stock_date - today i don't understand what format you are using, so i can't help you like this with that.

Edit:

So with stock_date in Y-m-d H:i:s date format, you can do something like this with yii\db\Query class:

use yii\db\Query;
...
$query = new Query;
$query->from('myTable');
$query->where(['>', 'stock_date', date('Y-m-d')]);
$average = $query->average('DATEDIFF(`stock_date`, CURRENT_DATE())');
marche
  • 1,756
  • 1
  • 14
  • 24
  • 1
    I have already added a screenshot of DB Column. By Difference I mean `stock_date` having date in format Y-m-d h:i:s. To calculate single product age you need to difference of Today date with date value mentioned in `stock_date` column. Its same like you calculate your age . (Today - DOB) will give your age. Think like this we are calculating average age of a big family, where we have D.O.B of all members and alive members. So you calculate age of all individuals and then average sum of that. Hope that make point clear what I am looking at. Are you able to write a reference query code. – Vikram Jan 19 '17 at 16:24
  • Edited my question, can't test the code right now but it should give you an idea of what to do. I recommend to check the official documentation of `yii\db\Query` and MySQL functions, those functions are really useful. – marche Jan 19 '17 at 17:06
  • Thanks @marche. Here is working code for me : $query = new Query; $query->from('mytable')->where(['status' => 1]); $AvgStatsCount = $query->average('DATEDIFF(`stock_date`, CURRENT_DATE())'); – Vikram Jan 19 '17 at 18:12