How to get all data using pagination?

I ran into this issue and can't figure out how to solve it. There are, for example, 100 products. Each product has its own attributes.

id         title        color      size       brand      category
1	       title1          red         30	     adidas        shoes
2         title2       black       30       puma          shoes
3         title3        blue        28       puma          shoes

To get the goods, I make the following request

$products = Product::getProducts($category, $allAttributes);

I am using pagination:

public static function getProducts ($category, $allAttributes) {
        return static::select(['id',  'title', 'color'', 'size'', 'brand])
           
            ->where([
                ['category', $category],
            ])
           
            ->paginate(10);
    }

As a result, I get 100 products and divide the pagination into 10 products. This works fine. But when I want to get the available attributes (color, size, brand), I get data from the current 10 products. How to get data for all products on each pagination page?

Tray2

3 years ago

Level 74

Eager load it?

Products::query()
	->with('attributes')
	->paginate(10);

1 like

drgreen

3 years ago

Level 28

You want to get the available values from the products table attributes? You could do it this way:

$colors = Product::distinct()->pluck('color');
$sizes = Product::distinct()->pluck('size');
$brands = Product::distinct()->pluck('brand');

1 like

Rediska

3 years ago

Level 1

@drgreen And if I have 10 attributes, then instead of one connection to the database, I need to connect 11 times? Is this really the best way?

Tray2

3 years ago

Level 74

@Rediska No, but since we don't know your database model, or how the attributes are stored, it's very hard to give sound advice.

I suggest reading this post, and pay close attention to the part One-to-many With Different Properties

https://tray2.se/posts/database-design-part-2

1 like

drgreen

3 years ago

Level 28

@Rediska Not sure what you want to query it for. But you could use union method for that, which will be executed as one query:

$color = Product::select(DB::raw('"Color"'), 'color');
$brand = Product::select(DB::raw('"Brand"'), 'brand');
$attributes = Product::query()
    ->select(DB::raw('"Size" as "key"'), DB::raw('`size` as "value"'))
    ->union($color)
    ->union($brand)
    ->get();

dd($attributes->mapToGroups(function ($item, $key) {
    return [$item['key'] => $item['value']];
})->toArray());

Results:

array:4 [▼
  "Size" => array:7 [▼
    0 => "5"
    1 => "3"
    2 => "6"
    3 => "1"
    4 => "4"
    5 => "10"
    6 => "2"
  ]
  "Color" => array:10 [▶]
  "Brand" => array:10 [▶]
]

SQL:

(
  select
    "Size" as "key",
    `size` as "value"
  from
    `products`
)
union
(
  select
    "Color",
    `color`
  from
    `products`
)
union
(
  select
    "Brand",
    `brand`
  from
    `products`
)

1 like

Rediska

3 years ago

Level 1

@Tray2 They are stored like this. Without connections. I know it's not quite right, but it's more convenient in my case.

id         title        color      size       brand      category
1         title1       black       30        nike          shoes
2         title2       black       30       puma          shoes
3         title3        blue       28       puma          shoes

Tray2

3 years ago

Level 74

@Rediska This will bite you in the ass, and it will bite hard.

You really should utilize the relational in a relational database.

As it is now, there is no way for you to guarantee the the correct attributes are used for your product.