Add a new column with a value to the select query in Laravel

Question

I would like to know how to create a default variable called "type" and set a value to "car" while doing a select join in Laravel.

Here is my code so far:

$items = DB::table('items')->orderBy('created_at', 'desc')
                           ->join('items_categories', 'items.item_category_id', '=', 'items_categories.category_id')
                           ->select( 
                                     'items.id as items___item_id',
                                     'items.item_title as items___item_title',
                                     'items_categories.id as items_categories___category_id',
                                     'items_categories.title as items_categories___category_title',
                                   )
                           ->take(20);

This works nice. However, I need to get/add a custom key and value for each record of this select so I can use it later in the template to filter stuff further.

So, I need to add a key called type with a value of car so in the print_r I will see type => car for every record and I can use this in my code.

How to do that?

Can I put that somhow in the select?

Like:

->select( 
           'items.id as items___item_id',
           'items.item_title as items___item_title',
           'items_categories.id as items_categories___category_id',
           'items_categories.title as items_categories___category_title',
           //something like this?
           'type' = 'car'
        )

Because right now I am getting this:

Array
(
    [0] => stdClass Object
        (
            [items___item_id] => 10
            [items___item_user_id] => 2
            [items___item_title] => A new blue truck
            [items_categories___category_id] => 1
            [items_categories___category_title] => Truck
        )

    [1] => stdClass Object
        (
            [items___item_id] => 11
            [items___item_user_id] => 2
            [items___item_title] => VW Tiguan
            [items_categories___category_id] => 1
            [items_categories___category_title] => SUV
        )

And I want to get this:

Array
(
    [0] => stdClass Object
        (
            [items___item_id] => 10
            [items___item_user_id] => 2
            [items___item_title] => A new blue truck
            [items_categories___category_id] => 1
            [items_categories___category_title] => Truck
            [type] => car
        )

    [1] => stdClass Object
        (
            [items___item_id] => 11
            [items___item_user_id] => 2
            [items___item_title] => VW Tiguan
            [items_categories___category_id] => 1
            [items_categories___category_title] => SUV
            [type] => car
        )

If possible, not in the model file, but during the one query, because it's only one time when I need this modification to be done.

Answer 1

Yep you can add a 'fixed' column neatly using the selectRaw method:

$items = DB::table('items')
   ->select('id','item_title')
   ->selectRaw("'car' AS type");

Answer 2

I came across the same requirement, I know it is late but it might help someone in the future:

Here is my code how to a add a custom field in Laravel eloquent model

$searchResults = Blog::with('categories')
                ->where('title', 'like', "%{$keyword}%")
                ->orWhere('description', 'like', "%{$keyword}%")
                ->select(['*', DB::raw("'{$keyword}' as keyword")])
                ->get();

Thanks

Answer 3

i implement it sth like this sample

    $first = Message::where('from_id', '=', Auth::user()->id)
        ->where('to_id', '=', $id)->get();
    foreach ($first as $f)
        $f->is_sent = true;

    $second = Message::where('from_id', '=', $id)
        ->where('to_id', '=', Auth::user()->id)->get();
    foreach ($second as $s)
        $s->is_sent = false;

    $chats = $first->merge($second)
        ->sortBy('created_at');

    return $chats->toJson();

you can do it by for eachafter get() method

Answer 4

You can use this method:

$data = DB::table('items')
   ->Select('items.id as items___item_id',
            'items.item_title as items___item_title');

# Add fake column you want by this command
$data = $data->addSelect(DB::raw("'fakeValue' as fakeColumn"));

$data = $data->orderBy('items.id')->get();

Enjoy it!

Answer 5

Here is the solution for this problem, hope it helps somebody else in the future.

$items = DB::table('items')->orderBy('created_at', 'desc')
                           ->join('items_categories', 'items.item_category_id', '=', 'items_categories.category_id')
                           ->select(DB::raw('"car" AS type, 
                                             items.id as items___item_id,
                                             items.item_title as items___item_title,
                                             items_categories.id as items_categories___category_id,
                                             items_categories.title as items_categories___category_title
                                            ')
                                   )
                           ->take(20);

Answer 6

You will want to create a model for your items table and query it that way. Using eloquent, you can create columns on the fly by adding column names to the $appends property and then defining a model attribute.

php artisan make:model Item

Any model automatically looks for a table that is the plural of the model name (Item looks for 'items'). In the Item model, add the following lines

/**
 * Append custom columns to the model
 * 
 * @var array
 */
protected $appends = ['type'];

/**
 * Define the type column to every Item object instance
 * 
 * @return string
 */
public function getTypeAttribute()
{
    return 'car';
}

Now update your query to use the model instead of DB::select. Make sure to use the model at the top of your controller

use App\Item; 

....

$items = Item::orderBy('created_at', 'desc')
                       ->join('items_categories', 'items.item_category_id', '=', 'items_categories.category_id')
                       ->select( 
                                 'items.id as items___item_id',
                                 'items.item_title as items___item_title',
                                 'items_categories.id as items_categories___category_id',
                                 'items_categories.title as items_categories___category_title',
                               )
                       ->take(20)->get();

You need to add get() as the final method when using a Model for it to return a collection vs. DB::select.