Add a new column with a value to the select query in Laravel
Solution 1
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!
Solution 2
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.
Solution 3
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);
John Doeherskij
Updated on July 09, 2022Comments
-
John Doeherskij almost 2 years
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 ofcar
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.