Retrieving a relationship after a join

Hi all,

I'm trying to wrap my head around joins. I have a model called components which has a many-to-many relationship with systems and therefore a pivot table components_systems. components also has a many-to-one relationship with manufacturers. So I'm able to pull the joined data by using the following...

    $comp_panel = DB::table('components')
      ->join('components_systems', 'components.id', '=', 'components_systems.component_id')
      ->where('system_id', '=', $system->id)
      ->where('component_category_id', '=', 1)
      ->select('components.*', 'components_systems.system_id', 'components_systems.quantity', 'components_systems.name')
      ->get();

The problem I'm having is I can't get the manufacturers name using the standard Laravel convention of...

@foreach($comp_panel as $panel)
    {{ $panel->manufacturer->name }}
@endforeach

Does this convention go away when I've joined another table's data?

jekinney

9 years ago

Level 47

Your query is not an Eloquent query but a db query. So no, you can't always access the data the same way as the 'laravel' way. It's actually the php object way btw.

First and foremost active record (pattern of accessing data that eloquent uses) doesn't use joins. Think of it as building multiple querys, but simple and (arguably) more efficient ones. Thus leveraging php to perform the work instead of MySQL. Pros and cons to this approach which is beyond this questions scope.

With out looking at more code, you need to utilize dd(); die and dump but better, to dump the data after your query and go through the results to ensure your getting the data you need/expect.

I see your join but only the pivot table not the other connected table?

nscherneck

9 years ago

Level 7

@jekinney Thanks for your reply. Correct, I don't need columns from the systems model. I'm querying for components that exist in the system and then iterating information about the component. dd() shows me just what I expect, all columns on the components table (for this particular system), and the three columns from the components_systems table.

Your explanation makes sense. Are you saying in order to access the manufacturer columns I need to re-write using Eloquent syntax rather than DB query builder?

To clarify, here are my database tables...

components

id
manufacturer_id
component_category_id
model
description
discontinued

components_systems

id
component_id
system_id
quantity
name

manufacturers

id
name

tylernathanreed

4 years ago

Level 14

You could do this using Eloquent:

$components = (new Component)->newQuery()
    ->where('component_category_id', '=', 1)
    ->whereHas('systems', function($query) use ($system) {
        $query->where('systems.id', '=', $system->id);
    });
    ->with('manufacturer')
    ->get();

If $system is a model, then you can do this instead:

$components = $system
    ->components()
    ->where('component_category_id', '=', 1)
    ->with('manufacturer')
    ->get();

In either case, the blade is the same:

@foreach($components as $component)
    {{ $component->manufacturer->name }}
@endforeach

Please or to participate in this conversation.