Catch the Average of a Relationship Table FieldMany to Many Laravel Eloquent

Question

I have a relationship Many to Many and in the relationship table the field "value". How do I bring the media from this field using the Eloquent?

Example:

Tabela A
a_id

Relationship Table

a_id
c_id
value

Table C

c_id

The query in mysql would be this:

SELECT sentis_feelings.feeling_id, avg(sentis_feelings.value) 
FROM sentis_feelings 
GROUP BY sentis_feelings.feeling_id;

Answer 1

Example with Eloquent

Obs: the best solution to your problem is at the end of this example, but, I made sure to show by the tables from many to many so that depending on the case can be used, quietly.

Tables

CREATE TABLE `feeling` (
  `feeling_id` int(11) NOT NULL AUTO_INCREMENT,
  `feeling_descricao` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`feeling_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
---------------------------------------------------------
CREATE TABLE `sentis` (
  `sentis_id` int(11) NOT NULL AUTO_INCREMENT,
  `sentis_desc` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`sentis_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
---------------------------------------------------------
CREATE TABLE `sentis_feelings` (
  `sentis_id` int(10) NOT NULL,
  `feeling_id` int(10) NOT NULL,
  `value` decimal(18,2) DEFAULT NULL,
  PRIMARY KEY (`sentis_id`,`feeling_id`),
  KEY `pkf1_idx` (`feeling_id`),
  KEY `pkf2_idx` (`sentis_id`),
  CONSTRAINT `pkf1` FOREIGN KEY (`feeling_id`) 
   REFERENCES `feeling` (`feeling_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `pkf2` FOREIGN KEY (`sentis_id`) 
   REFERENCES `sentis` (`sentis_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Models

//MODEL SENTIS
class Sentis extends Eloquent {
    public $table      = 'sentis'; 
    public $primaryKey = 'sentis_id';
    public $timestamps = false;    
    public function feeling()
    {
        return $this->belongsToMany('Feeling','sentis_feelings', 'sentis_id','feeling_id')
                    ->withPivot('value');
    }
}

---

//MODEL FEELING
class Feeling extends Eloquent {
    public $table      = 'feeling'; 
    public $primaryKey = 'feeling_id';
    public $timestamps = false;

    public function sentis()
    {
        return $this->belongsToMany('Sentis', 'sentis_feelings', 'feeling_id', 'sentis_id')
                    ->withPivot('value');
    }
}

Eloquent Query

the first result brings the grouping by feeling_id=1 (model feeling), but, this still does not bring all, because, to access the relationship I am obliged to pass the feeling_id (find(1)).

$resultado = Feeling::find(1)
    ->sentis()
    ->select(DB::raw('AVG(`value`) AS `Media`'))                    
    ->groupBy('feeling_id')
    ->get()
    ->toArray();
echo $resultado[0]['pivot']['feeling_id']. ': '.$resultado[0]['Media'];

The second result is bringing the grouping by sentis_id=2 (model Sentis), happening the same thing of the other case I am obliged to pass sentis_id (find(2)).

$resultado = Sentis::find(2)
    ->feeling()
    ->select(DB::raw('AVG(`value`) AS `Media`'))                    
    ->groupBy('sentis_id')
    ->get()
    ->toArray();
echo $resultado[0]['pivot']['sentis_id']. ': '.$resultado[0]['Media'];

Best Solutions

1 )

To solve use, then another model SentisFeeling as code below:

class SentisFeeling extends Eloquent {
    public $table      = 'sentis_feelings'; 
    public $primaryKey =  array('sentis_id', 'feeling_id');
    public $timestamps = false;
}

Thus answering your question with the same SQL as the question

$resultado = SentisFeeling::groupBy('feeling_id')
    ->select(DB::raw('`feeling_id`, AVG(`value`) AS `Media`'))
    ->get()
    ->toArray();

foreach ($resultado as $item) {
    echo $item['feeling_id']. ' - '. $item['Media'];
    echo '<br>';
}

2 )

Using Query Builder as an example below:

$resultado = DB::table('sentis_feelings')
    ->groupBy('feeling_id')
    ->select(DB::raw('`feeling_id`, AVG(`value`) AS `Media`'))
    ->get();

foreach ($resultado as $item) {
    echo $item->feeling_id. ' - '. $item->Media;
    echo '<br>';
}