CODEWITHSUNDEEP
phplaravellaravel-4subquery
Alvaro
Alvaro

Reputation: 41595

Creating a query in Laravel by using `with` and `where`

I'm wondering it would be possible to add a where condition to a with.

Such as:

Comment::with('Users')->where('allowed', 'Y')->get();

I was trying to find a more simple way to make queries avoiding the whereHas method which looks quite verbose: 

$users = Comment::whereHas('users', function($q)
{
    $q->where('allowed', 'Y');

})->get();

The raw query I want internally to generate should be like so:

select * from comments, users
where users.id = comments.user_id and
users.allowed = 'Y'

I'm used to work with CakePHP in which this queries look very simple:

$this->Comments->find('all', array('Users.allowed' => 'Y'));

The relationships I have defined are:

//Comments.php
public function Users()
{
    return $this->belongsTo('Users');
}

//Users.php
public function Comments(){
    return $this->hasMany('Comments');
}

Upvotes: 0

Views: 387

Answers (1)

The Alpha
The Alpha

Reputation: 146191

You may try this

$users = User::with(array('comments' => function($q)
{
    $q->where('attachment', 1);

}))->get();

Update : Alternatively you may use a where clause in your relationship in your User model

// Relation for comments with attachment value 1
// and if hasMany relation is used
public function commentsWithAttachment()
{
    return $this->hasMany('Comment')->where('attachment', 1);
}

// Relation for all comments
// and if hasMany relation is used
public function comments()
{
    return $this->hasMany('Comment');
}

So, you can just use

// Comments with attachment value 1
User::with('commentsWithAttachment')->get();

// All comments
User::with('comments')->get();

Update : I think you want all comments with users where attachment is 1, if this what you want then it should be Comment not User

Comment::with('user')->where('attachment', 1)->get();

In this case your relation should be

public function user()
{
    return $this->belongsTo('User'); // if model name is User
}

Because one comment belongs to only one user.

Upvotes: 1

Related Questions