Using Pluck to return columns from many to many tables with eloquent

Ramnik, pluck() retrieves all of the values for a given key. If you pass 2 values in the pluck(), it will return key => value pairs. So in your example you can retrive both columns like this

public function getMaterialBySection($section){

        $filteredMaterials = Section::with('materials')
              ->where('id', $section)
              ->get()
              ->pluck('materials.*.name', 'materials.*.id');

        return json_encode($filteredMaterials); 
}

$data = json_decode(getMaterialBySection($section));

foreach($data as $id => $name){

} 

Thanks @gitwithravish But my question is the pluck method not returning data.

pluck('materials.*.id', 'materials.*.name')

@ramniksingh

How about something simple like this,

public function getMaterialBySection($section){

        $filteredMaterials = Section::with([
		'materials' => function($query){
		    $query->select('id','name');
                 }])
              ->where('id', $section)
              ->first();

	return json_decode($filteredMaterials->materials ?? []);
}

Thanks @gitwithravish . It worked.

ublic function getMaterialBySection($section) {
        $filteredMaterials = Section::with([
            'materials' => function($query){
                $query->select('materials.id','name');
                     }])
                  ->where('id', $section)
                  ->first();
        
        return json_encode($filteredMaterials->materials);
    }  

Can we further narrow down list to only id & name fields so that it can be fetched in my ajax data as key=>value pair?

@ramniksingh

Well you can access it with javascript like this.

for(let material of response.data){
	console.log(material.id);
	console.log(material.name);
}

But if you want to go with key value pairs, then do this,

public function getMaterialBySection($section){

        $filteredMaterials = Section::with([
		'materials' => function($query){
		    $query->select('id','name');
                 }])
              ->where('id', $section)
              ->firstOrFail();

	return json_decode($filteredMaterials->materials ->pluck('name','id'));
}

public function getMaterialBySection($section){

        $filteredMaterials = Section::with([
		'materials' => function($query){
		    $query->select('id','name');
                 }])
              ->where('id', $section)
              ->first();

	return json_decode($filteredMaterials->materials ?? []);
}

This returns error

same id exists in both tables .

public function getMaterialBySection($section){

    $filteredMaterials = Section::with([
	'materials' => function($query){
	    $query->select('id','name');
             }])
          ->where('sections.id', $section)
          ->first();

return json_decode($filteredMaterials->materials ?? []);

}

public function getMaterialBySection($section){
        $filteredMaterials = Section::with('materials')->where('id', $section)->get()->pluck('materials');
        return $filteredMaterials; 
    }

Above code works but I need a way to extract just key value pairs from selected columns.

My java script is like this :

jQuery('select[name="section_id"]').on('change',function(){
               var section = jQuery(this).val();
               if(section)
               {
                  jQuery.ajax({
                     url : 'filtermaterial/getmaterial/' +section,
                     type : "GET",
                     dataType : "json",
                     success:function(data)
                     {
                        console.log(data);
                        jQuery('select[name="material_id"]').empty();
                        jQuery.each(data, function(key,value){
                           $('select[name="material_id"]').append('<option value="'+ key +'">'+ value +'</option>');
                        });
                     }
                  });
               }
               else
               {
                  $('select[name="material_id"]').empty();
               }
            });

@ramniksingh I would keep it simple like this :)

// you can pass section object or just section id in the arguments
public function getMaterialBySection($id){

    return Material::where('section_id',$id)
                   ->get()
                   ->pluck('name','id')
                   ->toJson;
}	      

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'section_id' in 'where clause' (SQL: select * from materials where section_id = 1)

well i just assumed that the foreign key for section in your materials table would be section_id. You change it to whatever it is

there is no column named section_id in materials. material & section models are connected in many to many relation through a pivot table named 'material_section' .

oh damn i forgot. Sorry. Then you can go with the earlier solution.

php

$filteredMaterials = Section::with([
	'materials' => function($query){
	    $query->select('id','name');
             }])
          ->where('sections.id', $section)
          ->first();

return json_decode($filteredMaterials->materials ?? []);

js

jQuery('select[name="section_id"]').on('change',function(){
               var section = jQuery(this).val();
               if(section)
               {
                  jQuery.ajax({
                     url : 'filtermaterial/getmaterial/' +section,
                     type : "GET",
                     dataType : "json",
                     success:function(data)
                     {
                        console.log(data);
                        jQuery('select[name="material_id"]').empty();
                        jQuery.each(data, function(index,material){
                           $('select[name="material_id"]').append('<option value="'+ material.id +'">'+ material.value +'</option>');
                        });
                     }
                  });
               }
               else
               {
                  $('select[name="material_id"]').empty();
               }
            });

Thanks a lot. It worked :)