My code get data from database using AJAX and return to view

Question

My code get data from database using AJAX the problem is i want to filter data by month year and quarter and the code that does that is:

->where(\DB::raw('MONTH(created_at)'), Carbon::today()->month);

However can't put it in a variable for example and call it so i did this it works but looks ugly.

I have this code in DashboardController:

public function readData(){

$TableB1 = \DB::table('users')
    ->join('group_user', 'users.id', '=', 'group_user.user_id')
    ->join('groups', 'groups.id', '=', 'group_user.group_id')
    ->select(
       'users.name as name',
       'group_user.user_id as id',
       'groups.name as groupname'
       )
    ->get();

    $filter = $_REQUEST["filter"]; // Get the variable data through AJAX call from the index


    foreach ($TableB1 as &$entry){
        if ($filter == 'month'){ // Filter data by month
        $meetings = \DB::table('meetings') 
        ->where('company_id', 1)->where('owned_by_id', $entry->id) 
        ->where(\DB::raw('MONTH(created_at)'), Carbon::today()->month);
        }
        else if ($filter == 'quarter'){ // Filter data by quarter
            $meetings = \DB::table('meetings') 
            ->where('company_id', 1)->where('owned_by_id', $entry->id) 
            ->where(\DB::raw('QUARTER(created_at)'), Carbon::today()->quarter);
        }
        else if ($filter == 'year'){ // Filter data by year
            $meetings = \DB::table('meetings') 
            ->where('company_id', 1)->where('owned_by_id', $entry->id) 
            ->where(\DB::raw('YEAR(created_at)'), Carbon::today()->year);
        } else { // Filter data by day
            $meetings = \DB::table('meetings') 
            ->where('company_id', 1)->where('owned_by_id', $entry->id) 
            ->where(\DB::raw('DAY(created_at)'), Carbon::today()->day);
        }
        $entry->meetingsCount = $meetings->count();


    }

    return $TableB1;

 }

In index.blade.php I have this code to retrieve the data using ajax:

var filter = 'day';

$(document).ready(function(){
    $.get('/dashboard/read?filter=' + filter, function(data){
        $.each(data,function(i,value){
            var tr =$("<tr/>");
            tr.append($("<th/>",{
                text : value.groupname
            })).append($("<th/>",{
                text : value.name
            })).append($("<th/>",{
                text : value.meetingsCount
            })).append($("<th/>",{
                text : value.callsCount
            })).append($("<th/>",{
                text : value.leadsCount
            }))
            $('#tableData').append(tr);
        })
    })

    $("#day").click(function() { // Filter data by day
        filter = 'day';

        $('#tableData').find('tr').empty(); // Clear Table 1st


        $.get('/dashboard/read?filter=' + filter, function(data){
            $.each(data,function(i,value){
                var tr =$("<tr/>");
                tr.append($("<th/>",{
                    text : value.groupname
                })).append($("<th/>",{
                    text : value.name
                })).append($("<th/>",{
                    text : value.meetingsCount
                })).append($("<th/>",{
                    text : value.callsCount
                })).append($("<th/>",{
                    text : value.leadsCount
                }))
                $('#tableData').append(tr);
            })
        })
    });

    $("#month").click(function() { //Filter data by month
        $('#tableData').find('tr').empty(); // Clear Table 1st

        filter = 'month';

        $.get('/dashboard/read?filter=' + filter, function(data){
            $.each(data,function(i,value){
                var tr =$("<tr/>");
                tr.append($("<th/>",{
                    text : value.groupname
                })).append($("<th/>",{
                    text : value.name
                })).append($("<th/>",{
                    text : value.meetingsCount
                })).append($("<th/>",{
                    text : value.callsCount
                })).append($("<th/>",{
                    text : value.leadsCount
                }))
                $('#tableData').append(tr);
            })
        })
    });

    $("#quarter").click(function() { //Filter data by quarter
        filter = 'quarter';

        $('#tableData').find('tr').empty(); // Clear Table 1st

        $.get('/dashboard/read?filter=' + filter, function(data){
            $.each(data,function(i,value){
                var tr =$("<tr/>");
                tr.append($("<th/>",{
                    text : value.groupname
                })).append($("<th/>",{
                    text : value.name
                })).append($("<th/>",{
                    text : value.meetingsCount
                })).append($("<th/>",{
                    text : value.callsCount
                })).append($("<th/>",{
                    text : value.leadsCount
                }))
                $('#tableData').append(tr);
            })
        })
    });

    $("#year").click(function() { //Filter data by year
        filter = 'year';

        $('#tableData').find('tr').empty(); // Clear Table 1st

        $.get('/dashboard/read?filter=' + filter, function(data){
            $.each(data,function(i,value){
                var tr =$("<tr/>");
                tr.append($("<th/>",{
                    text : value.groupname
                })).append($("<th/>",{
                    text : value.name
                })).append($("<th/>",{
                    text : value.meetingsCount
                })).append($("<th/>",{
                    text : value.callsCount
                })).append($("<th/>",{
                    text : value.leadsCount
                }))
                $('#tableData').append(tr);
            })
        })
    });

I know its a mess but how to refactor this code to make it look a little bit nicer the code works but looks awful.

Answer 1

Firstly, don't use php's super globals with Laravel. For the most part you can use the Request class for this. With most things in Laravel, the Request can be accessed in multiple different ways including injection (where it is resolved from the service container), facades and helper functions. For this example I'll use the request helper function:

$filter = request()->input('filter'); 

*You could also do request('filter') but for some params this could cause an issue.

---

As for you query I would strongly recommend using Eloquent instead of the query builder in this case.

You should already have a User model in your app directory so you should just need to create the Group and Meeting models. You can run the following command to do this:

php artisan make:model Group && php artisan make:model Meeting

Then add the following to your User model:

public function meetings()
{
    return $this->hasMany(Meeting::class, 'owned_by_id');
}

public function groups()
{
    return $this->belongsToMany(Group::class);
}

and add the following to your Group model:

public function users()
{
    return $this->belongsToMany(User::class);
}

finally, you don't need to do this yet but in your Meeting model:

public function user()
{
    return $this->belongsTo(User::class, 'owned_by_id');
}

---

The above will allow you to have a controller method looking something like:

public function readData()
{
    $filter = request()->input('filter');

    return \App\User::with('groups')->withCount(['meetings' => function ($query) use ($filter) {

        $filter = in_array($filter, ['month', 'quarter', 'year']) ? $filter : 'day';

        $query->where('company_id', 1)->whereRaw("$filter(created_at)", today()->$filter);
    }]);
}

groupname will now be available as group->name and the count will be available as meetings_count.