CODEWITHSUNDEEP
laravellaravel-4
DaveLV2
DaveLV2

Reputation: 358

Creating a search filter and one of the fields is from other table

So I`m trying to create a search filter.

And i have created this:

$data = Input::all();
$ads = new Ads();

if($data['description']) {
    $ads = $ads->where('description', 'LIKE', '%' . $data['description'] . '%');
}

if($data['min']) {
    $ads = $ads->where('price', '>', $data['min']);
}

if($data['max']) {
    $ads = $ads->where('price', '<', $data['max']);
}

And I have $data['city']. But the problem is that $ads dont have a city, it has userID and every user has its city. How would i do something like this but it would actualy search the user that owns this ad and check if the city is like the searched one.

I tried somthing like this:

    $resultAds = $ads;

    if($data['city']) {
        foreach($resultAds as $rAds) {
            if($rAds->compareCity($data['city'])) {
                $resultAds[] = $rAds;
            }
        }
    }

    $resultAds = $resultAds->paginate(12);

But that dosnt work. It gives this error: Symfony \ Component \ Debug \ Exception \ FatalErrorException (E_ERROR) Call to a member function compareCity() on a non-object And $rAds is for somereason boolean? I think thats becouse i havent done the ->get(); but i cannot do that becouse i would like to paginate the results.

Thanks for help.

So now my problem is to create a realationship to get city field out of users table.

Ads table

id
description
price
userID

User table

id
username
password
phone
city

I need to make a releationship so i could get all the ads with the requested city based on what city has the user with the userID taken from ads table.

Upvotes: 0

Views: 104

Answers (3)

vladsch
vladsch

Reputation: 606

If the ad to user is a belongsTo relationship, then you don't need a table or even to setup a relationship just to filter on city field from users table. You can do a join on the users table. I am assuming your user table is called users:

if($data['city']) {
    $ads = $ads->join('users', 'users.id', '=', 'city.userID')
       ->where('users.city','like','%'.$data['city'].'%');
}

The advantage of a join over whereHas is that it has no subquery that is executed against every row of the ads table, making it more efficient. Additionally, if you always join the users table, whether there is a filter on city or not, then you can pull the city from the users into the ads without having to do additional queries or with() relationships. Like this:

$ads = $ads->join('users', 'users.id', '=', 'city.userID')
if($data['city']) {
    $ads = $ads->where('users.city','like','%'.$data['city'].'%');
}

later:

$result = $ads->get(['ads.*', (new Illuminate\Database\Query\Expression('users.city')]);

or if you are going to paginate the result:

$result = $ads->paginate($pageSize, [
    'ads.*', 
    (new Illuminate\Database\Query\Expression('users.city'))
    ]);

every $ad that is in the $result will have a $ad->city field.

Upvotes: 1

DaveLV2
DaveLV2

Reputation: 358

So the answer is like this:

if($data['city']) {
    $ads = $ads->whereHas('users', function ($q) use($data) {
        $q->where('city','LIKE','%'.$data['city'].'%');
    });
}

This is the solution that worked. It is kinda taken from the other answer.

And I needed to add this in the model:

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

And now it works. Thanks everyone for help!

Upvotes: 0

user1669496
user1669496

Reputation: 33118

You would want to use whereHas as long as your relationship methods are setup correctly.

if($data['city'])
{
    $ads->whereHas('users', function($q) use ($data)
    {
        $q->where('city', $data['city']);
    });
}

Upvotes: 1

Related Questions