Eloquent ORM, subquery to get division result of selects from 2 tables

Question

Wondering if someone could give an idea how to write the following statement using Eloquent ORM?

SELECT (SELECT COUNT(*) FROM table_A WHERE created_at >= $STARTDATE and created_at <= $ENDDATE) / (SELECT COUNT(*) FROM table_B WHERE created_at >= $STARTDATE and created_at <= $ENDDATE)

other than

DB::select(
    DB::raw("SELECT (SELECT COUNT(*) FROM TABLE_A WHERE created_at >= ? AND created_at <= ?) / (SELECT COUNT(*) FROM TABLE_B WHERE created_at >= ? AND created_at <= ?) cnt "),
    [$this->startCurrentDate, $this->endCurrentDate, $this->startCurrentDate, $this->endCurrentDate]
)

Answer 1

You can use both WhereBetween() and WhereNotBetween() and count() in eloquent, depending on your version. If all you want is the count, simply use the first query, and the add the result of that query together with the result of the other query. As far as I know, counting two separate table will require two different queries in eloquent unless you want to complicate the query unnecesarily.

If you give us your model names, we can produce the exact code you need. Example:

$from = $this->startCurrentDate;
$to = $this->endCurrentDate;
$query1 = TableA::whereBetween('created_at', [$from, $to])->count();
$query2 = TableB::whereBetween('created_at', [$from, $to])->count();
$count = $query1+$query2;

or:

$from = $this->startCurrentDate;
$to = $this->endCurrentDate;
$query1 = TableA::where('created_at', '>=', $now)
                ->where('created_at', '<=', $to)
                ->get();
$query2 = TableB::where('created_at', '>=', $now)
                 ->where('created_at', '<=', $to)
                 ->get();
$count = $query1+$query2;

Or keep it as DB::RAW, just the way you have it.