php - get *related* linked models in laravel eloquent instead of raw SQL
Get the solution ↓↓↓I'm trying to get 'related' linked models by querying a link table, named company_projects which holds (as you expect) the id's of companies and projects (projects are kind of product-categories).
In this case, the used flow to determine a related project is:
- Get companies who are in the same project ('product category') as you
- Find the other project id's which are linked to those companies
- Get the info of the linked projects fetched by last step
What i'm trying to do is already functional in the following raw query:
SELECT
*
FROM
projects
WHERE
projects.id IN
(
SELECT cp1.project_id
FROM company_projects cp1
WHERE cp1.company_id IN
(
SELECT cp1.company_id
FROM projects p
LEFT JOIN company_projects cp2 ON cp2.project_id = p.id
WHERE p.id = X AND cp2.company_id != Y
)
)
AND projects.id != X
X = ID of current project ('product category')
Y = ID of current 'user' (company)
But my real question is, how to do this elegantly in Laravel Eloquent (currently v4.2). I tried it, but I have no luck so far...
Update:
I should note that I do have experience using Eloquent and Models through multiple projects, but for some reason I just fail with this specific query. So was hoping to see an explained solution. It is a possibility that I'm thinking in the wrong way and that the answer is relatively easy.
Answer
Solution:
You will need to utilize Eloquent relationships in order to achieve this. (Note that I am linking to the 4.2 docs as that is what you are using, but I would highly suggest upgrading Laravel to 5.1)
I am assuming you have a 'Company' and 'Project' model already. Inside each of those models, you need to a define a method that references its relationship to the other model. Based on your description, it sounds like the two have a Many to Many relationship, meaning that a company can have many projects and a project can also belong to many companies. You already have a database table linking the two. In the Eloquent ORM this linking table is called a pivot table. When you define your relationships in the model, you will need to pass the name of that pivot table as your second argument. Here's how it could look for you.
Company model:
class Company extends Model
{
/**
* Get the projects attached to a Comapny. Many To Many relationship.
*/
public function projects()
{
return $this->belongsToMany('Project','company_projects');
}
}
Project model:
class Project extends Model
{
/**
* Get the companies this project belongs to. Many To Many relationship.
*/
public function companies()
{
return $this->belongsToMany('Company','company_projects');
}
}
If your models have these relationships defined, then you can easily reference them in your views and elsewhere. For example, if you wanted to find all of the projects that belong to a company with an ID of 1234, you could do this:
$company = Company::find(1234);
$projects = $company->projects;
Even better, you can utilize something called eager loading, which will reduce your data lookup to a single line (this is mainly useful when passing data to views and you will be looping over related models in the view). So those statements above could be rewritten as:
$company = Company::with('projects')->find(123);
This will return your Company model with all its related products as a property. Note that eager loading can even be nested with a dot notation. This means that you can find all the models that link to your main model, and then all the models for those models, and so on and so forth.
With all of this in mind, let's look at what you specifically want to accomplish.
Let us assume that this method occurs in a Controller that is being passed a project id from the route.
public function showCompaniesAndProjects($id)
{
//Get all companies in the same project as you
//Use eager loading to grab the projects of all THOSE companies
//Result will be a Project Object with all Companies
//(and those projects) as Eloquent Collection
$companies = Project::with('companies.projects')->find($id);
//do something with that data, maybe pass it to a view
return view('companiesView')->with('companies',$companies);
}
After defining your relations in your models, you can accomplish that whole query in a single line.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: uncaught error: call to undefined function mysqli_connect()
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.