CODEWITHSUNDEEP
mysqllaraveleloquent
Alphy Gacheru
Alphy Gacheru

Reputation: 657

How to query multiple rows in a column of a table

Below is my controller and when I dd($types_id); I get an array of two ids. Now the problem is when I pass the variable in the where method to pluck the names of the types associated with the ids, it only fetches the name of the first id instead of the two ids. What may I be doing wrong?

/*Get Type List*/

    public function getTypeList(Request $request)
    
        {
        
        
            $types_id = DB::table("vehicles")
                ->where("condition_id",1)
                ->pluck("type_id","id")->unique();
           //dd($types_id);
        
           $types = DB::table("type_ids")
                ->where("type_id", $types_id)
                ->pluck("name");
          //dd($types);
        
          return response()->json($types);
      }

Upvotes: 0

Views: 47

Answers (2)

berkay kılıç
berkay kılıç

Reputation: 160

Problem 1 - You should be using whereIn for the second query.

Problem 2 - Without selecting any column from table and getting all columns for the collection is bad optimization for the fetching operation. You should be using distinct for the query instead of filtering collection by unique method.

public function getTypeList(Request $request)    
{      
    $types_id = DB::table("vehicles")
        ->where('condition_id',1)
        ->select("type_id")
        ->distinct("type_id")->get()
        ->pluck("type_id");
        
    $types = DB::table("type_ids")
        ->select('name')
        ->whereIn("type_id", $types_id)->get()
        ->pluck("name");
        
    return response()->json($types);
}

Upvotes: 1

Donkarnash
Donkarnash

Reputation: 12835

Just change where to whereIn

public function getTypeList(Request $request)    
{      
    $types_id = DB::table("vehicles")
        ->where("condition_id",1)
        ->pluck("type_id","id")->unique();
    //dd($types_id);
        
    $types = DB::table("type_ids")
        ->whereIn("type_id", $types_id)
        ->pluck("name");
    //dd($types);
        
    return response()->json($types);
}

Upvotes: 1

Related Questions