Laravel Fluent queries - How do I perform a 'SELECT AS' using Fluent?

Question

I have a query to select all the rows from the hire table and display them in a random order.

DB::table('hire_bikes')->order_by(\DB::raw('RAND()'))->get();

I now want to be able to put

concat(SUBSTRING_INDEX(description, " ",25), "...") AS description

into the SELECT part of the query, so that I can select * from the table and a shortened description.

I know this is possible by running a raw query, but I was hoping to be able to do this using Fluent or at least partial Fluent (like above).

How can I do it?

Answer 1

You can actually use select AS without using DB::raw(). Just pass in an array into the select() method like so:

$event = Events::select(['name AS title', 'description AS content'])->first();

// Or just pass multiple parameters

$event = Events::select('name AS title', 'description AS Content');

$event->title;
$event->content;

I tested it.

Also, I'd suggest against using a DB:raw() query to perform a concatenation of your description field. If you're using an eloquent model, you can use accessors and mutators to perform this for you so if you ever need a limited description, you can simply output it in your view and not have to use the same query every time to get a limited description. For example:

class Book extends Eloquent
{
    public function getLimitedDescriptionAttribute()
    {
        return str_limit($this->attributes['description'], $limit = 100, $end = '...');
    }
}

In your view:

@foreach($books as $book)

    {{ $book->limited_description }}

@endforeach

Example Output (not accurate to limit):

The description of this book is...

I'd also advise against using the DB facade because it always utilizes your default connection. If you're querying a secondary connection, it won't take this into account unless you actively specify it using:

DB::connection('secondary')->table('hire_bikes')->select(['name as title'])->get();

Just to note, if you use a select AS (name AS title) and you wish to update your the model, you will still have to set the proper attribute name that coincides with your database column.

For example, this will cause an exception because the title column does not exist in your database table:

$event = Events::select('name AS title')->first();

$event->title = 'New name';

$event->save(); // Generates exception, 'title' column does not exist.

Answer 2

You can do this by adding a DB::raw() to a select an array in your fluent query. I tested this locally and it works fine.

DB::table('hire_bikes')
  ->select(
      array(
        'title',
        'url',
        'image',
        DB::raw('concat(SUBSTRING_INDEX(description, " ",25),"...") AS description'),
        'category'
      )
    )
  ->order_by(\DB::raw('RAND()'))
  ->get();