Tag : Query Builder



Laravel 5 - whereIn and whereNotIn with subquery example using query builder

As you know laravel is very popular php framework in today. So every point will be require for database related and that important. In this post i would like to share with you how to use subquery with whereIn and whereNotIn clause from scratch. We may sometimes require to use select query with where in or where not in clause. we can simply make query using MySQL. But we don't know how to use it on laravel query builder. However, In this example we will learn how to convert mysql query into laravel query builder of wherein with subquery. This example is from scratch, so i will give you first m.....

By Hardik Savani | March 23, 2017 | | 15067 Viewer | Category : Laravel MySql
READ MORE

Laravel - How to search with comma separated values using Query Builder ?

Sometimes, we require to manage to take single column for multiple integer ids, for example if we have "posts" table and require to set multiple tags on each post, so at that time we can take just single column and store selected tags ids. this way will use less memory. It is simple to store data into row, but as you think how we can search or find specific tag id data from comma separated column value using laravel query builder. However, It is possible using <strong class="path">"find_in_set()"</strong> of MySql predefine function, We can use find_in_set() using whereRow() of laravel query .....

By Hardik Savani | March 2, 2017 | | 5584 Viewer | Category : Laravel MySql
READ MORE

Laravel 5 - "syntax error or access violation 1055 in group by" Solved

Yesterday i was working on my Laravel 5.4 application and when i use group by statement in query builder I found error like as bellow: <br/> <strong> SQLSTATE[42000]: Syntax error or access violation: 1055 'admin.products.name' isn't in GROUP BY (SQL: select `products`.*, SUM(products_stock.stock) from `products` inner join `products_stock` on `products_stock`.`product_id` = `products`.`id` group by `products`.`id`) </strong> I was thinking what will be error and why it comes. i thought maybe it comes from mysql. i just add following query using laravel builder as like bellow: <strong>Laravel.....

By Hardik Savani | February 15, 2017 | | 3317 Viewer | Category : Laravel
READ MORE

Laravel - How to make subquery in select statement?

Sometimes we require to make subquery inside the select statement in Laravel. At that time we are thinking how to select from subquery using in Laravel Query Builder. In this post i will give you example of subquery in select statement in Laravel 5 application from scratch. For subquery in select statement we are going to use <strong class="path">DB::raw()</strong>. DB raw function through we can simply make suquery join in Laravel Eloquent Query Builder. Here as bellow full example, i have three tables as listed bellow: <strong>1)products</strong> <strong>2)products_stock</strong> <strong.....

By Hardik Savani | February 6, 2017 | | 28050 Viewer | Category : Laravel MySql
READ MORE

MySql comma separated column join in PHP Laravel 5

If we work in big application or project of Laravel Framework, sometimes we require to manage column value into comma separated because we can save memory of database. But if we store comma separated value in column with ids then it's difficult to inner join or left join and get records from another tables. However, SQL provide FIND_IN_SET() for checking value one by one, that way we can also search from comma separated value. FIND_IN_SET() will help to join with tables and we can simply get data. In this post, I will give you full example of how to get data from comma separated column table.....

By Hardik Savani | January 7, 2017 | | 5868 Viewer | Category : PHP Laravel MySql SQL
READ MORE

Laravel 5.3 - Call to undefined method Illuminate\Database\Query\Builder::lists() Solved

Today i want to tell you about error "Call to undefined method Illuminate\Database\Query\Builder::lists()" and how to solve this error in Laravel 5.3. When i was working on my Laravel 5.3 application, at that time i require to get lists of email and id of users table for select drop-down. But when i use lists() with DB table i found error "Call to undefined method Illuminate\Database\Query\Builder::lists()". Error was like bellow preview: <pre> BadMethodCallException in Builder.php line 2440: Call to undefined method <br/> Illuminate\Database\Query\Builder::lists() </pre> I did search a lo.....

By Hardik Savani | September 24, 2016 | | 9856 Viewer | Category : Laravel
READ MORE

Laravel 5.3 - whereDate(), whereMonth(), whereDay() and whereYear() examples.

Laravel is a very popular PHP framework. Laravel 5.3 release few days ago and there are several new feature added by Laravel. Laravel 5.3 introduce several new where conditions like whereDate(), whereMonth() etc in Query Builder. Today, i am going to show you how to use it in your Laravel Application. I give you one example table and understand how works this four query builder functions one by one. So, first you can see bellow table with some records. <strong>products table</strong> <img src="/upload/product-table.png" style="width:100%;border:1px solid red"> <strong>whereDate()</strong> .....

By Hardik Savani | September 6, 2016 | | 25307 Viewer | Category : Laravel
READ MORE

Laravel 5 - where condition with two columns example code

Laravel 5 introduce whereColumn() in Query Builder, that way we can compare two column like simple where condition. We sometimes require to check this type of condition. In this example code, i have simple "items" table and i want to get only created_at and updated_at column should equals. So you can also check bellow laravel eloquent as bellow: <strong>Example:</strong> <pre class="prettyprint lang-php"> $data = DB::table("items") ->whereColumn('created_at','updated_at') ->get(); print_r($data); </pre> <strong>Example 2:</strong> <pre class="prettyprint lang-php"> .....

By Hardik Savani | July 29, 2016 | | 2682 Viewer | Category : Laravel
READ MORE

Group by year month example in Laravel 5 using Query Builder

Sometimes, we have created_at column with timestamp and we wanted to get with group by with month, that way we can use for chart. However, you can do it using mysql MONTH() function. we can use DB raw with mysql function and get group by monthly records. So, you can see bellow query and use it. <strong>Example:</strong> <pre class="prettyprint lang-php"> DB::table("clicks") ->select("id" ,DB::raw("(COUNT(*)) as total_click")) ->orderBy('created_at') ->groupBy(DB::raw("MONTH(created_at)")) ->get(); </pre>

By Hardik Savani | June 20, 2016 | | 18563 Viewer | Category : Laravel
READ MORE

Laravel 5 select with sum query example

We mostly required to get sum of amount, salary etc in laravel. We can also get sum of column using mysql SUM(). We have two way to get sum of column value. first we can use laravel sum() of query builder and another one we can use with directly with select statement using DB::raw(). I give you both example you can see and use any one as perfect for you. <strong>Example 1:</strong> <pre class="prettyprint lang-php"> $data = DB::table("click")->sum('numberofclick'); print_r($data); </pre> <strong>Example 2:</strong> <pre class="prettyprint lang-php"> $data = DB::table("click") ->select(DB.....

By Hardik Savani | June 8, 2016 | | 29142 Viewer | Category : Laravel
READ MORE

    Laravel 5 Stripe example using Laravel Cashier from Scratch

    Laravel 5 Stripe example using Laravel Cashier from Scratch

    Open Post

    Laravel 5 ajax request validation example

    Laravel 5 ajax request validation example

    Open Post

    Laravel 5 amazon s3 file upload tutorial - Part 2

    Laravel 5 amazon s3 file upload tutorial - Part 2

    Open Post

    Simple PHP Jquery Ajax CRUD(insert update delete) tutorial example with source code

    Simple PHP Jquery Ajax CRUD(insert update delete) tutorial example with source code

    Open Post

    Laravel 5 Repository Pattern Tutorial from scratch

    Laravel 5 Repository Pattern Tutorial from scratch

    Open Post

    Laravel 5 - How to prevent browser back button after user logout?

    Laravel 5 - How to prevent browser back button after user logout?

    Open Post