#groupby in laravel 9

[khoinv]

[Laravel] Write better code

After reviewing the code for the new team members, I found long/wrong codes. I write it down here.

Content

1. Use default params when getting value from request

2. Use Eloquent when function

3. Use Eloquent updateOrCreate

4. Use map instead of bundle if/else

5. Use collection when you can

6. Stop calling query in loop

7. Stop printing raw user inputted values in blade.

8. Be careful when running javascript with user input.

9. Stop abusing Morph

1. Use default params when getting value from request

The original code

$search_type = SearchTypeConstant::TITLE; if($request->has('search_type')){ $search_type = $request->get('search_type'); }

It is verbose

Refactoring

$search_type = $request->get('search_type', SearchTypeConstant::TITLE);

2. Use Eloquent when function

The original code

$query = User::active(); if($first_name = $request->get('first_name')){ $query = $query->where('first_name', $first_name); } $users = $query->get();

We can remove temp $query variable

Refactoring

$users = User::active()->when($first_name = $request->get('first_name'), function($first_name){ return $q->where('first_name', $first_name); })->get();

3. Use Eloquent updateOrCreate

The original code

if($user->profile){ $user->profile->update($request->get('profile')->only('phone', 'address')); } else { $user->profile()->create($request->get('profile')->only('phone', 'address')); }

It is verbose

Refactoring

$user->profile()->updateOrCreate([], $request->get('profile')->only('phone', 'address'));

4. Use map instead of bundle if/else

The original code

function get_status_i18n($status){ if($status == STATUS::COMING){ return 'coming'; } if($status == STATUS::PUBLISH){ return 'publish'; } return 'draft'; }

It is long

Refactoring

private const MAP_STATUS_I18N = [ STATUS::COMING => 'coming', STATUS::PUBLISH => 'publish' ]; function get_status_i18n($status){ return self::MAP_STATUS_I18N[$status] ?? 'draft'; }

5. Use collection when you can

The original code

function get_car_equipment_names($car){ $names = []; foreach($car->equipments as $equipment){ if($equipment->name){ $names[] = $equipment->name; } } return $names; }

It is long

Refactoring

function get_car_equipment_names($car){ return $car->equipments()->pluck('name')->filter(); }

6. Stop calling query in loop

Ex1:

The original code

$books = Book::all(); // *1 query* foreach ($books as $book) { echo $book->author->name; // Make one query like *select \* from authors where id = ${book.author_id}* every time it is called }

N + 1 queries problem

Refactoring

$books = Book::with('author')->get(); // Only two queries will be called. // select * from books // select * from authors where id in (1, 2, 3, 4, 5, ...) foreach ($books as $book) { echo $book->author->name; }

Simple implementation of $books = Book::with('author')->get() code as the bellow.

$books = Book::all(); //*1 query* $books_authors = Author::whereIn('id', $books->pluck('author_id'))->get()->keyBy('author_id'); // *1 query* foreach($books as $book){ $books->author = $books_authors[$book->author_id] ?? null; }

Ex2;

The original code

$books = Book::all(); // *1 query* foreach($books as $book){ echo $book->comments()->count(); // Make one query like *select count(1) from comments where book_id = ${book.id}* every time it is called }

N + 1 queries problem

Refactoring

=>

$books = Book::withCount('comments')->get(); // Only two queries will be called. foreach($books as $book){ echo $book->comments_count; }

Simple implementation of $books = Book::withCount('comments')->get() code as the bellow.

$books = Book::all(); // *1 query* $books_counts= Comment::whereIn('book_id', $books->pluck('id'))->groupBy('book_id')->select(['count(1) as cnt', 'book_id'] )->pluck('book_id', cnt); // *1 query* foreach($books as $book){ $book->comments_count = $likes[$book->id] ?? 0; }

Total: 2 queries

Note: Read more about eager-loading In some frameworks, like phoenix, lazy-load is disabled by default to stop incorrect usage.

7. Stop printing raw user inputted values in blade.

The original code

<div>{!! nl2br($post->comment) !!}</div>

There is a XSS security issue

Refactoring

<div>{{ $post->comment }}</div>

Note: if you want to keep new line in div, you can use white-space: pre-wrap Rules: Don't use printing raw({!! !}}) with user input values.

8. Be careful when running javascript with user input.

The original code

function linkify(string){ return string.replace(/((http|https)?:\/\/[^\s]+)/g, "<a href="%241">$1</a>") } const $post_content = $("#content_post"); $post_content.html(linkify($post_content.text()));

There is a XSS security issue. Easy hack with input is http:deptrai.com<a href="javascript:alert('hacked!');">stackoverflow.com</a> or http:deptrai.com<img src="1" alt="image">

Refactoring

function linkify(string){ return string.replace(/((http|https)?:\/\/[^\s]+)/g, "<a href="%241">$1</a>") } const post = $("#content_post").get(0); post.innerHTML = linkify(post.innerHTML)

Bonus: simple sanitize function Note: Don't use unescaped user input to innerHTML. Almost javascript frameworks will sanitize input before print it into Dom by default. So, be careful when using some functions like react.dangerouslySetInnerHTML or jquery.append() for user inputted values.

In my test results, WAF(Using our provider's default rules) can prevent server-side XSS quite well, but not good with Dom-Base XSS.

Rules: Be careful when exec scripts that contains user input values.

9. Stop abusing Morph

When using morph, we cannot use foreign key relationship and when the table is grow big we will face performance issues.

The original code

posts id - integer name - string videos id - integer name - string tags id - integer name - string taggables tag_id - integer taggable_id - integer taggable_type - string

Refactoring

posts id - integer name - string videos id - integer name - string tags id - integer name - string posts_tags tag_id - integer post_id - integer videos_tags tag_id - integer video_id - integer

[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.