Concat Columns with Laravel 5 Eloquent

CONCAT columns with Laravel 5 eloquent

You need to wrap your query in DB::raw:

$comp = Component::select(DB::raw("CONCAT('name','id') AS ID"))->get()

Also, note because you are doing your query like this, your model might behave differently, because this select removes all other fields from the select statement.
So you can't read the other fields from your model without a new query. So ONLY use this for READING data and not MODIFYING data.

Also, to make it in a nice list, I suggest you modify your query to:

$comp = Component::select(DB::raw("CONCAT('name','id') AS display_name"),'id')->get()->pluck('display_name','id');
// dump output to see how it looks.
dd($comp);// array key should be the arrray index, the value the concatted value.

How to concatenate columns with Laravel 4 Eloquent?

Tenant::select('Tenant_Id', DB::raw('CONCAT(First_Name, " ", Last_Name) AS full_name'))
    ->orderBy('First_Name')
    ->lists('full_name', 'Tenant_Id');

CONCAT with Laravel eloquent between two fields from table field

You'll have to join the tables yourself. Using with('other_table') does only eager load the related models, but not within one query. Each referenced model passed to with() will result in an additional query.

In your case, a solution could look like this:

$customer = Customer::query()
    ->join('customer_types', 'customers.customer_type_id', '=', 'customer_types.id')
    ->select([
        'customers.*',
        DB::raw("CONCAT(customers.name, ' - ', customer_types.type) as customer_plus_type"),
    ])
    ->first();

This will select all fields of the customers table as well as your custom field with the name customer_plus_type. Please make sure you change the customers.customer_type_id field in the join accordingly. From your question it is unclear how it was named.

By the way, if you still need to eager load the customerType relation, you can just add with('customerType') somewhere before the call to first().

Using CONCAT with Eloquent in Laravel

You can use join() for this as:

$cars   = Car::join('users', 'cars.user_id', '=', 'users.id')
            ->where(DB::raw('concat(users.first_name," ",cars.vin," ",cars.make)') , 'LIKE' , "%$search%")
            ->select('cars.*')
            ->paginate();

Concat two columns With laravel eloquent

One way to do this would be to use map. You can pass the $countryObj in to it and then simply concatenate the strings together:

$countryObj = Country::where('name', 'like', $country . '%')->first();

$cities = $countryObj->cities->map(function ($city) use ($countryObj) {
    return $countryObj->name . '-' . $city->name;
});

return response()->json(['cities' => $cities], 200);

Concat columns using “with()” function in Laravel Eloquent

The issue is that Eloquent will first query the users table, and only after, the persons table, so one query is not aware of the other and thus concatenating will not work.

You can use the Query Builder to do this using a join. It will be something like it:

$user = DB::table('users as u')
    ->join('persons as p', 'p.id', '=', 'u.person_id')
    ->selectRaw('CONCAT(p.name, " - ", u.code) as concatname, u.id')
    ->lists('concatname', 'u.id');

---

EDIT:
And, as suggested by @michel-ayres comment, as long as you have an acessor to the field:

public function getFullNameAttribute() { 
    return $this->attributes['name'] . ' - ' . $this->attributes['code'];
}

you can use your own model to perform the join and listing:

User::join('person','person.id','=','user.person_id')
    ->select('person.name', 'user.code', 'user.id')
    ->get()
    ->lists('full_name', 'id');

Laravel concat in query (where condition)

Laravel does some stuff behind the scenes like adding in the tick marks for you.

Fortunately, it also offers a couple of tools to still get the job done for you...

For this type of thing, DB::raw() usually works really well. Try something like this...

$query->orWhere(DB::raw("CONCAT(`nvp`, ' ', `vpv`)"), 'LIKE', "%".$this->searchNeedle."%");

---

Related Topics