#orderby in laravel 9

[413-request-entity-too-large]

Laravelで月別や日別で集計する方法

MENU

Laravelで月別や日別で集計する方法

Posted: 2019.09.16 / Category: PHP / Tag: Laravel

Laravelでタイムスタンプのあるログデータを月や日別に集計する方法をご紹介します。

Sponsored Link

テーブル例

たとえば下記のようなデータベーステーブルがあるとします。 モデル名はLogとして、日毎にカウント（count）の合計を出したいとします。

id INT count INT created_at TIMESTANP

月別の集計

2019年の月毎に集計したい場合、まずはwhereYearで2019年を検索します。 groupByでformat('m')にすると月別にまとまるので、最後にmapでsumします。

Log::whereYear('start_at', 2019) ->orderBy('created_at') ->get() ->groupBy(function ($row) { return $row->created_at->format('m'); }) ->map(function ($day) { return $day->sum('count'); });

日別の集計

日別の場合whereMonthで集計したい月を追加します。 groupByの部分を日のdに変更するだけです。 2019年9月を日別に集計したい場合は次のようになります。

Log::whereYear('created_at', 2019) ->whereMonth('created_at', 9) ->orderBy('created_at') ->get() ->groupBy(function ($row) { return $row->created_at->format('d'); }) ->map(function ($day) { return $day->sum('count'); });

RECENT POSTS　/　POPULAR POSTS

jQueryでスクロールすると表示する系いろいろ864,602

jQueryでjsonデータを扱ってみ��【入門編】420,409

jQueryで簡単に作れるマウスオーバーでアニメーションするボタン５種373,809

jQueryのanimateで自由にアニメーションできるようになろう334,542

jQuery+CSS3で固定ナビゲーション→レスポンシブでハンバーガーメニューに切り替える232,873

jQueryでスクロールすると上部に固定されるナビゲーション221,316

jQueryで背景画像をアニメーションで無限ループさせる162,661

jQueryで画面遷移のないサイトを作ろうとしたときのちょっとしたメモ161,702

jQueryで指定した要素にclass,idを追加する159,278

可変グリッドレイアウトなWebデザイン集めてみました151,255

CATEGORIES　/　TAGS　/　ARCHIVE

LINKS

このブログではjQueryやWordPressを中心としたWeb制作関係のことを書いております。 ご意見ご感想などお気軽にコンタクトフォームよりお問い合わせください。 Web関係のお仕事も募集しております。

Copyright © WEBOPIXEL All rights reserved.

[programmingbiters-blog]

New Post has been published on https://programmingbiters.com/implementing-an-add-to-favourites-feature-in-laravel/

Implementing an 'Add to Favourites' feature in Laravel

Sometimes we may want to give our users the ability to like an item and add it to a favourties list. For example, if we are building an ecommerce app, it makes sense to implement some kind of a Wishlist, where users can add products they liked, and browse that Wishlist later. Implementing such an ‘Add to Favourites’ feature in Laravel is very easy, thanks to Eloquent relationships.

TL;DR: A complete implementation can be found on this Github repo 🙂

# Setting up the database

Assuming we already have a model setup (in this example, I’m using Product  model that has two fields: title  and description ), all we need to do is create a pivot table in order to define a polymorphic relationship between the models.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

public function up()

    Schema::create(‘favouriteables’, function (Blueprint $table)

        $table->increments(‘id’);

        $table->integer(‘user_id’)->unsigned();

        $table->integer(‘favouriteable_id’);

        $table->string(‘favouriteable_type’);

        $table->timestamps();

        $table->foreign(‘user_id’)->references(‘id’)->on(‘users’)->onDelete(‘cascade’);

    );

favouriteable_id  will contain the id of the model we favourited. favouriteable_type  will contain the class name of the owning model. We also add a foreign key user_id  to keep track of items an user favourited.

# Setting up the relationships

Relationship between User  and Product  would be a a polymorphic relation– a user may favourite many products and a product may be favourited by many users. So this will be a many to many polymorphic relation. Let’s define this relationship in the corresponding models.

Let’s start with the  User  model:

1

2

3

4

5

6

7

8

public function favouriteProducts()

    return $this->morphedByMany(Product::class, ‘favouriteable’)

                ->withPivot([‘created_at’])

                ->orderBy(‘pivot_created_at’, ‘desc’);

We are grabbing the created_at  field from this pivot table to get the time when a particular product was favourited and then ordering the items so that the last favourited item is on the top. In the  orderBy() method, we are usingpivot_created_at  to order the items by using the time they were inserted into the pivot table. If we just use  created_at instead, it will use the time when the products were created and not the time when they were favourited, which we do not want.

Now let’s defined the inverse relationship on the Product  model.

1

2

3

4

5

6

public function favourites()

    return $this->morphToMany(User::class, ‘favouriteable’);

We are also creating a favouritedBy() method to check if a particular user has favourited a particular product.

1

2

3

4

5

6

public function favouritedBy(User $user)

return $this->favourites->contains($user);

We type-hint a User  into this method and then accessing the polymorphic relation, we check if the current product has already been favourited by the user we passed into the method.

# Adding a product to favourites list

To add a product to the favourites list, first we need a route. This will be aPOST  route.

