Extending DB facade Laravel

Question

I would like to convert a timestamp and have some other values related to it. My question is how I can introduce my own method like DB::raw() that appends everything to the current select values. So, for instance, for something like this

$user = DB::table('users')
->select('*', DB::timestamp('timestamp_column', 'convert_timezone', 'called_as'))
->where('id', 1)->first();

Let's assume that I am trying to get the value for created_at column and it's called as converted_created_at and it should return something like below.

{
id: 1,
name:'John Doe',
converted_created_at: {
 'utc_time': 'created_at value as that is in utc by default',
 'converted_time': 'timestamp converted into user timezone',
 'diff': '10 hours ago' // difference between created_at and current timestamp 
}
}

So, how do I introduce my own method that does this? You can take example of any SQL database as you wish. I know I can do that with Model but I wanted to see how to approach this problem using a facade. Thank you in advance for your help.

I update my answer again again – Kamil Kiełczewski Oct 23 '18 at 10:14 — Kamil Kiełczewski, Oct 23 '18 at 10:14

Kamil Kiełczewski · Answer 1 · 2018-10-24T09:39:53.897

First look here: https://stackoverflow.com/a/40615078/860099 - Try this Extend DB facade:

namespace App\Facades; 
use Illuminate\Support\Facades\DB as DBBase;

class DB extends DBBase {...}

and in config/app.php change

'DB' => Illuminate\Support\Facades\DB::class,

to

'DB' => App\Facades\DB::class,`

(i write code from head)

Alternative: You can easily create helper class eg. DBTools witch static methods and inside that methods you will use DB and construct proper query. And use it like that DBTools::yourMethod(...)

As argument to that method you can give... QUERY here is example of calling this method

DBTools::yourMethod(User::query())->first();

and inside you can easyily manipulate that query and return updated version.

ALTERNATIVE: If your goal is to add some new filed in Model (json) that not exist in db but is generated then you can use $appends (look: mutators and appends)

class User extends Model
{
    protected $appends = ['converted_created_at'];

    ...

    public function getConvertedCreatedAtAttribute() {

        return ...; // return generated value from other fields/sources
    }

Thank you very much. This gives me the idea on how to proceed now. — Koushik Das, Oct 24 '18 at 07:14

score 1 · Answer 2 · answered Oct 24 '18 at 08:21

Thanks to @kamil for showing me the way. I am writing an answer in case anyone in the future finds this helpful. I have come up with my own method that helps to convert timezone easily without writing too much code inside select query for DB facade for PostgreSQL. I have created a file like this now.

<?php

namespace App\Custom\Facade;

use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class DBTools extends DB
{
    /**
     * Convert a timestamp
     * @param $timestamp - timestamp to be converted
     * @param bool $insideRaw - if this helper method is getting used inside DB::raw() method
     * @param null $timezone
     * @param null $format - time format
     * @param null $calledAs - column to called as
     * @return \Illuminate\Database\Query\Expression|string
     */
    public static function convertTime($timestamp, $insideRaw = false, $timezone = null, $format = null, $calledAs = null)
    {
        if (Auth::check()) {
            if (!$timezone)
                $timezone = Auth::user()->timezone;
            if (!$format)
                $format = Auth::user()->time_format;
        }
        $query = "to_char($timestamp at time zone '$timezone', '$format')" . ($calledAs ? " as $calledAs" : '');
        if (!$insideRaw) {
            return DB::raw($query);
        }
        return $query;
    }
}

Now this can be easily be called inside select for DB facade or inside DB::raw() in case you're handling much more complicated query. Hope this helps someone.

Extending DB facade Laravel

2 Answers2

Linked