Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Data cannot find correctly by query

I want to develop a Mess Manager Application. Where users(members) comes and goes in every month.

Here is my Eloquent Query in my Controller which I tried:
where session(‘dates’) like- Nov-2023, Dec-2023, Jan-2024

$sessionDate = Carbon::createFromFormat('M-Y', session('dates'));
$currentDate = now();
$sessionDateNumeric = intval($sessionDate->format('Ym'));
$currentDateNumeric = intval($currentDate->format('Ym'));
if ($sessionDateNumeric == $currentDateNumeric) {
  $users = User::where('batch', $this->batch)->where('status', 'active')->get();
} elseif ($sessionDateNumeric < $currentDateNumeric) {
     $users = User::where('batch', $this->batch)
                ->where(function ($query) use ($sessionDate) {
                    $query->where('status', 'active')
                        ->where(function ($query) use ($sessionDate) {
                            $query->whereDate('created_at', '<=', $sessionDate->endOfMonth())
                                ->orWhereDate('updated_at', '<=', $sessionDate->endOfMonth());
                        });
                    $query->orWhere('status', 'inactive')
                        ->where(function ($query) use ($sessionDate) {
                            $query->whereDate('created_at', '<=', $sessionDate->endOfMonth())
                                ->orWhereDate('updated_at', '<=', $sessionDate->endOfMonth());
                    });
                })->get();
   } else {
  $users = [];
}

here is my user table in my MySQL database :

Name status created_at updated_at
M active 2023-12-09 16:33:08 2023-12-09 16:33:08
N active 2023-10-11 16:52:49 2023-12-14 17:51:28
L inactive 2023-11-15 18:02:36 2023-11-22 18:02:36
K active 2023-11-08 04:20:23 2023-12-14 17:37:41
S active 2023-12-14 05:27:27 2024-01-04 05:38:38

you can see in my data table- Oct-2023 month, 1 user is created, so October months $users=1person. Nov-2023 month, 2 users created and 1 user active from previous month so, total $users=3person. Dec-2023 month, 2 users created, 3 user active from previous month and which 1 user inactive in this month but i need his data, so total $users=4person and current month(Jan-2024) its enough to show all active users.

and my desired $users output for month Oct-2023= 1person(N), Nov-2023= 3person(N,L,K), Dec-2023=4person(M,N,K,S),Jan-2024=4person(M,N,K,S) details. I hope I explain my problem.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I find my problems solutions-

    $sessionDate = Carbon::createFromFormat('M-Y', session('dates'));
        $currentDate = now();
        $currentDateNumeric = intval($currentDate->format('Ymd'));
        $sessionDateNumeric = intval($sessionDate->format('Ymd'));
        $sessionDateStartOfMonth = intval($sessionDate->startOfMonth()->format('Ymd'));
        $sessionDateEndOfMonth = intval($sessionDate->endOfMonth()->format('Ymd'));
        $sessionDateAddOneMonth = intval($sessionDate->startOfMonth()->addMonth()->format('Ymd'));
        $users = [];
        $users = User::when($sessionDateNumeric == $currentDateNumeric, function ($query) {
            $query->where('status', 'active');
        })
            ->when($sessionDateNumeric < $currentDateNumeric, function ($query) use ($sessionDateStartOfMonth, $sessionDateEndOfMonth, $sessionDateAddOneMonth) {
                $query->where(function ($query) use ($sessionDateAddOneMonth) {
                    $query->where('status', 'active')
                        ->where('created_at', '<=', $sessionDateAddOneMonth);
                })
                    ->orWhere(function ($query) use ($sessionDateStartOfMonth, $sessionDateEndOfMonth) {
                        $query->where('status', 'inactive')
                            ->where('updated_at', '>=', $sessionDateStartOfMonth)
                            ->where('updated_at', '<=', $sessionDateEndOfMonth);
                    });
            })
            ->get();

    Thanks for contributing.


  2. 0

    You can replace with this query

      $users = User::where('batch', $this->batch)
       ->where(function ($query) use ($sessionDate) {
        $query->where('status', 'active')
           ->where(function ($query) use ($sessionDate) {
            $query->whereDate('created_at', '<=', $sessionDate)
                ->orWhereDate('updated_at', '<=', $sessionDate);
        });

    $query->orWhere('status', 'inactive')
        ->where(function ($query) use ($sessionDate) {
            $query->whereDate('created_at', '<=', $sessionDate)
                ->orWhereDate('updated_at', '<=', $sessionDate);
        });
})
->get();

    You can use this one

    $users = User::where('batch', $this->batch)
    ->where(function ($query) use ($sessionDate) {
        $query->where('status', 'active')
            ->where(function ($query) use ($sessionDate) {
                $query->whereDate('created_at', '<=', $sessionDate->endOfMonth())
                    ->orWhereDate('updated_at', '<=', $sessionDate->endOfMonth());
            })
            ->orWhere('status', 'inactive')
            ->where(function ($query) use ($sessionDate) {
                $query->whereDate('created_at', '<=', $sessionDate->endOfMonth())
                    ->orWhereDate('updated_at', '<=', $sessionDate->endOfMonth());
            });
    })
    ->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search