Friday, March 31, 2017

Laravel 5.3 - Pull data of polymorphic table in one query?

Using the polymorphic relationship likeable, I have setup authors and books as likeable_type in likeable_items table.

Here are the models:

class Like extends Model {
    public function likeable(){
        return $this->morphTo();
    }
}

class Author extends Model {
    public function likes(){
        return $this->morphMany('App\Like', 'likeable');
    }
}

class Book extends Model {
    public function likes(){
        return $this->morphMany('App\Like', 'likeable');
    }
}

I want to use one efficient query to pull them both in with their respective data, paginated by 10, something like this does not work (I commented the code to show what is needed in each step).

$likeableData = 
DB::table('likeable_items')
  // We want to fetch additional data depending on likeable_type
  ->select(['books.title', 'books.author_name', 'book_counts.like_count']) // when likeable_type = 'book'
  ->select(['authors.name', 'authors.country', 'authors.age', 'author_counts.like_count']) // when likeable_type = 'author'
  ->leftJoin('books', 'books.id', '=', 'likeable_items.likeable_id') // when likeable_type = 'book'
  ->leftJoin('book_counts', 'book_counts.book_id', '=', 'likeable_items.likeable_id') // when likeable_type = 'book'
  ->leftJoin('author_counts', 'author_counts.author_id', '=', 'likeable_items.likeable_id') // when likeable_type = 'author'

  // We want to have distinct results, based on unique id of book/author
  ->distinct()

  // We want to order by the highest like_count, regardlress of likeable_type
  ->orderBy('book_counts.like_count', 'desc') // order by highest like_count when likeable_type = 'book'
  ->orderBy('author_counts.like_count', 'desc') // order by highest like_count when likeable_type = 'author_counts'

  // We want to paginate the mixed results
  ->paginate(10);

return $likeableData;

How can I get the mixed results back of the highest liked author/book by likes_count, with their respective data, paginated by 10?



via Wonka

Advertisement