Asked  8 Months ago    Answers:  5   Viewed   67 times

Lets say we are using Laravel's query builder:

$users = DB::table('really_long_table_name')
           ->select('really_long_table_name.id')
           ->get();

I'm looking for an equivalent to this SQL:

really_long_table_name AS short_name

This would be especially helpful when I have to type a lot of selects and wheres (or typically I include the alias in the column alias of the select as well, and it get's used in the result array). Without any table aliases there is a lot more typing for me and everything becomes a lot less readable. Can't find the answer in the laravel docs, any ideas?

 Answers

58

Laravel supports aliases on tables and columns with AS. Try

$users = DB::table('really_long_table_name AS t')
           ->select('t.id AS uid')
           ->get();

Let's see it in action with an awesome tinker tool

$ php artisan tinker
[1] > Schema::create('really_long_table_name', function($table) {$table->increments('id');});
// NULL
[2] > DB::table('really_long_table_name')->insert(['id' => null]);
// true
[3] > DB::table('really_long_table_name AS t')->select('t.id AS uid')->get();
// array(
//   0 => object(stdClass)(
//     'uid' => '1'
//   )
// )
Wednesday, March 31, 2021
 
Crashthatch
answered 8 Months ago
91

lists() turns the resulting collection into an array with key value. You can only have two database columns in there. Otherwise you have to use select() but then you will get a collection of models not just an array.

$categories = CategoryModel::select('catID', 'catName', 'imgPath')
                           ->where('catType', '=', 'Root')
                           ->get();
Wednesday, March 31, 2021
 
steros
answered 8 Months ago
41

According to Laravel's Eloquent documentation for querying relations (look for the "Querying Relationship Existence" subheading), this should work:

User::has('comments')->paginate(20);
Wednesday, March 31, 2021
 
muffe
answered 8 Months ago
77

You could create an accessor on the Product model that did the check for you. Works the same if you just wanted to define it as a method, also (good for if you want to abstract some of the Eloquent calls, use an interface for your Product in case you change it later, etc.)

/**
 * Create a custom thumbnail "column" accessor to retrieve this product's
 * photo, or a default if it does not have one.
 * 
 * @return string
 */
public function getThumbnailAttribute()
{
    $default = $this->defaultPhoto;

    return ( ! is_null($default))
        ? $default->thumb
        : '/products/default/thumb.jpg';
}

You might also want to look into Presenters. A bit overkill for some situations, but incredibly handy to have (and abstract things like this away from your models).

Wednesday, March 31, 2021
 
JohnnyW
answered 8 Months ago
99

This is how you do a subquery where:

$q->where('price_date', function($q) use ($start_date)
{
   $q->from('benchmarks_table_name')
    ->selectRaw('min(price_date)')
    ->where('price_date', '>=', $start_date)
    ->where('ticker', $this->ticker);
});

Unfortunately orWhere requires explicitly provided $operator, otherwise it will raise an error, so in your case:

$q->orWhere('price_date', '=', function($q) use ($start_date)
{
   $q->from('benchmarks_table_name')
    ->selectRaw('min(price_date)')
    ->where('price_date', '>=', $start_date)
    ->where('ticker', $this->ticker);
});

EDIT: You need to specify from in the closure in fact, otherwise it will not build correct query.

Monday, June 14, 2021
 
Powering
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share