0

How can i get n records from a model per parent in laravel eloquent.

For example lets say i have products table and categories table. And i want a list of all the products who's name starting with A but not more then 10 products per category.

My table structure is something like this.

products table

---------------------------------
id  | title | slug | category_id
----------------------------------

Category table 

--------------
id  | title |
--------------

I tried to follow this example which is exactly what i want https://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/

But when i tried to query just like the example in the link after adding the scope in my product model . It throwing and sql error saying.

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=' (SQL: select count(*) as aggregate from...

Can anyone please tell how i can get n results per related model or how can i fix this error.

user7747472
  • 1,874
  • 6
  • 36
  • 80
  • What MySQL version? Because MySQL 8.0 has a better method then using MySQL's user variables what the tutorial is using. – Raymond Nijland Jun 08 '18 at 13:29
  • @RaymondNijland i am working on Xamp server latest one .And in phpmyadmin its showing Server version: 10.1.25-MariaDB . I guess that the one – user7747472 Jun 08 '18 at 13:33
  • Basically, the error is already telling you what to do: check the collations of your tables. If you set them to their different types on purpose, you have bad luck, because then these types of queries won't work. If this isn't the case, make sure the collation is the same on all tables and columns. There is plenty resources explaining how to go about this. – Namoshek Jun 08 '18 at 18:34
  • @Namoshek, could it be because of that my category are on on xamp server which is a mariadb and my products are on live server which is in inodb Could it be the causing issue/? If so can you tell me how can i fix that thanks – user7747472 Jun 08 '18 at 19:32
  • No idea, to be honest. Sorry. – Namoshek Jun 08 '18 at 19:47
  • You could get some help from [How to SELECT the newest four items per category?](https://stackoverflow.com/questions/1442527/how-to-select-the-newest-four-items-per-category/1442867#1442867) – M Khalid Junaid Jun 09 '18 at 21:22
  • Hey @user7747472 did you try my answer? –  Jun 12 '18 at 08:27

4 Answers4

1

There is no native support for this in Laravel.

I created a package for it: https://github.com/staudenmeir/eloquent-eager-limit

Use the HasEagerLimit trait in both the parent and the related model.

class Category extends Model {
    use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;
}

class Product extends Model {
    use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;
}

Then you can apply limit()/take() to your relationship:

Category::with(['products' => function($query) {
    $query->where('name', 'LIKE', 'A%')->limit(10);
}])->get();
Jonas Staudenmeir
  • 24,815
  • 6
  • 63
  • 109
0

Pass a query when you call relation:

Category::with(['products' => function($query){
    $query->take(10)->skip(0);
}])->get();

Assuming you have a relation in your Category model.

public function products()...
cau
  • 572
  • 3
  • 9
  • This will load `10` products in total, for all categories that you fetch. This means you can end up with some categories having loaded zero products and others having loaded multiple products. – Namoshek Jun 08 '18 at 16:49
  • That way can be done, but i dont want to do that way for several reasons one of which is i can not sort prodduct price and applying other filters which are directly linked to products will be bit difficult – user7747472 Jun 08 '18 at 17:36
  • Why not? $query->where('price', '>', 0)->take(10)->skip(0); You can use several eloquent functions inside a 'with' relation... Before of 'with' you can use 'has' too. So many options. Clean and easy. – cau Jun 08 '18 at 19:42
  • And after all this you can still filter the collection. – cau Jun 08 '18 at 19:49
0

First lets create your models:

class Category extends Model
{
    public $timestamps = false;

    protected $fillable = [

       'title'];

    public function products(){
    return $this->hasMany(\App\Products::class)->limit(10);
}
}

and the second one:

class Products extends Model
{
    public $timestamps = false;

    protected $casts = [
        'category_id' => 'int',
    ];

    protected $fillable = [

       'title',
        'slug'
    ];

    public function category()
    {
        return $this->belongsTo(\App\Category::class);
    }
}

Using eloquent the following would be:

$Category = Category::get();

You can also use starts_with() to define the column, word

  • 1
    Hi, this might be correct way to do to filter select product based on category. But what i want is other way around i.e. product based on category so that i can easily apply filters on products . Moreover i have over 2.5 million rcords in each table this way it will quite a lot time and server resource. But your answer is great :)) – user7747472 Jun 12 '18 at 11:15
  • actually this is product based on category,using foreach you simply do `$Category->product->name` and you already get all categories with 10 products each if you want to apply filters you simply apply `$Category->products->where()->otherfilters()->get();` and there you go.If my answer helps you please dont forget to mark it as such :) –  Jun 12 '18 at 12:10
0

From how I see it, for each categoryID you need an array of 10 products to be listed. Using Eloquent, this is what I came up with

$categories = Category::all()
productsArray = array();
foreach($categories as $category) {
        $products = Product::where('category_id', $category->id)->where('title', 'LIKE', 'A%')->get();
        if(sizeof($products)) {
            $products = $products->take(10);
            $productsArray[$category->id] = $products;
        }
    }
return $productsArray;

The only down side I see in this is the looping for each category ID, which might take more time if you have thousands of records. You can have any other key, other than $category->id if you wish to show it in your blade file.

nsg
  • 129
  • 2
  • 3