Raw query to Eloquent

Question

I have polymorphic many-to-many relation between my projects and (members, teams) tables. Basically a member or a team can be assigned to a project. And members are grouped under teams.

I want to select projects which are assigned to the team that current member is in. I can get it by raw query like this:

DB::select(
  DB::raw('
    SELECT DISTINT project_id FROM projectvisibles PV 
      WHERE PV.projectvisible_type = "Team" 
        AND PV.projectvisible_id IN (
          SELECT team_id FROM member_team 
            WHERE member_id = :id)
  '), ['id' => $id]
);

How can i write this raw query in Eloquent form?

My DB:

Projects table:

id,
project_id,
name

Members Table:

id,
name

Teams Table:

id,
name

Member_Team Table: (members can be grouped under teams, via this table)

member_id,
team_id

ProjectVisibles Table: (projects can be assigned to members OR teams via this table (polymorphic many-to-many)

project_id,
projectvisible_id (member id or team id),
projectvisible_type (member or team)

Look at this http://stackoverflow.com/questions/16815551/how-to-do-this-in-laravel-subquery-where-in — Mateusz Nowak, Jun 29 '14 at 20:51

score 0 · Accepted Answer · answered Jun 29 '14 at 20:56

It will be something this kind

Db::table('projectvisibles')->where('projectvisible_type', '=', 'Team')->whereIn('projectvisible_id', function($query) use (& $id)
{
    $query->select('team_id')->from('member_team')->where('member_id', '=', $id);
})->get();

Raw query to Eloquent

1 Answers1