I have a Many to Many relationship and in the relationship table the "value" field. How do I bring the media to this field using 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;
Example with Eloquent
The best solution to your problem is at the end of this example, but, I made it a point to show by the many-to-many tables so that depending on the case can be used, calmly.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 one still does not have all, because, to access the relationship, 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 as the other case I'm forced 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;
}
So 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>';
}