How to write the below eloquent query code using raw query in laravel?-CodePudding

My eloquent query:

$data = (new Model())->whereDate('start_date', '<=', Carbon::today())
                ->whereDate('end_date', '>=', Carbon::today())
                ->count();

The raw query I have tried:

$data = (new Model())->select(DB::raw("COUNT(id) as countData where date(start_date) <= NOW() and date(end_date) >= NOW()"))->get();

How can I write my eloquent query as raw query. The below raw query gives me syntax violation error;

CodePudding user response：

This is because of how you are constructing your query. If you inspect the generated SQL statement you'll see that the SQL FROM clause is actually on the end of your statement and not at all where it should be.

You'll want to split your WHERE clauses out and use either where or whereRaw. For example:

$data = (new Model)->select(DB::raw('COUNT(id) as countData'))
                ->whereRaw('date("start_date") <= NOW() AND date("end_date") >= NOW()')
                ->get();

CodePudding user response：

Your question was a little unclear but if you want to do what you did with eloquent in raw query, be noted that the select function of DB in raw by itself, so simply write your query inside select function:

$tableName = (new Model)->getTable();
$data = DB::select("
    SELECT COUNT(`id`) as `countData` FROM `$tableName`
    WHERE DATE(`start_date`) <= NOW() AND DATE(`end_date`) >= NOW()
")->first();

Alternatively you may use whereRaw function of your Model:

$data = Model::whereRaw(
    "DATE(`start_date`) <= NOW() AND DATE(end_date) >= NOW()"
)->count();