1

2

3

Route::post(‘/products/product/favourites’, ‘ProductController@store’)->name(‘product.fav.store’);

We are using a POST  route rather than a GET. This is because it gives us the added advantage of implementing CSRF protection.

But since this is a POST route, we can NOT directly use  it like this:

1

2

3

<a href=“ route(‘product.fav.store’) ”>Add to Favourites</a>

If we do, Laravel will throw a MethodNotAllowedHttpException .

To get around this, we will use the same technique that Laravel uses to implement logout route in it’s default auth scaffolding:

1

2

3

4

5

6

7

8

9

10

<a href=“#” onclick=“event.preventDefault();

                     document.getElementById(‘product-fav-form’)

                             .submit();”>Add to Favourites</a>

<form id=“product-fav-form” class=“hidden”

      action=“ route(‘product.fav.store’, $product) ” method=“POST”>

     csrf_field()

</form>

We define a form that posts through to product.fav.store  route, where we also inject the $product so that Laravel automatically resolves the model for us. We then add CSRF protection   csrf_field()  . We also add an id  to the form. When someone clicks on the link, we first prevent the default behaviour using  event.preventDefault(); and then we get the form by it’sid , and simply submit it.

Next, let’s implement the store()  method on our ProductController .

1

2

3

4

5

6

7

8

public function store(Request $request, Product $product)

$request->user()->favouriteProducts()->syncWithoutDetaching([$product->id]);

return back();

First we grab the currently signed in user from the Request and then accessing the favouriteProducts()  relationship, we call thesyncWithoutDeatching()  method and pass in the product. This will go ahead and attach the product to the currently signed in user by inserting a record in the intermediate table. Here we are using syncWithoutDetaching()  method mainly because of two reasons:

first, when the user favourites another product, this would not remove the user’s any previously favourited products from the table.

second, this would ensure that if a user already favourited a product and somehow makes another request to favourite the same product, it will NOT store a new record.

If all of these do not make much sense, do go through the Eloquent Relationship’s documentation.

Finally, we need to update our view so that once a user has favourited a product, the ‘Add to Favourites‘ link  disappears for that particular user on that particular product.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

@if(!$product->favouritedBy(Auth::user()))

    <span class=“pull-right”>

        <a href=“#” onclick=“event.preventDefault();

                            document.getElementById(‘product-fav-form’)

                                    .submit();”>Add to Favourites</a>

        <form id=“product-fav-form” class=“hidden” action=“ route(‘product.fav.store’, $product) ”

              method=“POST”>

             csrf_field()

        </form>

    </span>

@endif

This is where the favouritedBy()  method on our Product  model comes into play. We pass the currently authenticated user into the method that checks if this user has already liked this product. In case the user has liked it, we skip the link. Otherwise we go ahead and show it.

# Displaying the favourites list

Displaying the favourites list for a particular user is easy.

1

2

3

4

5

6

7

8

public function index(Request $request)

$products = $request->user()->favouriteProducts()->paginate(5);

return view(‘fav’, compact(‘products’));

We get the request object in our method, and from that request, we extract the currently signed in user, access the favourtieProducts()  method on ourUser  model, grab all the products and paginate them should we wish to and finally pass them to the corresponding view.

Therefore, in our view, we can do something like this:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

@if($products->count())

    @foreach($products as $product)

        <article>

            <h4> $product->title </h4>

            <span class=“pull-left”>Added $product->pivot->created_at->diffforHumans() </span>

        </article>

        <hr>

    @endforeach

@else

    No favourite items found :(

@endif

 $product->pivot->created_at->diffforHumans()   gives us the time when that particular product was favourited.

# Removing a product from favourites list

To remove a product from the list, we first define a DELETE  route.

1

2

3

Route::delete(‘/products/product/favourites’, ‘ProductController@destroy’)->name(‘product.fav.destroy’);

Just like what we did with product.fav.store  route, similarly we will also use a hidden form here to POST through this product.fav.destroy  route to ensure CSRF protection.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

<a href=“#” onclick=“event.preventDefault();

         document.getElementById(‘product-fav-destroy- $product->id ’)

                 .submit();”>

Remove from Favourites

</a>

<form action=“ route(‘product.fav.destroy’, $product) ”

      method=“POST”

      id=“product-fav-destroy- $product->id ”>

     csrf_field()

     method_field(‘DELETE’)

</form>

We define   method_field(‘DELETE’)  to spoof the value of form’s HTTP verb. We are also generating unique form ids for each product, otherwise when we remove products, they will be removed from the top.

Next, let’s define the destroy()  method on the ProductController .

1

2

3

4

5

6

7

8

public function destroy(Request $request, Product $product)

$request->user()->favouriteProducts()->detach($product);

return back();

Since we are using Route Model Binding, we automatically get the $product we are trying to remove. From the $request  object, we extract the user and accessing the relationship, we grab the user’s favourite products and we detach the product. The detach()  method will remove the record from the pivot table.

That’s all we need to remove items.

# Conclusion

Because we used polymorphic relations, it is very easier to extend the application and create the ability to like other models. For example, we may want to implement a feature where a user favourites a seller, or a blog post or even another user. This can now be implemented in a hassle free way.