OrderBy deeply nested relationship

I have three tables.

posts
	id
	postable_type
	postable_id
	
posts_translations
	id
	post_id
	title
	locale	

views
	id
	viewable_type
	viewable_id

What I want to achieve is order posts by views count.

$query = \App\Models\Post::leftJoin('post_translations', function ($join) {
        $join
            ->on('posts.id', 'post_translations.post_id')
            ->where('post_translations.locale', app()->getLocale());
    })
        ->select('posts.*')
        ->selectSub(
            \App\Models\View::select(DB::raw('count(*)'))
                ->where('viewable_type', \App\Models\PostTranslation::class)
                ->where('viewable_id', 'post_translations.id')
            , 'view_count'
        )
        ->orderByDesc('view_count');

This will produce mysql query:

SELECT 
	`posts`.*,
	(
		SELECT COUNT(*)
		FROM `views`
		WHERE `viewable_type` = 'App\Models\PostTranslation' AND `viewable_id` = post_translations.id
	) AS `view_count`
FROM
	`posts`
LEFT JOIN 
	`post_translations`
	 ON
	 	`posts`.`id` = `post_translations`.`post_id`
	 AND
	 	`post_translations`.`locale` = 'en'
ORDER BY `view_count` DESC

When I run the query from mysql command line I get:

+----+-----------------------+-------------+------------+
| id | postable_type         | postable_id | view_count |
+----+-----------------------+-------------+------------+
| 26 | App\Models\Video\Item |      305478 |         11 |
| 83 | App\Models\Video\Item |      313500 |          4 |
|  4 | App\Models\Video\Item |      307388 |          1 |
| 38 | App\Models\Video\Item |      310604 |          0 |
| 41 | App\Models\Video\Item |      312532 |          0 |
| 40 | App\Models\Video\Item |      311555 |          0 |
| 39 | App\Models\Video\Item |      311269 |          0 |

Which is the correct order, but when I get() collection from this query order is incorrect

dd($query->limit(2)->get()->toArray());

array:2 [
  0 => array:12 [
    "id" => 1
    "postable_type" => "App\Models\Article"
    "postable_id" => 10
    "view_count" => 0    
  ]
  1 => array:12 [
    "id" => 26
    "postable_type" => "App\Models\Video\Item"
    "postable_id" => 305478
    "view_count" => 0    
  ]
]

And output shows incorect view_count. e.g. for postable_id 305478 it should be 11

What is wrong here? What am I missing?

Tray2

5 years ago

Level 73

Eloquent is great but it can be very tricky to get right when the query gets more complex.

What I usually do is create a database view in a migration then run eloquent against that.

Here is an example from one of my applications

  DB::statement(
                "CREATE OR REPLACE VIEW book_views AS
                 SELECT
                    (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
                FROM books b,
                     genres g,
                     formats f
                WHERE b.genre_id = g.id
                AND   b.format_id = f.id"
            );

Then I query it like so

 $books = BookView::orderBy('author_name')
                         ->orderBy('series_started')
                         ->orderBy('part')
                         ->orderBy('released')
                         ->orderBy('title')
                         ->get();

borman

5 years ago

Best Answer

Level 2

The problem was in query builder,

->where('viewable_id', 'post_translations.id')

Has to be changed to

->whereColumn('viewable_id', 'post_translations.id')

In the first one I am comparing against string no actual column

Please or to participate in this conversation.