The Eloquent way so to speak isn't always the most eloquent way to do stuff unfortunately.
When it comes to more complex queries like the one below (taken from my mediabase project).
SELECT DISTINCT (SELECT GROUP_CONCAT(a.id ORDER BY a.id SEPARATOR ',')
FROM authors a, author_books ab
WHERE a.id = ab.author_id
AND ab.book_id = b.id) author_id,
(SELECT GROUP_CONCAT(concat(a.last_name, ', ', a.first_name)
ORDER BY a.last_name, a.first_name SEPARATOR ' & ')
FROM authors a, author_books ab
WHERE ab.author_id = a.id
AND ab.book_id = b.id) author_name,
b.id book_id,
b.title,
(SELECT ROUND(AVG(s.score), 1)
FROM scores s
WHERE s.item_id = b.id AND media_type_id = 1) rating,
b.series,
b.part,
b.released,
g.id genre_id,
g.genre,
f.id format_id,
f.format,
CASE series
WHEN 'Standalone' THEN b.released
ELSE (SELECT MIN(bi.released) FROM books bi WHERE bi.series = b.series)
END series_started,
bc.user_id
FROM authors a,
books b,
genres g,
formats f,
book_collections bc
WHERE b.genre_id = g.id
AND b.format_id = f.id
AND b.id = bc.book_id
As you can see it uses five tables and a couple of inline views. To create something like this in the query builder would be way more complex. So what I like to do is create a database view to pull in the data I need from the tables I need. To create one of these database views is very simple. A migration could look lile this.
class CreateBookCollectionViews extends Migration
{
public function up()
{
DB::statement(
"CREATE OR REPLACE VIEW book_collection_views AS
SELECT DISTINCT (SELECT GROUP_CONCAT(a.id ORDER BY a.id SEPARATOR ',')
FROM authors a, author_books ab
WHERE a.id = ab.author_id
AND ab.book_id = b.id) author_id,
(SELECT GROUP_CONCAT(concat(a.last_name, ', ', a.first_name)
ORDER BY a.last_name, a.first_name SEPARATOR ' & ')
FROM authors a, author_books ab
WHERE ab.author_id = a.id
AND ab.book_id = b.id) author_name,
b.id book_id,
b.title,
(SELECT ROUND(AVG(s.score), 1) FROM scores s WHERE s.item_id = b.id AND media_type_id = 1) rating,
b.series,
b.part,
b.released,
g.id genre_id,
g.genre,
f.id format_id,
f.format,
CASE series
WHEN 'Standalone' THEN b.released
ELSE (SELECT MIN(bi.released) FROM books bi WHERE bi.series = b.series)
END series_started,
bc.user_id
FROM authors a,
books b,
genres g,
formats f,
book_collections bc
WHERE b.genre_id = g.id
AND b.format_id = f.id
AND b.id = bc.book_id"
);
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
DB::statement('DROP VIEW IF EXISTS book_collection_views');
}
}
Then just create your model like normal and then you can filter, sort and do whatever you like with the data.
The good thing about doing it this way is that you can tri, the SQL for performance and will run just one query in you controller/view.
I also keep my Eloquent eloquent so to say.
BookCollectionView::whereUserId($user->id)
->orderBy('author_name')
->orderBy('series_started')
->orderBy('part')
->orderBy('released')
->orderBy('title')
->get(),
'user' => $user]);
