Issue with 1-many Laravel eloquent model query

Question

I have spent several hours trying to figure this out and I'm hoping someone can help..

I'm simply trying to output article headlines with an averaged article rating for each. I've set up db tables Articles and Rating to hold the data, in the Rating table I have rating_id and the primary and article_id as the foreign key linking to the the Articles table [primary key].

I've created a model for each table and a 1-many relationship between Article and Rating models:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Article extends Model
{
    protected $table = 'articles';
    protected $primaryKey = 'article_id';

    /**
     * Define a 1-many relationship in eloquent
     */
    public function ratings()
    {

    return $this->hasMany(Rating::class);

    }
}

I've also setup an inverse relationship between Rating and Article models (is this actually required?):

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Rating extends Model
{
    protected $table = 'rating';
    protected $primaryKey = 'rating_id';

    /**
     * Define a 1-many inverse relationship in eloquent
     */
    public function articles()
    {

    return $this->belongsTo(Article::class);

    }
}

In my controller, I am calling the collection of ratings via the 'ratings' property where article_id=2 - there should be 2 records returned as there are 2 ratings for this article. Code sample:

public function ShowRating() {

$ratings = Article::find(2)->ratings;

var_dump($ratings);

    }

I'm getting the SQL error, is my model relationship incorrect? Code sample:

> QueryException in Connection.php line 647: SQLSTATE[42S22]: Column not
> found: 1054 Unknown column 'rating.article_article_id' in 'where
> clause' (SQL: select * from `rating` where
> `rating`.`article_article_id` = 2 and `rating`.`article_article_id` is
> not null)

Once I have managed this stage I can average out the ratings using the avg method.

Apologies in advance - I'm convinced this is something stupid but I just can't figure it out. Help much appreciated..

Answer 1

I wanted to post my working code, thanks again for the help.

First issue - hadn't defined parameters in my models for hasMany and belongsTo methods defining the relationship between my articles and rating tables - specifically definition of the foreign/primary key for tables; Laravel info: https://laravel.com/docs/5.4/eloquent-relationships

Article model:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Article extends Model
{
    protected $table = 'articles';
    protected $primaryKey = 'article_id';

    /**
     * Define a 1-many relationship in eloquent
     */
    public function ratings()
    {

    //NOTE ignore IDE error
    return $this->hasMany(Rating::class, 'article_id');

    }
}

Rating model:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Rating extends Model
{
    protected $table = 'rating';
    protected $primaryKey = 'rating_id';

    /**
     * Define a 1-many inverse relationship in eloquent
     */
    public function articles()
    {

    return $this->belongsTo(Article::class, 'article_id');

    }
}

Second issue was I was using eager loading to output on the 1-many relationship not the inverse relationship (i.e. using the Rating object). I also used a method on the collection class to apply an average to my ratings and outputted everything in my controller as per below:

    public function GetAllRatings() {

   $ratings = Rating::all();

   $ratings->average = $ratings->avg('rating_score');

   foreach ($ratings as $rating) {
   echo $rating->articles->headline;
   echo $ratings->average;
}     
    }

I hope this helps someone else spend less than 2 days on 15 lines of code, still I've got to learn!! Any errors (even thought seems to be working fine) please let me know and I will amend this answer. Thanks again all.

Answer 2

I think your migration/DB structure is wrong. -> Unknown column 'rating.article_article_id' I may can help you if you post the code.