Level 5
So for your model did you define a relation between a user and a tenant_preferences if not that's your first step in solving your query issue. I'm guessing every user has a preference ? you have to first define that relationship.
May 1, 2018
5
Level 2
Laravel combine multiple collections into one query
I'm wondering if this is possible. I have 3 models. Users TenantPreferances PropertyAdverts
I'm trying to find out if I can do a query like so.
Find all tenants, whose preferences, match the currently signed in users properties.
The 3 databases are like so
User Model
Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->string('email')->unique();
$table->string('userType');
$table->string('password');
$table->rememberToken();
$table->timestamps();
});
PropertyAdverts
Schema::create('property_adverts', function (Blueprint $table) {
$table->increments('id');
$table->string("photo");
$table->string('address');
$table->string('county');
$table->string('town');
$table->string('type');
$table->string('rent');
$table->string('date');
$table->string('bedrooms');
$table->string('bathrooms');
$table->string('furnished');
$table->longText('description');
$table->integer('user_id'); //Landlord ID
$table->timestamps();
});
Tenant Preferances
Schema::create('tenant_preferances', function (Blueprint $table) {
$table->increments('id');
$table->string('county');
$table->string('type');
$table->string('rent');
$table->string('bedrooms');
$table->string('bathrooms');
$table->boolean('status')->default('0');
$table->integer('user_id'); //Tenant ID
$table->timestamps();
});
Level 1
First I want to understand, a User is basically a tenant as well. Right?
So I guess the table to be clearer should be named user_preferences
Correct me if I didn't get it right.
Secondly, IMHO you should put your relationship in your table users to have user_preference_id cause it makes more sense that a user have a preference and that a preference can be applied on more than one user.
Also by that, you will be able to fetch all users with similar preferences by fetching all users who have user preference id similar to the signed in user preference id, for example:
// This will fetch the signed in user preference id
$signed_in_user_preference_id = Auth::user()->user_preference_id ;
// Fetch users with similar preferences to the signed in user preferences
$users = DB::table('users')->where('user_preference_id', $signed_in_user_preference_id )->get();
References:
Level 2
@alielkhateeb @Mandrizzy The user can be either user/tenant, but only a tenant can set preferences. Only a landlord can post a property. I want a landlord to do a search, and all landlords with preferences => then the landlords active property are diplayed
Level 1
@andremac96 so basically a landlord won't have preferences, and a user won't be able to post PropertyAdverts
I still feel I am missing something, users of the system are tenants, landlords or both?
Well, I'll answer for both situations:
If users can be landlords and tenants at the same time then Users table can have a flag column, which will be true if the user is a landlord and a tenant, false if the user is just a tenant. For instance a column called is_landlord
If users cannot be landlords and tenants at the same time, then the users table can have an enum column called type for example and it should hold the type of the user ''landlord'' or ''tenant''. With this a user will be either a landlord or a tenant, cannot be both.
Level 53
Unless needed, I would remove the userType = tenant / landlord and make it so that a user can be a landlord and tenant at the same time (I can own multiple houses, yet I'm also renting one myself)
Having an advert will make me a landlord, and having preferences makes me a (potential) tenant.
The DB structure is fine:
- A user can have multiple property_adverts (advert belongsTo a user)
- A user can also have multiple preferences for searching new housing.
So far so good.... looks like a real world scenario to me.
A new user will be just that.... a user.
- When you setup a preference, you become a tenant
- When you setup a property advert, you become a landlord.
No field on the user table needed for that (unless you need logic to see if someone is a landlord before they setup their first property advert).
Now for the answer to your question:
UserModel
public function scopeTenants($q)
{
return $q->has('preferences');
}
public function scopeLandlords($q)
{
return $q->has('adverts');
}
public function adverts()
{
// if a user can only have 1 advert, change this to hasOne and the method name to advert()
return $this->hasMany(PropertyAdvert::class);
}
public function preferences()
{
// if a user can only have 1 preference, change this to hasOne and the method name to preference()
return $this->hasMany(TenantPreference::class);
}
// if you have property adverts, you are a landlord
public function getIsLandlordAttribute()
{
return $this->adverts->count() > 0;
}
// if you have preferences set, you are a tenant
public function getIsTenantAttribute()
{
return $this->preferences->count() > 0;
}
public function getMatchingAdverts()
{
$matches = collect();
// it not a tenant, return empty collection
if(!$this->is_tenant) return $matches;
// this will check multiple preferences if needed
foreach($this->preferences as $preference) {
$adverts = PropertyAdvert::where('county' , $preference->county)
->where('type' , $preference->type)
->where('rent', '<=', $preference->rent)
->where('bedrooms', '>=', $preference->bedrooms)
->where('bathrooms', '>=', $preference->bathrooms)
->get();
$matches = $matches->merge($adverts);
}
// and ofcourse, only return the unique matches.
return $matches->unique();
}
/*
If a user can only have one preference, change the names of the above relationship methods and use this:
public function getMatchingAdverts()
{
// it not a tenant, return empty collection
if(!$this->is_tenant) return collect();
return PropertyAdvert::where('county' , $this->preference->county)
->where('type' , $this->preference->type)
->where('rent', '<=', $this->preference->rent)
->where('bedrooms', '>=', $this->preference->bedrooms)
->where('bathrooms', '>=', $this->preference->bathrooms)
->get();
}
*/
Controller:
$user = auth()->user();
if($user->is_tenant) {
$adverts = $user->getMatchingAdverts();
dd($adverts);
}
$landlords = User::landlords()->get();
$tenants = User::tenants()->get();
Please or to participate in this conversation.