CODEWITHSUNDEEP
phplaraveleloquentcount
cloude
cloude

Reputation: 346

Get 3 counts from 3 tables in one trip to the database using Laravel Eloquent

I have three different eloquent query for get count of my tables.

$posts = Post::where('published', true)->count();
$pages = Pages::where('published', true)->count();
$products = Product::where('published', true)->count();

There is a way for have an unique Eloquent query?

I am concerned about the safety of my query, so if possible, I'd like to avoid DB::raw.

Upvotes: 2

Views: 1355

Answers (2)

mickmackusa
mickmackusa

Reputation: 47864

If you want to employ query builder methods as much as possible, then here is another approach where the additional subqueries are fed into the SELECT clause: (PHPize Demo)

var_export(
    DB::table('Post')
    ->whereRaw('published = true')
    ->selectRaw('COUNT(*) posts')
    ->selectRaw('(' . DB::table('Pages')->whereRaw('published = true')->selectRaw('COUNT(*)')->toSql() . ') pages')
    ->selectRaw('(' . DB::table('Products')->whereRaw('published = true')->selectRaw('COUNT(*)')->toSql() . ') products')
    //->toSQL()
    ->get()
    ->first()
);

I wasn't able to implement ->toSqlWithBindings() in the sandbox as suggested at laravel Eloquent ORM - How to get compiled query? ...but that looks very helpful here.

Upvotes: 0

VIKAS KATARIYA
VIKAS KATARIYA

Reputation: 6005

Write your code like in single query.

$data = DB::select("SELECT (SELECT COUNT(*) FROM posts WHERE published = true) as post_count, (SELECT COUNT(*) FROM pages WHERE published = true) as page_count, (SELECT COUNT(*) FROM products WHERE published = true) as product_count");

After that you can get record like

$data->post_count
$data->page_count
$data->product_count

Upvotes: 2

Related Questions