laravel database query with multiple relationships

Question

I would like to load posts from users of a certain age from the database. The user can save the minimum(users->profile->min_age) and maximum age(users->profile->max_age) in his profile.

The age for the logged-in user I receive with the following:

Auth::user()->birthday->diff(Carbon::now())->format('%y');

However, every time I need the age of the user who has created the post. I can not get the age in the same query ... I'm confused

this is my current query, but without age:

Post::where('type', 7)->whereIn('gender', [0,1])->where('user_id', '!=' , Auth::user()->id)
            ->whereNotExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('users_finder')
                    ->whereColumn('users_finder.post_id', 'posts.id')
                    ->where('users_finder.user_id', auth()->id());
            })->first();

Post.php

public function user() {
    return $this->belongsTo('App\User', 'user_id');
}

User.php

public function posts()
    {
      return $this->hasMany('App\Post');
    }

public function profile()
    {
      return $this->hasOne('App\Profile');
    }

profile.php

public function users()
    {
    return $this->belongsTo('App\User');
    }

Does that work with where or do I have to use a join for this? How can I load the corresponding posts?

That is a very complex query for something that could maybe be pretty simple - do you have a relationship between post and user? — Watercayman
– Watercayman, Commented Sep 1, 2019 at 19:04
yes, one to many. The user can have many posts. I will add the relationship to the question — slickness
– slickness, Commented Sep 1, 2019 at 19:10

Namoshek · Accepted Answer · 2019-09-01 19:10:08Z

1

You can add this constraint to your posts query:

// TODO: set these from current user
$minAge = 25;
$maxAge = 28;

$posts = Post::query()
    ->where('type', 7)
    ->where('user_id', '!=' , auth()->id())
    ->whereIn('gender', [0,1])
    ->whereNotExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('users_finder')
            ->whereColumn('users_finder.post_id', 'posts.id')
            ->where('users_finder.user_id', auth()->id());
    })
    ->whereHas('user', function ($query) use ($minAge, $maxAge) {
        $query->whereRaw('timestampdiff(YEAR, birthday, CURRENT_TIMESTAMP) >= ?', [$minAge])
            ->whereRaw('timestampdiff(YEAR, birthday, CURRENT_TIMESTAMP) <= ?', [$maxAge]);
    })
    ->first();

Note: this answer assumes you are using MySql

I'm not sure the YEAR difference of MySql is suitable for this here, as it counts 2 years + 1 day of difference already as 3 years. But it should give you a direction to look for.

answered Sep 1, 2019 at 19:10

Namoshek

6,5562 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

slickness Over a year ago

'timestampdiff(YEAR, birthday, CURRENT_TIMESTAMP) It's the biggest challenge i can't handle. can you also write this in a laravel query?

Namoshek Over a year ago

No, you cannot. You only have a birthdate on your hand, but you need the age. This calculation can either be performed in-memory after fetching all posts with all their auhtors (which would be way too slow); or you can perform it within the database system during the query, which is much faster. Unfortunately, Laravel has no built-in methods for this job. So you need a raw query. But it is quite simple, isn't it? Or do you not understand something about it? I'm happy to explain it to you.

Collectives™ on Stack Overflow

laravel database query with multiple relationships

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related