#groupBy in laravel

[adityaypi]

laravel distinct count group by

laravel distinct count group by

public function showUniqueUsersBhagwatByDate(Request $request) { \LogActivity::addToLog('admin'); $data= array(); $data = DB::table('cal_user_activity')->select(DB::raw('DATE(updated_at) date'),DB::raw('count(DISTINCT user_id) as page_count'))->where('module','BhagwatAudio')->groupby('date')->get(); return view('admin/shownewbhagwatgeetalistener',['data'=>$data,'title'=>'Show Uniques Users…

[laravel-tutorial]

Laravel: How to have custom “groupBy”?

[codesolutionsstuff]

How To Use Chart JS In Laravel 

The fundamentals of Chart.js are quite straightforward. First, we must install Chart.js into our project. Depending on the settings of your project, you may be installing it using npm or bower, or you may link to a constructed version via a CDN or clone/build from GitHub. Simply connecting to the created CDN version in the sample's blade file would suffice for this brief example. A The fundamentals of Chart js are quite straightforward. First, we must install Chart js into our project. Depending on the settings of your project, you may be installing it using npm or bower, or you may link to a constructed version via a CDN or clone/build from GitHub. In our examples, we'll only link to the built-in CDN version for the purposes of this brief demonstration. We'll just plot the ages of the app users in this case. We're presuming you've already set up the Laravel auth scaffolding and carried out the required migrations to make a Users table. If not, take a look at the information here or modify it for the model you're using for your chart's data. Therefore, before creating any users at random, we'll first add an age column to our Users table. For more information, see our post on how to use faker to create random users, however for this demonstration, let's make a database migration to add an age column by using: add age to users table php artisan make:migration —table='users' To change the up function to: edit this file in the database migrations directory. Schema::table('Users', function (Blueprint $table) { $table->int('age')->nullable(); }); Run php artisan migrate after that, and your Users table should now contain an age column. Visit /database/factories/UserFactory now, and add the following at the end of the array: 'age' is represented by $faker->numberBetween($min = 20, $max = 80), The complete return is thus: return ; Run the following commands to build a UsersTableSeeder: make:seeder UsersTableSeeder in PHP This will produce UsersTableSeeder.php in the database. The run function should include the following: factory(AppUser::class, 5)->create(); When this is executed, 5 users will be created; modify 5 to the number of users you need. After that, we must open DatabaseSeeder.php in /database/seeds and uncomment the code in the run() function. Finally, execute php artisan db:seed. Five new users should appear, each of whom has an age. For our Charts page, we will now develop a model, controller, views, and routes. Run the following command in PHP: make:controller ChartController —model=Chart. To the file /app/Http/Controllers/ChartController.php, add the following: use AppUser; use AppChart; use DB; ... public function index() { // Get users grouped by age $groups = DB::table('users') ->select('age', DB::raw('count(*) as total')) ->groupBy('age') ->pluck('total', 'age')->all(); // Generate random colours for the groups for ($i=0; $ilabels = (array_keys($groups)); $chart->dataset = (array_values($groups)); $chart->colours = $colours; return view('charts.index', compact('chart')); } The random colour scheme is one example of the exciting things you can do with the controller's data, though you can also specify hardcoded colours if you'd choose. In /resources/views/charts/, we must now create an index.blade.php file and add the following (depending on your blade setup and layout; here is an example): Laravel Chart Example Chart Demo Finally, we need to add the following to /routes/web.php: Route::get('/charts', 'ChartController@index')->name('charts'); Go to at your-project-name.test/charts now. Although this should serve as a good starting point for your understanding of the fundamentals of charts and graphs in Laravel, you may refer to the Chart.js documentation for more details on customizing your charts. Read the full article

[codingspoint]

How to use groupby having with DB::raw in Laravel ?

How to use groupby having with DB::raw in Laravel ?

Today now in this post i will show you How to use groupby having with DB::raw in Laravel Query Builder? Most of times we probably need to use the group by having because if we are work on the small project then it’s not need to use this generally. But if when we are working with any big project as like e-commerce, social blogging site or any ERP level project then we must need to use the having…

[codehunger]

Laravel Group By Using eloquent with example

Laravel Group By Using eloquent with example

In this article, we will learn about the laravel group by and how to do it using eloquent, but before moving forward let’s see what is a group by in SQL. SQL groupby statement The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”. The GROUP BY the statement is often used with aggregate functions…

[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

[chandanmistry89]

Laravel DB query unique count Without groupBy: $res = DB::table('table')->distinct()->count(field); With groupBy: $res = count(DB::table('table')->groupBy('field')->get());

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

[lxgzhw]

Laravel第四天上午

复习 DB类提供的方法

增 insert(array)

删 where()->delete()

table() where() delete()

改 where()->update(array)

table where() update()

查 select get first count

groupBy having() orderBy offset() limit() join()

Session类的使用方法

创建 Session::input(键,值)

读 Session::get()

判断 has()

删除 flush()

对象 session()

今日内容 Model如何使用:数据模型类的父类

特点:只能被继承

继承也是要使用父类的方法及属性

DB有的方法Model都有,只多不少

独有方法 模型中唯二自动添加时间的方法

save()

create()返回对象 通过…

[fripig]

fripig Laravel 集合类中 GroupBy 方法的使用技巧 | Laravel China 社区 - 高品质的 Laravel 开发者社区 #技術文章

fripig Laravel 集合类中 GroupBy 方法的使用技巧 | Laravel China 社区 - 高品质的 Laravel 开发者社区 #技術文章 from fripig via IFTTT

[tryhyperhost]

GroupBy multiple levels in Laravel

http://dlvr.it/QDK1nC https://hyper.host

[programmingbiters-blog]

New Post has been published on http://programmingbiters.com/laravel-5-generate-sitemap-using-roumen-sitemap-package-example/

Laravel 5 generate sitemap using roumen/sitemap package Example

In this post i want to github example of generate dynamic sitemap using “roumen/sitemap” package. sitemap is very usefull for SEO. you can submit your sitemap on google webmaster, bing webmaster etc that way you can increase website rank and web traffic. So, if you are working on Laravel framework then you can use “roumen/sitemap”. this package will helps to generate dynamic sitemap of website. so it’s very simple way to use and pretty interesting package. you can also add image on sitemap. this package is also provide to generate sitemap and store it on you folder etc. you can also see the preview of sitemap.Preview:

So, let’s follow following step and use it. i provide just two step one for installing packages and second one for example of sitemap generate.

Step 1: Installation Package

first you have to fire following command on your terminal and get the latest version of “roumen/sitemap” package.

composer require roumen/sitemap

Add ServiceProvider

Ok, Now open config/app.php file and add service provider of this package.

'providers' => [ .... 'RoumenSitemapSitemapServiceProvider', ]

Publish Config File

following command through you can create publish config file of “roumen/sitemap” package. after fire this command you can see one file app/config/sitemap.php and you can change your own setting.

php artisan vendor:publish --provider="RoumenSitemapSitemapServiceProvider"

Step 2: Example With Route

In Last step i provde example of how to generate sitmap. so first open app/Http/routes.php and add following route.

app/Http/routes.php

Route::get('sitemap', function() /* create new sitemap object */ $sitemap = App::make("sitemap"); /* add item to the sitemap (url, date, priority, freq) */ $sitemap->add(URL::to('/'), '2012-08-25T20:10:00+02:00', '1.0', 'daily'); $posts = DB::table('post') ->select('post.*' ,'post.title as postTitle','post.slug as postSlug' ,'post_image.image as image','post.updated_at as postUpdated_at') ->join('post_image','post_image.post_id','=','post.id') ->orderBy('created_at','desc') ->groupBy('post.id') ->get(); $postResult = array(); if(!empty($posts)) foreach ($posts as $key => $value) $postResult[$value->id]['id'] = $value->id; $postResult[$value->id]['slug'] = $value->postSlug; $postResult[$value->id]['postTitle'] = $value->postTitle; $postResult[$value->id]['postUpdated_at'] = $value->postUpdated_at; $postResult[$value->id]['image'][] = $value->image; /* add every post to the sitemap */ foreach ($postResult as $key=>$value) $images = array(); foreach ($value['image'] as $key2 => $value2) $images[] = array( 'url' => URL::to('/')."/uploadImages/post/".$value2, 'title' => $value['slug'], 'caption' => $value['postTitle'] ); $sitemap->add(URL::route('front.post',$value['slug']), $value['postUpdated_at'], '1.0', 'daily', $images); /* show your sitemap (options: 'xml' (default), 'html', 'txt', 'ror-rss', 'ror-rdf') */ return $sitemap->render('xml'); );

Now you can open sitemap route and check output, it will be like preview. You can also get more information of this package from here : Wiki

قالب وردپرس