Level 53
@giwrgos Use joins. And format your code.
1 like
Feb 27, 2015
4
Level 2
How to make one array of results using 3 tables
Hello everyone, I'm looking for a help if anyone can help me. Basically I'm having 3 tables in my database, the one is called category the second property and the third one is used to relate the category with property and is called property_relationship. The table property_relationship has the following fields. id, category_id, sub_category_id,property_id.
From here I'm collecting all the available properties_ids of the given category_id using the following lines: $propertiesid_Array = Properties_relation::where('category_id', '=', $category->category_id)->lists('property_id');
and then I'm doing the following to collect all the properties $properties = $this->property->whereIn('property_id', $propertiesid_Array)->whereNull("deleted_at")->orderBy('updated_at', 'DESC')->paginate(20);
This is working fine. What I'm trying now to to is to add an extra field inside the $properties instance that will contain the name of the sub_category and then using the same loop that I have into the view to print this field.
for example we have the following data
category: ["id"=>1,"category_title"=>"Category A","type"=>"parent"], ["id"=>2,"category_title"=>"Sub Category A","type"=>"sub"]
property: ["id"=>1,"property_title"=>"Property A"],
property_relationship: ["id"=>1,"category_id"=>1,"sub_category_id"=>2,"property_id"=>1]
So the $properties variable I want to be a list with all the available property_relationship having the following attributes ["id"=>1,"category_title"=>"Sub Category A","property_title"=>"Property A" ]
Does anyone knows how I can do it?
Thank you
Level 2
I came out with this Query "select property.property_id, property_title, category_title from property LEFT JOIN property_relationship ON property_relationship.property_id = property.property_id INNER JOIN category ON category_id = sub_category_id AND category_id = '3'" and is working, but how i can do this query with Eloquent ORM? This is the help that i need, how to write it. I want only the properties that exist in the property_relationship for the given category, not containing null of deleted_at (whereNull) order by updated_at of the property table and pagination 20
Level 53
@giwrgos Assuming you want all properties of a single category AND you have belongsToMany relationships defined for the two, this is all you need to do it in eloquent:
$category = Category::find(3);
$category->properties; // collection of related properties
Btw given what you said above, you want 2 inner join not left join
Level 2
Ok i found the solution after long time.
inside the model of the property i made the following methods
public function category_title($id) {
return Category::where("category_id","=",$id)->select("category_title")->first();
}
public function category()
{
return $this->hasOne('Properties_relation');
}
inside the Category model this
public function relationships() {
return $this->hasMany('Properties_relation');
}
and it work. Thanks for the help
1 like
Please or to participate in this conversation.