Using user id inside query while fetching users

I'm trying to get All users that have more than 20 reports on them within the last 2 hours, considering the reports created after the user's last ban (if there is one) created_at value.

$users = User::withWhereHas("profile", function ($query) {
            $query->whereNull("banned_until");
        })
            ->whereHas('reports', function ($query) {
                $query->where('created_at', '>=', Carbon::now()->subMinutes(120))
                    ->where('created_at', '>=',
						 Carbon::parse(BanLog::where("banned_id", /* user_id here */)
->latest()->value("created_at"))->format("Y-m-d H:i:s"))
                    ->groupBy('reported_id')
                    ->havingRaw('COUNT(*) >= ' . 20);
            })
            ->get();

How to get the user id, and how can I optimize this query?

Shivamyadav

2 years ago

Level 20

try using this

$users = User::withWhereHas("profile", function ($query) {
        $query->whereNull("banned_until");
    })
    ->whereHas('reports', function ($query) {
        $query->where('created_at', '>=', Carbon::now()->subMinutes(120))
            ->whereRaw('created_at >= (
                SELECT MAX(created_at)
                FROM ban_logs
                WHERE banned_id = users.id
            )')
            ->groupBy('reported_id')
            ->havingRaw('COUNT(*) >= 20');
    })
    ->get();

1 like

Phil_Dr

2 years ago

Level 1

@Shivamyadav Thank you, but this code only works if the user has a row in the ban_logs table, but if there is no row, the query doesn't return the user.

So I slightly edited it and now it works just fine:

$users = User::query()->withWhereHas("profile", function ($query) {
            $query->whereNull("banned_until");
        })
            ->whereHas('reports', function ($query) {
             $query->where('created_at', '>=', Carbon::now()->subMinutes(120)
                    ->where(function ($subQuery) {
                        $subQuery->whereRaw('created_at >= (
                            SELECT MAX(created_at)
                            FROM ban_logs
                            WHERE banned_id = users.id
                        )')
                            ->orWhereNotExists(function ($innerSubQuery) {
                                $innerSubQuery->select(DB::raw(1))
                                    ->from('ban_logs')
                                    ->whereRaw('banned_id = users.id');
                            });
                    })
                    ->groupBy('reported_id')
                    ->havingRaw('COUNT(*) >= 20');
            })
            ->get();

Thank you again for your help in finding a solution to the problem.

Please or to participate in this conversation.