CODEWITHSUNDEEP
phplaravellaravel-5
V4n1ll4
V4n1ll4

Reputation: 6099

Laravel 5 select only relationship data on hasMany relationship

I am building an application that has projects and projects have plot_types.

I want to be able to check if a plot_type exists under the current project.

I have the following code:

$testResult = $project->with(['plotTypes' => function($query) use ($row) {
            $query->where('name', $row->plot_name);
        }])->first()

This produces the following MySQL:

select exists(select * from `projects` where exists (select * from `projects_plot_types` where `projects_plot_types`.`project_id` = `projects`.`id` and `name` = ?)) as `exists`

This SQL returns rows that are NOT related to the $project object. For example when I do dd($project) I get:

#attributes: array:11 [▼
    "id" => "4"
    "name" => "xxx"
    "number" => "1234"
    "builder" => "1"
    "overall_budget" => "3456.00"
    "start_date" => "2016-03-31"
    "end_date" => "2016-04-30"
    "created_date" => "2016-03-16 15:22:05"
    "updated_date" => "2016-03-16 15:22:07"
  ]

Yet, when I do dd($testResult); it gives;

#relations: array:1 [▼
"plotTypes" => Collection {#767 ▼
  #items: array:1 [▼
    0 => ProjectsPlotTypes {#770 ▼
      #table: "projects_plot_types"
      #fillable: array:2 [▶]
      +timestamps: false
      #connection: null
      #primaryKey: "id"
      #perPage: 15
      +incrementing: true
      #attributes: array:4 [▼
        "id" => "1"
        "project_id" => "1"
        "name" => "TYPE 1  - VENTILATION"
        "budget" => "324.67"
      ]

Notice, the project_id above shows 1. This is not related to the current project as the current project id is 4.

Why is this happening?

Upvotes: 0

Views: 2645

Answers (2)

patricus
patricus

Reputation: 62368

This is one of those potentially confusing parts of the ActiveRecord model. All your model instances contain the same methods used to retrieve model instances, so it easy think something should work one way when it really doesn't.

Calling $project->with(), this is the exact same as calling Project::with(). Even though you're calling with() on an instance of the project, it isn't going to restrict the loaded objects to only those related to your instance.

When you call $project->with(), the first thing it does is create a new query for all projects, and then adds in the eager loading. You then call first(), which just gets the first project record, and all its eager loaded objects.

To get the plot types for your specific project, you have a couple options.

  1. Just query the relationship. $project->plotTypes() gives you a base query for all the plot types associated with your project. You can add your constraints and get the records from there.

    $plotTypes = $project->plotTypes()->where('name', $row->plot_name)->get();
dd($plotTypes);

  2. Load the related plot types with constraints:

    // assume your project doesn't have any plottypes loaded yet
$project = Project::find(1);

// load the plottypes relation with constraints
$project->load(['plotTypes' => function($query) use ($row) {
    $query->where('name', $row->plot_name);
}]);
dd($project->plotTypes);

  3. Filter the already loaded Collection of related plot types. $project->plotTypes has all the plot types related to your project, but you can use the where() method on the Collection (different than the where() on the query) to filter through the records in the Collection.

    // assume your project already has all plotTypes loaded
$project = Project::with('plotTypes')->find(1);

// you just want to get a subset of those pre-loaded plottypes
$plotTypes = $project->plotTypes->where('name', $row->plot_name);
dd($plotTypes);

Upvotes: 1

oseintow
oseintow

Reputation: 7411

Use the whereHas method to do the filtering instead of the with

$testResult = $project->whereHas('plotTypes' => function($query) use ($row) {
        $query->where('name', $row->plot_name);
 })->with('plotTypes')->first();

And moreover do you want to get all the related records or only the first record?

if all then change the first() to get()

Hope this helps

Upvotes: 0

Related Questions