Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

peanut's avatar

Select columns from pivot table?

I have three tables:

  • product_lines (id, name)
  • project_posts (id, project_id, title)
  • project_products (id, project_post_id, product_line_id)

So it's a many to many relationship using project_products as the pivot table. This is how I have my relationship set up in the Post model:

public function products()
{
 return $this->belongsToMany('\Duplicolor\Models\Product\Line', 'project_products', 'project_post_id', 'product_line_id');
}

Then I run this query on a Post object:

public function linked_products()
{
 return $this->products()
    ->get(array('name'));
}

I get this data back:

array (size=1)
  0 => 
    array (size=2)
      'name' => string 'Headlight Restoration Kit' (length=25)
      'pivot' => 
        array (size=2)
          'project_post_id' => int 57
          'product_line_id' => int 5

How do I also select the ID in the pivot table? And what if they have the same column names for another column I want to select? Do I just have to write the table name down like : product_lines.id ? If not, it throws an ambiguous error. I didn't know if there was a better way of doing so.

Thanks.

0 likes
10 replies
jostrander's avatar

In your first code example you need to use the "with" method on belongsTo():

public function products()
{
    return $this->belongsToMany('\Duplicolor\Models\Product\Line', 'project_products', 'project_post_id', 'product_line_id')->withPivot('id');
}

*Edited to use withPivot() instead of with()

1 like
peanut's avatar

This is the error I receive when doing so:

Call to undefined method Illuminate\Database\Query\Builder::id()
jostrander's avatar

What file is that coming from? are you attempting to access the ID as a function like so:

$linked_products->pivot->id();

I'm fairly certain you need to access the property as such:

$linked_products->pivot->id;
mrterryh's avatar

I'm on mobile and don't have time to read your post, so I'm going to provide an answer based on your title.

Check out the withPivot() method.

jostrander's avatar

Ah! I forgot the pivot part, even after looking at the documentation. I'll update my post accordingly.

peanut's avatar

I'm just running:

$post = Post::find( $id );

... then I'm going from there by running:

@foreach($post->linked_products() as $product)
$product->pivot->id
@endforeach

$product->pivot->id doesn't pull any data.. most likely because it's not even returning in the $post->linked_products() part because only those two columns are being returned.. not the ID.. Which I need the ID.

Qubicle's avatar

Try running this query instead

public function linked_products()
{
    return $this->products()
        ->addSelect('product_lines.id')
        ->get(array('name'));
}

60% chance you will get what you need (usually works for me)

peanut's avatar

@Qubicle I already knew that though.. I wanted to avoid product_lines.id.. I know I can just type the table name in the get() array.. I didn't know if this was the best way of going about it.

Please or to participate in this conversation.