How to get the latest added posts grouped by day?

Hello! Tell me, I need to get the data of products added in the last 7 days. I do it this way:

public function getProductsWeek()
    {
        return Product::withTrashed()
            ->where('created_at', '>=', Carbon::now()->subDays(7))
            ->groupBy('date')
            ->orderBy('date', 'ASC')
            ->get(array(
                Product::raw('Date(created_at) as date'),
                Product::raw('COUNT(*) as "products"')
            ))->toArray();
    }

It works. But in this case, if no new entries were added on one of these days, then nothing comes. And I need the date and quantity to come to zero.

How to get an array for all 7 days, no matter if entries were added or not?

LaryAI

3 years ago

Level 58

To get an array for all 7 days, regardless of whether entries were added or not, you can use a left join with a subquery that selects the count of products for each day. Here's an example:

public function getProductsWeek()
{
    $dates = collect();
    $startDate = Carbon::now()->subDays(7)->startOfDay();
    $endDate = Carbon::now()->endOfDay();

    while ($startDate <= $endDate) {
        $dates->push($startDate->format('Y-m-d'));
        $startDate->addDay();
    }

    return DB::table('dates')
        ->leftJoinSub(
            Product::selectRaw('DATE(created_at) as date, COUNT(*) as products')
                ->where('created_at', '>=', Carbon::now()->subDays(7))
                ->groupBy('date'),
            'products',
            'dates.date',
            '=',
            'products.date'
        )
        ->orderBy('dates.date', 'ASC')
        ->get(['dates.date', DB::raw('COALESCE(products.products, 0) as products')])
        ->toArray();
}

This code creates a collection of all the dates in the last 7 days, then performs a left join with a subquery that selects the count of products for each day. The COALESCE function is used to replace null values with 0.

1 like

form12

3 years ago

Level 1

use Illuminate\Support\Facades\DB;

public function getProductsWeek() { $startDate = Carbon::now()->subDays(6)->startOfDay(); // Get the start date for the past 7 days

// Create a subquery to fetch the count of products per day
$subQuery = Product::withTrashed()
    ->select(DB::raw('DATE(created_at) as date'), DB::raw('COUNT(*) as products'))
    ->where('created_at', '>=', $startDate)
    ->groupBy('date');

// Perform a left join with the subquery to include all 7 days, even if there are no entries
$result = DB::table(DB::raw("({$subQuery->toSql()}) as sub"))
    ->rightJoin(DB::raw("(SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) as date
                         UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) as date
                         UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) as date
                         UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) as date
                         UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) as date
                         UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) as date
                         UNION ALL SELECT CURDATE() as date) as dates"), 'dates.date', '=', 'sub.date')
    ->orderBy('dates.date', 'ASC')
    ->select('dates.date', 'sub.products')
    ->get()
    ->toArray();

return $result;

} in this code,we use a subquery to fetch the count of products per day for the pase 7 days. then , we perform a right join with a derived table dates that includes all 7 days, regardless of whether there are entries or not. This ensures that all 7 days are included in the result with the count of products, even if it's zero.

1 like

Rediska

3 years ago

Level 1

@form12 It almost turned out, except for the error))) What's wrong?

Illuminate\Database\QueryException SQLSTATE[HY000]: General error: 2031 (SQL: select dates.date, sub.products from (select DATE(created_at) as date, COUNT(*) as products from products where created_at >= ? group by date) as sub right join (SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) as date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) as date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) as date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) as date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) as date UNION ALL SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) as date UNION ALL SELECT CURDATE() as date) as dates on dates.date = sub.date order by dates.date asc)

Please or to participate in this conversation.