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?

The Raw equivalent of what im after is `DB::query('SELECT title, url, image, concat(SUBSTRING_INDEX(description, " ",25),"...") AS description, category FROM hire_bikes ORDER BY RAND()');` — adam Kearsley, Jan 22 '13 at 15:10
You can edit your question. ;) By the way, usually you do such logic in the model, otherwise you lose the benefit of using fluent (being DBMS-agnostic) and you can go for the raw query in the first place, saving you time. — dualed, Jan 22 '13 at 15:14
Its a query i only need to run on one page, so no need for it to be in a model, it has no joins or dependencies etc... I know its easily done via a raw query and just as quick, i was just hoping to learn more about Fluent methods and see if it was possible to select multiple columns without selecting them all. Fluent only appears to let you have 1 column OR all columns, which is a shame! — adam Kearsley, Jan 22 '13 at 15:17
Of course you can select specific columns [`->get(array('id', 'email as user_email'));`](http://laravel.com/docs/database/fluent#get), however your question seems to be about using functions (which can be DBMS-specific) in your query, that is something completely different. — dualed, Jan 22 '13 at 15:23

score 38 · Answer 1 · edited Jan 12 '21 at 02:14

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.

This should have been awarded the answer, for suggestion a solution which does not involve raw() — Mawg says reinstate Monica, Jan 28 '15 at 14:03
You doesn't even have to use an array. Just simply pass it as a parameter like: `Car::select('brand_name AS brand', 'colour', 'horsepower AS hp')->first();`. You can do this because retrieving the function parameters (`$columns`) in `select` is done this way: `$this->columns = is_array($columns) ? $columns : func_get_args();`. — totymedli, Sep 25 '15 at 14:09

score 24 · Accepted Answer · edited Jan 12 '21 at 02:10

24

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

edited Jan 12 '21 at 02:10

Peter Mortensen

30,738
21
105
131

answered Jan 24 '13 at 13:11

Alex Naspo

2,052
1
20
37

score 0 · Answer 3 · answered Sep 03 '14 at 09:53

0

select(array(DB::raw('latitude as lat'), DB::raw('longitude as lon')))

answered Sep 03 '14 at 09:53

malhal

26,330
7
115
133

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

3 Answers3