how to show all the products laravel-CodePudding

I have a query where I show all the products that are in that warehouse, but if it has more than one it shows me the x quantity but with the same data, thats because I have ->first(); but If I remove ->first(); it says an error whit the traceability.

 $Input = $request->key;
    $almacen = $request->almacen;
$name = DB::table('products')
    ->leftJoin('inventories', 'inventories.product_id', '=', 'products.id')
    ->whereRaw("(products.reference LIKE '%$Input%' OR products.name LIKE '%$Input%') AND products.deleted_at is null")
    ->leftJoin('warehouses', 'inventories.warehouse_id', '=', 'warehouses.id')
    ->where('warehouses.id', $almacen)
    ->whereNull('products.deleted_at')
    ->whereNull('inventories.deleted_at')
    ->select(
        'products.reference',
        'products.name',
        'products.sku',
        'products.id',
        'inventories.lot',
        'inventories.expirationDate',
        'inventories.traceability',
        'inventories.warehouse_id'
    )

    ->get();

$array = [];
foreach ($name as $key) {
    //$key->traceability
    array_push($array, $key->reference);
}
//$array = array_unique($array);
$html = '';

if ($name != '[]') {
    foreach ($array as $value) {
        $prodName = DB::table('products')
        ->leftJoin('inventories', 'inventories.product_id', '=', 'products.id')
        ->where('products.reference', $value)
        ->whereNull('products.deleted_at')
        ->whereNull('inventories.deleted_at')
        ->select(
            'products.reference',
            'products.name',
            'products.sku',
            'products.id',
            'inventories.lot',
            'inventories.expirationDate',
            'inventories.traceability'
            )
            ->first();
            //return $value;
        $html .= '<div><a style="color: #000000"  traceability="'.$prodName->traceability.'" reference="' . $value . '" sku="' . $prodName->sku . '" name="' . $prodName->name . '" lot="' . $prodName->lot . '" expirationDate="' . $prodName->expirationDate . '" data="' . ($value) . " " . ($prodName->name) . '" id="' . $prodName->id . '">' . ($value) . " " . ($prodName->name) ." " . ($prodName->lot) ." " . ($prodName->expirationDate) ." " . ($prodName->traceability) .'</a></div>';
    }
} else {
    $html .= '<div><a style="color: #000000"  exist="0" data="Sin coincidencias." id="0">Sin coincidencias.</a></div>';
}
return $html;

});`

I would like to see the products whit the correct data,

CodePudding user response：

Your code is currently only returning the first row in the database because you are using first().

If you remove first(), you will just be left with a Query Builder and will get an error like "Undefined property: Illuminate\Database\Query\Builder::$traceability" - because you haven't got the data yet.

You can use get() instead of first() to get all of the rows, and this will return a Collection.

You can also use ->whereIn('products.reference', $array) instead of looping through all of the elements in $array

You are also comparing $name to the string '[]' instead of an empty array. You can use empty() instead: if (!empty($name)) {

And then you can simply loop through the Collection and add the html:

    // Use empty() - This checks if an array is empty or not
    if (!empty($name)) {
        $products = DB::table('products')
            ->leftJoin('inventories', 'inventories.product_id', '=', 'products.id')
            // Use whereIn - This checks if any of the values in $array match products.reference
            ->whereIn('products.reference', $array)
            ->whereNull('products.deleted_at')
            ->whereNull('inventories.deleted_at')
            ->select(
                'products.reference',
                'products.name',
                'products.sku',
                'products.id',
                'inventories.lot',
                'inventories.expirationDate',
                'inventories.traceability'
            )
            // Return all rows
            ->get();

        foreach ($products as $product) {
            $html .= '<div><a style="color: #000000"  traceability="' . $product->traceability . '" reference="' . $product->reference . '" sku="' . $product->sku . '" name="' . $product->name . '" lot="' . $product->lot . '" expirationDate="' . $product->->expirationDate . '" data="' . ($product->reference) . " " . ($product->->name) . '" id="' . $product->->id . '">' . ($product->reference) . " " . ($product->name) . " " . ($product->lot) . " " . ($product->expirationDate) . " " . ($product->traceability) . '</a></div>';
        }
    } else {