In this article we will learn to use aggregate function concat in laravel eloquent with search and query builder. Concat is used to join two columns and in laravel eloquent there is no method for concat but we can use DB builder to use native MySQL Methods. In MySQL or SQL based database we can do concat of columns using concat aggregate function.
Sometimes in our application we want to use join first name and last name or any other columns to show jointly in our application so we can either use mysql concat function or laravel accessor mutators.
Using the laravel
Concat mysql concates two or more column and result as one column. For example if you have first name, middle name and last name then you can concat three columns in one as full name and then we can search full name with any query.
Here is the syntax
Model::select(\DB::raw("concat(column_name," ",column_name_2) as full_name"))
//or
Model::where(\DB::raw("concat(column_name," ",column_name_2)"),"like","%query%")->get(); // or first()
//or
\DB::table('table_name')->select(\DB::raw("concat(column_name," ",column_name_2) as full_name"))
In the above syntax we have used 3 examples to show all possibilities to use concat aggregate function in laravel.
First is using the Model and concat mysql function using DB class. Second is Model with where and Db::raw class to search the columns.
Result of above query will be:
select concat(column_name," ",column_name_2) as full_name from `table`
//OR
select * from `table` where concat(column_name," ",column_name_2) like "%query%"
Let’s understand laravel min in eloquent query with example
Example 1 – Mysql concat aggregate function query using eloquent
So in this example i will use mysql concat method in laravel eloquent to concat two columns.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Article;
class ArticleController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$Article = Article::select(\DB::raw("concat(first_name," ",last_name) as full_name"));
\\or
DB::table('articles')->select(\DB::raw("concat(first_name," ",last_name) as full_name"));
}
}Output will be :
select concat(first_name," ",last_name) as full_name from `articles`and Use in View:
Use:
@foreach ($articles as $article) {
{{ $article->full_name }}
}
@endforeachExample 2 – Mysql Concat with search in laravel eloquent
In this example we will search the concated columns using where
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Article;
class ArticleController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$Article = Article::select(\DB::raw("concat(first_name," ",last_name) as full_name"))
where(\DB::raw("concat(first_name," ",last_name)"),"like","%a%")->get();
return view("home",["articles"=>$Article])
}
}Output will be :
select concat(first_name," ",last_name) as full_name from `articles` where concat(first_name," ",last_name) like '%a%'Example 3 – Laravel Model accessor to concat the columns
In this example we will use Laravel Model accessor to concat the columns but this will not work with the search
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Notifications\Notifiable;
class Article extends Model {
use Notifiable;
public function getFullNameAttribute()
{
return "{$this->first_name} {$this->last_name}";
}
}
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Article;
class ArticleController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$Article = Article::all();
return view("home",["articles"=>$Article])
}
}then We can use as follow :
@foreach ($articles as $article) {
{{ $article->full_name }}
}
@endforeachAlso Read : Laravel max in eloquent query with example