Query onOfMany I want to build a query and for now i have no clue how to go about it, maybe someone here can give me a hint?
I have a user and a posts model.
So a user has many posts.
Now i want to query all posts but only the latest one per user and only posts not older than 7 days.
Add the appropriate relationship to the User model:
// User model
public function latestPost(): HasOne
{
return $this->hasOne(Post::class)->latestOfMany();
}
Then query for all Users along with their latestPost - subject to the given constraints:
User::query()
->with(['latestPost' => fn ($builder) -> $builder->where('created_at', >, today()->subDays(7))
->get()
You can use whereHas to constraint the results only to users who have a latestPost within the past 7 days.
@tykus hi thx for that.
To query the users with their latest posts is not my issue.
Im searching for a way to query posts.
Like:
Post::where('created_at', >, today()->subDays(7))-> subquery only one per user
@mc_gee what do you mean subquery only one per user - what does the sub-query do here?
@tykus a user can write mutliple posts in the last week.
But i want to get only the latest of them.
But i dont want to query users.
@mc_gee okay, if you have a MySQL version that supports window functions, this would be the one way to achieve the desired result:
WITH latest_posts AS (
SELECT
posts.*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS position
FROM posts
WHERE created_at > '2022-09-01'
)
SELECT * FROM latest_posts WHERE position = 1;
Please sign in or create an account to participate in this conversation.
