Adding a raw Order By to pivot table

Posted 5 months ago by timgavin

I'm curating my posts table on the home page to only show posts I've handpicked.

When I select a post for the home page, its ID goes into a posts_homepage table, which is basically a pivot table. I select the post's id (post_id) from posts_homepage and then get the posts from the posts table.

This is working just fine, however, now I want to put them in a particular order, so I've added an order_by field to posts_homepage. I'm having a difficult time figuring out how to get the result in the order I want, nothing's working; I'm just getting them in the default order.

TABLE posts_homepage
post_id    INT 10 UNSIGNED
order_by INT 10 UNSIGNED

Current query...

$posts = Post::latest('published_at')
    ->whereHas('user', function ($query) {
        $query->where('verified', 1);
    })
    ->whereRaw("posts.id IN (SELECT post_id FROM posts_homepage)")
    ->whereRaw("posts.status = 'published'")
    ->paginate(30);

I've tried removing latest('published_at') and inserting ->orderByRaw('ORDER BY posts_homepage.order_by ASC'), but that didn't make a difference or I'd receive an error message Unknown column 'posts_homepage.order_by' in 'order clause'

I also tried ->whereRaw("posts.id IN (SELECT post_id FROM posts_homepage ORDER BY order_by ASC)") but that didn't work either. I've tried a few other options as well, but can't get them returned based on the order_by field

Any suggestions?

Please sign in or create an account to participate in this conversation.