Concatenate string to value inside Select MY SQL Eloquent Query Laravel

December 19, 2021

$return = ReturnDocument::where('contact_id',$contact->id)
        ->join('documents', 'return_documents.document_id', '=', 'documents.id')
        ->select([
            'return_documents.id as id',
            'return_documents.date as date',
            'documents.serial as serial',
            'documents.type as type',
            'return_documents.net_total as amount',
            'return_documents.note as description'
        ])
        ->where('return_documents.date', '<=', $to)
        ->where('return_documents.date', '>=', $from)
        ->when($draft == false, function ($query) {
            return $query->where('return_documents.confirmed_at','<>', null);
        });

This is a query I have written which works fine.
There could be three possible values for documents.type as type – Invoice, Quotation or Bill.

However, I need a small change. I need the type to be modified. I want to concatenate the word Return at the end of the type. Meaning:
if the value is Invoice I want the type to come out as InvoiceReturn

Looks like concat can do it, not sure how to implement concat inside the select array. I tried something like this but doesn’t work.

'concat(documents.type,"Return") as type'

>Solution :

You’ll need to wrap your type select entry in a call to DB::raw():

$return = ReturnDocument::where('contact_id',$contact->id)
    ->join('documents', 'return_documents.document_id', '=', 'documents.id')
    ->select([
        'return_documents.id as id',
        'return_documents.date as date',
        'documents.serial as serial',
        DB::raw('concat(documents.type, "Return") as type'),
        'return_documents.net_total as amount',
        'return_documents.note as description'
    ])
    ->where('return_documents.date', '<=', $to)
    ->where('return_documents.date', '>=', $from)
    ->when($draft == false, function ($query) {
        return $query->where('return_documents.confirmed_at','<>', null);
    });