是否可以在Laravel中的列值之前添加一些字符串？

Question

因此，我需要将AWS S3 URL放在附件值之前。我使用 Laravel查询构建器从数据库中获取数据。

查询

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

return $query;

结果

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

attactment_others 来自表单请求，并将其上传到AWS S3。我只是插入附件路径，而不是完整的S3 URL。但是现在，我需要返回完整的URL。

假设我将AWS URL放在 .env 文件中。 aws_url = https：//loremipsum.s3.ap-southeast-1.amazonaws.com

是否可以从我的 QUERY BUERDER 中返回结果？

Answer 1

您可以使用 laravel raw表达式和 mysql contat函数。例如：

$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();

此外，您将该URL放入.env中

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

并创建新的配置文件，例如config/aws.php

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

，然后可以有一个小的代码：

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

---

如果您通过JSON API返回此数据，那么我建议您使用 laravel api api Resources

Answer 2

您可以使用SQL的内置带有 db立面，类似这样的东西：

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();

Answer 3

$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;