How to use database specific functions in Laravel Eloquent ORM for modular usage

Question

Im developing a project which uses ORM to make project run on every database system as much as we can. Project uses postgresql right now. Im wondering how to use database specific functions without losing ORM modularity.

For example: I have to use "extract" function for one query like so;

DELETE FROM tokens AS t WHERE (extract(epoch from t.created_at) + t.expires) < extract(epoch from NOW())

If i want to use model class to achieve this. Soon or late i need to write extract function where clause in raw format

Tokens::whereRaw('(extract(epoch from t.created_at) + t.expires) < extract(epoch from NOW())')->get();

If i use query builder

DB::table('tokens')->whereRaw('(extract(epoch from t.created_at) + t.expires) < extract(epoch from NOW())')->select()->get();

Same things happens

I need something like when i use postgresql ORM need to use EXTRACT() function or when i use mysql ORM need to use UNIX_TIMESTAMP() function

What the ways i can use to achieve this ?

Jarek Tkaczyk · Accepted Answer · 2015-01-15 13:54:05Z

This could go in the respective drivers, but Taylor Otwell's view on driver-specific functions is, that you simply should use raw statements, just like you do.

However in Eloquent you can pretty easily do it yourself:

// BaseModel / trait / builder macro or whatever you like
public function scopeWhereUnix($query, $col, $operator = null, $value = null)
{
    if (func_num_args() == 3)
    {
        list($value, $operator) = array($operator, '=');
    }

    switch (class_basename($query->getQuery()->getConnection()))
    {
        case 'MySqlConnection':
            $col = DB::raw("unix_timestamp({$col})");
            break;
        case 'PostgresConnection':
            $col = DB::raw("extract(epoch from {$col})");
            break;
    }

    $query->where($col, $operator, $value);
}

Now you can do this:

Tokens::whereUnix('created_at', 'value')->toSql();
// select * from tokens where unix_timestamp(created_at) = 'value'
// or
// select * from tokens where extract(epoch from created_at) = 'value'

You have a bit more complex condition, but you still can achieve that with a little bit of hack:

Tokens::whereUnix(DB::raw('created_at) + (expires', '<', time())->toSql();
// select * from tokens where unix_timestamp(created_at) + (expires) < 12345678
// or
// select * from tokens where extract(epoch from created_at) + (expires) < 12345678

Unfortunately Query\Builder (DB::table(..)) is not that easy to extend - in fact it is not extendable at all, so you would need to swap it with your own Builder class, what is rather cumbersome.

I based your logic to take care of this issue. You can look my answer. Unfortunately i cant +1 your answer since my rep is low :(

user1669496 · Accepted Answer · 2015-01-15 13:56:55Z

Take this logic out of the models.

Create a repository for Postgres, let's call it PostgresTokenRepository. The constructor of this repository should look like...

<?php

class PostgresTokenRepository implements TokenRepositoryInterface
{
    protected $token;

    public function __construct(Token $token)
    {
        $this->token = $token;
    }

    public function getTokens()
    {
        return $this->token->whereRaw('(extract(epoch from t.created_at) + t.expires) < extract(epoch from NOW())')->get();
    }
}

And you will need an interface... TokenRepositoryInterface

interface TokenRepositoryInterface
{
    public function getTokens();
}

Now you should be all set as far as the repository goes. If you need to do a MySQL implementation, just create a MysqlTokenRepository which will look similar except the getTokens() function would use UNIX_TIMESTAMP().

Now you need to tell Laravel that when you are looking for an implementation of TokenRepositoryInterface, it should return PostgresTokenRepository. For that, we will need to create a service provider.

<?php

class UserServiceProvider extends \Illuminate\Support\ServiceProvider
{
    public function register()
    {
        $this->app->bind('TokenRepositoryInterface', 'PostgresTokenRepository');
    }
}

And now the only thing left to do is add this Service Provider to the service providers array in config/app.php.

Now whenever you need this repository in your controllers, you can have them automatically injected. Here is an example...

class TokenController extends BaseController
{

    protected $token;

    public function __construct(TokenRepositoryInterface $token)
    {
        $this->token = $token;
    }

    public function index()
    {
        $tokens = $this->token->getTokens();

        return View::make('token.index')->with('tokens', $tokens);
    }
}

The purpose for doing it this way is when you want to start using the MySQL implementation, all you have to do is modify the service provider to return MysqlTokenRepository instead of PostgresTokenRepository. Or if you want to write a new implementation all together, it will all be possible without having to change production code. If something doesn't work, simply change that one line back to PostgresTokenRepository.

One other benefit that sold me is this gives you the capability of keeping your models and controllers very light and very testable.

Can Tecim · Accepted Answer · 2015-01-16 17:33:13Z

I ended up creating a global scope. Created a trait like ExpiresWithTimestampsTrait that contains the logic for whereExpires scope. The scope does adding where clause that specific to database driver.

public function scopeWhereExpired($query)
{
    // Eloquent class is my helper for getting connection type based on the @jarek's answer
    $type = Eloquent::getConnectionType($this);
    switch ($type) {
        case Eloquent::CONNECTION_POSTGRESS:
            return $query->whereRaw("(round(extract(epoch from (created_at)) + expires)) < round(extract(epoch from LOCALTIMESTAMP))");
            break;
        default:
            break;
    }
}

So i just need to use that trait on the model. I need to add just an "case" clause to whereExpires scope for support mysql with where clause in the future when i start using mysql

Thanks to everybody!

Collectives™ on Stack Overflow

How to use database specific functions in Laravel Eloquent ORM for modular usage

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related