php - get *related* linked models in laravel eloquent instead of raw SQL ← (PHP, Laravel)

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:

1. Get companies who are in the same project ('product category') as you
2. Find the other project id's which are linked to those companies
3. 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.

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.