Join many-to-many lost after adding orderBy

The issue you're encountering is related to how Eloquent handles eager loading (with) in combination with joins and ordering. When you use a join, you're essentially flattening the result set, which can interfere with Eloquent's ability to correctly map related models.

To solve this, you can separate the join logic from the eager loading logic. Instead of using a join, you can use Eloquent's relationship methods to maintain the integrity of the relationships. Here's how you can modify your query:

Post::with(['author:id,name', 'tags:id,name'])
    ->select('id', 'title', 'author_id', 'created_at')
    ->latest()
    ->simplePaginate(10);

Explanation:

1. Eager Loading: By using with(['author:id,name', 'tags:id,name']), you're telling Eloquent to load the author and tags relationships. This ensures that the related data is fetched in a separate query, preserving the relationship data.

2. Select Statement: Ensure that you include the id of the posts table in your select statement. This is crucial because Eloquent needs the primary key to map the relationships correctly.

3. Ordering: Use latest() to order the posts by the created_at column. This method is a shorthand for orderBy('created_at', 'desc').

By structuring your query this way, you maintain the integrity of the relationships while still applying the desired ordering. This approach leverages Eloquent's capabilities to handle relationships more effectively than a raw join would.