How to Concat Two Columns in Laravel Eloquent Query With Search ?

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 }}
}
@endforeach

Example 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 }}
}
@endforeach

Also Read : Laravel max in eloquent query with example

Related Posts