CODEWITHSUNDEEP
databaselaraveljoineloquent
Mike_NotGuilty
Mike_NotGuilty

Reputation: 2395

Laravel Eloquent - Select with join where column text like column text from other table

I want to select users from my users table and return a boolean if they live in a city, that exists in my cities table in my database.

The problem is, that I can't use the ID of the city in the users table, because I get the users list from an external source.

On a normal SQL Query I would JOIN the cities table (users.city LIKE cities.name) and then add a column to the result with true false.

But how is this possible in Laravel eloquent where I have my Model User and my model City. Both models have the column city as a text. Of course the query should be as performant as possible and I don't want for each row a separate query.

Thanks

Upvotes: 1

Views: 1711

Answers (2)

Lim Kean Phang
Lim Kean Phang

Reputation: 501

User::join('City','user.city','=','city.name')
->select(user.city,DB::raw('(CASE WHEN user.city <> NULL THEN 1 ELSE 0 END) AS is_user')
  )

Upvotes: 2

Milan Maharjan
Milan Maharjan

Reputation: 4236

In you user model you can add new accessor property

protected $appends = ['lives_in_city'];

public function getLivesInCityAttribute(){
    $hasCity = City::where('city', 'like', $this->city)->first();
    return $hasCity? true: false;
}

Now your user object will have new field lives_in_city which will return the boolean value.

Upvotes: -1

Related Questions