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

Question

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');
}

The Alpha · Accepted Answer · 2014-01-27 17:02:10Z

1

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.

edited Jan 27, 2014 at 17:02

answered Jan 27, 2014 at 16:28

The Alpha

147k30 gold badges294 silver badges313 bronze badges

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

11 Comments

Alvaro Over a year ago

That's exactly what I said I would like to avoid. Quite verbose.

The Alpha Over a year ago

Sorry! I didn't get it, can you explain ?

Alvaro Over a year ago

I posted it with whereHas insteas of with, but it still looking very large for such a simple and common operation.

Alvaro Over a year ago

Now it wouldn't work the other way around, if the relationship was defined as belongsTo and i want to query a connected table which has the hasMany relationship. Comments::with('Users')->where('name', 'Paul');

The Alpha Over a year ago

How is your relation declared ?

|

Collectives™ on Stack Overflow

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

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related