Converting raw SQL to Eloquent

Your query should be pretty simple if you have models for all those tables, so just using where() clauses and relationships you will be good to go..

Do you have User, UserPhoto and UserProfile models?

Share the structure of your database and what is the connection between the tables. What have you tried?

I've User, UserPhoto and UserProfile models.

User model have two relations profile and photos

//Relation to user_profiles table
public function profile()
{
      return $this->belongsTo(UserProfile::class, 'id', 'user_id');
}

//Relation to user_photos table
public function photos()
{
   return $this->hasMany(UserPhoto::class, 'user_id', 'id');
}
        

I wrote like this:

$users = User::whereHas('photos')
            ->where(['role_id' => $roleId, 'status' => User::STATUS_APPROVED]);

if (!empty($request->input('gender'))) 
{
      $gender = $request->gender;
       $users->whereHas('profile', function ($query) use ($roleId, $gender) {
             $query->where('gender', '=', $gender);
       });
            
     $users->orWhereHas('profile', function ($query) use ($roleId, $gender) {
                $query->where('interested_in', '=', $roleId)
                    ->where('gender', '=', $gender);
            })->whereHas('photos');
}

if (empty($request->all())) 
{
$users->orWhereHas('profile', function ($query) use ($roleId) {
      $query->where('interested_in', '=', $roleId);
})->whereHas('photos');
}
$users = $users->paginate(6);

users table has fields

id, name, role_id, status, ....

user_profiles table has fields

id, user_id,gender,location,state,country,interested_in,...

user_photos table has fields

id, user_id, image, is_featured,...

For example, I want to get all users with role_id = 2 or interested_in = 2 and gender = 2 and status = 2.

@ajithlal first of all, I believe the relationship to the profile can be this way:

public function profile()
{
    return $this->hasOne(UserProfile::class);
}

Slightly modified version of all of your code:

$users = User::has('photos')
    ->when( ! empty($request->input('gender')), function($query) use ($roleId, $gender) {
        return $query->whereHas('profile', function($query) use ($roleId, $gender) {
            $query->where('interested_in', $roleId)
                 ->orWhere('gender', $gender);
            });
    })
    ->where(['role_id' => $roleId, 'status' => User::STATUS_APPROVED])
   ->with(['profile', 'photos']); // last line eager loads the relationship

Play around with it, but you don't need the conditions, you can use when() which will execute the callback only if the condition is satisfied.

I will try with this. BTW what is the difference between hasOne and belongsTo while writing relationship in model ?.

@ajithlal more of a way to interpret that, like when you read it makes more sense:

A user has one profile, but a profile belongs to a User.

So you have a user_id in the profiles table, that's why a Profile belongs to a User.. but the user has one profile. Other than that I think it accomplishes the same thing if it works :)

@nakov I've tried the given code. but It's not returning the expected result. I want the query should be like this

SELECT * FROM users LEFT JOIN user_profiles ON users.id = user_profiles.user_id WHERE (users.role_id = 1 OR user_profiles.user_id = 1) AND user_profiles.gender = 2 AND users.status = 1

I want to apply this WHERE (users.role_id = 1 OR user_profiles.user_id = 1) condition in my query.

@ajithlal that's why I said here is a code and play around with it, unfortunately I don't have a codebase, so any code that I give you will be not tested and most probably wrong.

@nakov how can I write this query

SELECT * FROM users LEFT JOIN user_profiles ON users.id = user_profiles.user_id WHERE (users.role_id = 1 OR user_profiles.interested_in = 1) AND user_profiles.gender = 2 AND users.status = 1

to eloquent?

@ajithlal okay, don't want to waste time learning right? :)

Here is my try:

User::with('profile')
    ->whereHas('profile', function($query) {
         $query->where('users.role_id', 1)
             ->orWhere('user_id', 1)
             ->where('gender', 2);
         })
    ->where('status', 1)
    ->get();

@nakov It worked. Thanks.

I'm not aware that we can write where condition inside relation like this $query->where('users.role_id', 1).