Laravel 5.4 - Return single row from joined table

Question

I have model Album (table albums) and model Photo (table photos). When I'm fetching all albums to page, I would like to display first photo from each album as album image.

Album model:

public function photos()
{
    return $this->hasMany('App\Photo');
}

Photo model:

public function album()
{
    return $this->belongsTo('App\Album');
}

Query:

public function getAlbums()
{
   return DB::table('albums')
        ->join('photos', 'albums.id', '=', 'photos.album_id')
        ->select('photos.title', 'albums.title AS album_title', 'albums.slug AS album_slug')
        ->get();
}

It returns all photos with album title. How to modify the query to return only first photo from each album? How to use LIMIT 1 in joined table? Or is there another way to do this? Thanx.

Reslut

Answer 1

Why not do things the Laravel way instead of raw DB queries? There's a trick to doing exactly this. First in your Album model, add a method (I use latest() but maybe you want to do an oldest(), or some other limit described in the docs ):

public function firstPhoto() {
    return $this->hasOne('App\Photo')->latest();
}

Now you can do:

public function getAlbums() {
    return Albums::with('firstPhoto')->get();
}

Answer 2

Replace get() with first() since get returns an object instance, first will return a single instance;

public function getAlbums()
{
   return DB::table('albums')
        ->join('photos', 'albums.id', '=', 'photos.album_id')
        ->select('photos.title', 'albums.title AS album_title', 'albums.slug AS album_slug')
        ->first()

}