The COUNT Function in SELECT statement

Did you try DB:: raw ()?

Why not just use eloquent?

If your Article model has a relationship set up:

// Article.php

public function comments()
{
    return $this->hasMany('Comment');
}

Then something like:

foreach (Article::all() as $article) {
    echo $article->title;
    echo $article->comments->count();
}

You can still use query builder methods with eloquent. E.g.

Article::where('created_at', '>', new DateTime('2016-01-01'))->orderBy('id', 'DESC')->get();

Did you try DB:: raw ()?

No, I'm using the Eloquent (as an OOP CLASS to access my DB) only. So

use DB;

causes an error; or i'm doing something wrong

Why not just use eloquent?

I pass $info to TWIG, that means it is not possible to iterate in that way.

@FewG You can still use an eloquent query with Twig. Just do something like:

$info = Article::with('comments')->orderBy('id', 'DESC')->get();

Pass $info to your twig view and you can iterate the collection however you like. Even if you don't want to you can still do $info->toArray() to just get an array. E.g.

echo count($info[0]['comments']);

Although it's easier just to use eloquent.

As a side note, your table naming is unconventional which may be the cause of your errors. You are already using eloquent even without my previous suggestions and it assumes a convention unless you tell it otherwise.

I.e. Your Article model should have a table articles (whereas your table looks to be article - singular). Likewise with the comment table.

Model names should be CamelCase singular whilst tables names should be snake_case plural. You can of course do it your own way but I find it often easier to stick to convention.

@harryg Yes, of course, I can use the Eloquent's name convention, but i'm using the ER-Model and let my table's names always be in singular

   protected $table = 'comment';
// with the primary key = id
// and
// with the foreign keys like foreign_table_id

the problem was that Eloquent interpreted

'count(comment.article_id) as count'

like a column name and not like a statement. Furthermore there is no DB::raw() in Eloquent Standalone (or i haven't found it), but i have been advised of possibility to use in my case

new Illuminate\Database\Query\Expression('count(comment.article_id) as answers')

instead of DB::raw()

P.S. Thank you for your advice

It sounds like you might just rather use the query builder then rather than hack an eloquent query. See https://laravel.com/docs/5.2/queries#joins. You should be able to use raw statements then.

Basically reference the DB facade instead of your eloquent model and specify the table and joins before selecting your fields.

Basically just modify your original query to be like:

$info = DB::table('article')
                ->leftJoin('comment', 'article.id', '=', 'comment.article_id')
                ->leftJoin('user', 'user.id', '=', 'article.user_id')
                ->groupBy('article.id')
                ->select(   // Article's ID
                    'article.id',  
                    // Article's Title
                    'article.title',
                    // ID of Article's Category
                    'article.category_id as cid',
                    // Author's ID
                    'article.user_id as uid',
                    // Summary count of all Views
                    'article.views',
                    // Creation's Time
                    'article.created_at',
                    // Author's Nickname
                    'user.username',
                    // Comment's Count
                    DB::raw('count(comment.article_id) as count')
                )
                ->orderBy('article.id', 'desc')->get();