phplaraveleloquent-relationshiplaravel-relations

Laravel 7: Finding a relation between two dates


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();
    }

Solution

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