How to use database functions in Kohana 3 ORM select query

3
votes

I'm using Postgres with Kohana 3's ORM module and would like to run a SELECT using a postgres function to convert values already in the database to lower case before doing the comparison.

In SQL I would write:

select * from accounts where lower(email) = '[email protected]';

In Kohana I would like to write something like this:

$user = ORM::factory('user')
    ->where('lower(email)', '=', strtolower('[email protected]'))
    ->find();

But this gives an error because ORM is trying to deduce the column name as 'lower(email)' rather than just 'email'.

I'm new to Kohana and ORM so alternatives that would give me the same result would be useful too.

3
postgresqlkohanakohana-3kohana-orm
Warning: you are about to replace a DB function (LOWER) with a PHP's one (strtolower) - they may or may not behave the same way depending on various factors (locales, encodings, nature of input data etc.). - Milen A. Radev
Good call, I added a custom DB::lower method to my application that escapes the passed in value and wraps it in lower so I can now write: ... ->where(DB::expr('lower(email)'), '=', DB::lower($email)) - Gerry Shaw

3 Answers

2
votes

Or IMHO even beter, try this:

$user = ORM::factory('user')
    ->where('LOWER("email")', '=', DB::expr("LOWER('[email protected]')"))
    ->find();

PS. I do not see any need to create a DB::lower() helper, but that might just be me...

EDIT:

$value = '[email protected]';

$user = ORM::factory('user')
    ->where('LOWER("email")', '= LOWER', (array) $value)
    ->find();

The query will become something like (havent used ORM in a while) "SELECT users.id, users.email FROM users WHERE LOWER("email") = LOWER ('[email protected]') LIMIT 1". Notice the space, I just updated some of my code to use this since I just figured out this posibility.

I hope you will be as happy with it as I am.

1
votes

try this:

$user = ORM::factory('user')
    ->where(DB::expr('lower(email)'), '=', strtolower('[email protected]'))
    ->find();
1
votes

I'm not completely happy with the use of a helper but I use it a couple other classes so it's nice to keep the logic in one location. Here is what I'm currently using.

class DB extends Kohana_DB
{
    public static function lower($value)
    {
        return DB::expr('lower('.Database::instance()->quote($value).')');
    }
}

class Model_User extends Model_Base
{
    public static function find_by_email($email)
    {
        $user = ORM::factory('user')
            ->where(DB::expr('lower(email)'), '=', DB::lower($email))
            ->find();
        return $user;
    }