Laravel 7: Finding a relation between two dates-CodePudding

I'm trying to get the jobs relation between two dates by using the whereHas method however I keep getting an Integrity constraint violation and am not sure what is causing this issue.

I've tried using the with and where method, but they all return the same error.

Error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause
is ambiguous (SQL: select * from `categories` where exists (select * from `jobs` inner
join `category_job` on `jobs`.`id` = `category_job`.`job_id` where `categories`.`id` = `category_job`.`category_id` and `created_at` = `=<` and `created_at` >= `2022-06-19 23:59:59`))

Function:

    /**
     *
     */
    public function categoriesThisWeek()
    {
        $categoriesThisWeek = Category::with('jobs')
            ->whereHas("jobs", function ($query) {
                $query
                    ->whereColumn('created_at', '=<', Carbon::now()->startOfWeek())
                    ->whereColumn('created_at', '>=', Carbon::now()->endOfWeek());
            });

        return  $categoriesThisWeek->get();
    }

CodePudding user response：

Both tables probably have a created_at column so you need to specify which one (tbl1.created_at) you want to use.

CodePudding user response：

You have two errors, one is using whereColumn instead of where without precising the table and using values (whereColumn is to compare a column to another one) and the other is using the operator =< which doesn't exist.

public function categoriesThisWeek()
{
    $categoriesThisWeek = Category::with('jobs')
        ->whereHas("jobs", function ($query) {
            $query
                ->where('created_at', '<=', Carbon::now()->startOfWeek())
                ->where('created_at', '>=', Carbon::now()->endOfWeek());
        });

    return  $categoriesThisWeek->get();
}