Is it possible to add some string before Column value in Laravel?-CodePudding

So, i need to put AWS S3 URL before the attachment value. I use Laravel Query builder to get data from database.

Query

$query = DB::table('subscriber')->select(['ID as SBC_ID','SUBSCRIBER_NAME','ATTACHMENT_OTHERS'])
->get();

return $query;

Result

[   
    {
        "SBC_ID": 1,
        "SUBSCRIBER_NAME": "NAME",
        "ATTACHMENT_OTHERS": "uploads/1655362922-Annotation 2022-05-31 141139.png"
    },
    {
        "SBC_ID": 2,
        "SUBSCRIBER_NAME": "NAME 2",
        "ATTACHMENT_OTHERS": "uploads/1655362922-image.png"
    }
]

The ATTACHMENT_OTHERS is from Form Request and uploaded to AWS S3. I just insert the attachment path, not full S3 URL. But now, i need to return full URL.

Let say i put my AWS url in the .env file. AWS_URL=https://loremipsum.s3.ap-southeast-1.amazonaws.com

Is it possible to return the result from my query builder ?

CodePudding user response：

$query = DB::table('subscriber')->select(['ID as SBC_ID','SUBSCRIBER_NAME','ATTACHMENT_OTHERS'])
->get();
$AWS_URL = "https://loremipsum.s3.ap-southeast-1.amazonaws.com/";
foreach($query as $result)
{
$SBC_ID = $result->SBC_ID;
$SUBSCRIBER_NAME = $result->SUBSCRIBER_NAME;
$ATTACHMENT_OTHERS = $AWS_URL.$result->ATTACHMENT_OTHERS;
}

$query = ([
'SBC_ID' => $SBC_ID,
'SUBSCRIBER_NAME' => $SUBSCRIBER_NAME,
'ATTACHMENT_OTHERS' => $ATTACHMENT_OTHERS 
]);

return $query;

CodePudding user response：

You can use SQL's built-in function CONCAT() with DB facade, something like like this:

use Illuminate\Support\Facades\DB;
// ...
$aws_host = "https://loremipsum.s3.ap-southeast-1.amazonaws.com" . "/";
$query = DB::table('subscriber')->select([
    'ID as SBC_ID',
    'SUBSCRIBER_NAME',
    DB::raw('CONCAT("' . $aws_host . '", ATTACHMENT_OTHERS) AS ATTACHMENT_OTHERS'),
])->get();

CodePudding user response：

You can use Laravel Raw Expressions and MySQL CONCAT function. E.g.:

$query = DB::table('subscriber')
    ->select([
        'ID as SBC_ID', 
        'SUBSCRIBER_NAME', 
        DB::raw('CONCAT(\'https://loremipsum.s3.ap-southeast-1.amazonaws.com\', ATTACHMENT_OTHERS) as ATTACHMENT_OTHERS')
    ])->get();

Additionally, you put that url in .env

AWS_URL=https://loremipsum.s3.ap-southeast-1.amazonaws.com

and create new config file, e.g. config/aws.php

return [
    'attachment_url' => env('AWS_URL', 'AWS_URL'),
]

and then you can have a little neater code:

$query = DB::table('subscriber')
    ->select([
        'ID as SBC_ID',
        'SUBSCRIBER_NAME',
        DB::raw('CONCAT(\''.config('aws.attachment_url').'\', ATTACHMENT_OTHERS) as ATTACHMENT_OTHERS')
    ])->get();

If you returning this data via JSON API, then I suggest that you use Laravel API Resources