count relation of relation in laravel

Question 1

Suppose I have a Conversation model like this :

class Conversation extends Model
{
    public function questions (){
        return $this->hasMany('AppQuestion','conversation_id','conversation_id');
    }
    public function category ()
    {
        return $this->belongsTo('AppCategory', 'cat', 'cat_id');
    }

}

And a Question model like this:

class Question extends Model
{
    public function conversation ()
    {
        return $this->belongsTo('AppConversation', 'conversation_id', 'conversation_id');
    }
}

As you can see there is a hasMany relation between those two.

In the other hand there is a Category like below that has a relation with Conversation model :

class Category extends Node
{
    public function conversations (){
        return $this->hasMany('AppConversation','cat','cat_id');
    }
}

Now I want to append an attribute named question_count to Category that counts all questions of conversations of each category. for that I added this :

    public function getQuestionsCountAttribute ()
    {
        return $this->conversations->questions->count();
    }

But when fetch a category I got this error :

ErrorException in Category.php line 59:
Undefined property: IlluminateDatabaseEloquentCollection::$questions

What did I do? how can I count relations of a relation with minimum server overloading?

I am using laravel 5.3.4.

Question 2

I think that you need a has many through relationship here.

What you do wrong:

When you write $this->conversations->questions, this can't work, because the questions are a relation of a single conversation and not of a collection of conversations (here, $this->conversations is a Collection)

The solution:

Using hasManyThrough relation:

You can find the documentation for this relation on this page, if my explanation is bad

The basics are, you need to define a relation on your Category model:

class Category extends Node
{
    public function conversations ()
    {
        return $this->hasMany('AppConversation');
    }

    public function questions ()
    {
        return $this->hasManyThrough('AppQuestion', 'AppConversation');
    }
}

(I will let your look into the documentation for your non standards foreign keys)

You should then be able to use: $category->questions->count()

Question 3

It's mostly raw queries:

DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
DB::statement('SET @sql = CONCAT(...)');
DB::statement('PREPARE stmt FROM @sql');
DB::statement('EXECUTE stmt');
DB::statement('DEALLOCATE PREPARE stmt');

Try this:

DB::table('item_details')->selectRaw('GROUP_CONCAT(...) INTO @sql')->get();
$sql = DB::selectOne('select @sql')->{'@sql'};
ItemDetails::select('item_number', DB::raw('SUM(quantity) as total_quantity'))
    ->selectRaw($sql)
    ->groupBy('item_number')
    ->get();

Question 4

User Model

public function groups()
{
    return $this->belongsToMany('AppGroup');
}

Group Model

public function tasks()
{
    return $this->belongsToMany('AppTask');
}

Task Model

public function groups()
{
    return $this->belongsToMany('AppGroup');
}

Retrieving all tasks for a user.

$user = User::find(1);

$user->load('groups.tasks');

$tasks = $user->groups->pluck('tasks')->collapse();

Question 5

as per @Jarek Tkaczyk's response all i had to do was to country_id in the groupby clause

Question 6

This can be done in (at least) 2 ways.

Using pure Eloquent model logic:

class Buy extends Model
{
  public function getTotalPrice() {
    return $this->buyDetails->sum(function($buyDetail) {
      return $buyDetail->quantity * $buyDetail->price;
    });
  }
}

The only issue here is that it needs to fetch all buy details from the database but this is something you need to fetch anyway to display details in the view.

If you wanted to avoid fetching the relation from the database you could build the query manually:

class Buy extends Model
{
  public function getTotalPrice() {
    return $this->buyDetails()->sum(DB::raw('quantity * price'));
  }
}

count relation of relation in laravel

Answers

php,laravel,laravel-5,eloquent,laravel-5.3