Welcome Guest, Not a member yet? Register   Sign In
[SOLVED] Postgres typecasting in query
#1

[eluser]predat0r[/eluser]
Hi,
I have an AR query, where one of the joined column needs a typecasting like:

Code:
$this->db->join('customer', "customer.id::int = log.user_id", 'left')

First I had an error because the join method's regex could't process the ::int part

So I hacked (temporarily) a little bit the DB_active_record class, to add the ::int part to the end of customer.id but won't be better, I get the following error:

Code:
ERROR: column customer.id::int does not exist LINE 3: LEFT JOIN "customer" ON "customer"."id::int" = "log... ^

SELECT * FROM "log" LEFT JOIN "customer" ON "customer"."id::int" = "log"."user_id" WHERE "log"."user_id" = 1 LIMIT 15

Using typecasting eg. in select clause causes no problem..

Any idea how can I typecast in join clauses? I need AR solution, because its a query builder lib for Datatables..

thanks

EDIT: ok with the help of a postgres forum post, I solved the question..

Problem was with my hack that "customer"."id::int" should be "customer"."id"::int ...

If anybody interested, here's the *hack* for it. Please DO NOT MODIFY original classes, I'll also extend the DB_active_record class later.. (eg. http://dtownsend.co.uk/blog/2013/01/exte...ive-record)

It's just for curiosity:

DB_active_record.php, join method:

Code:
if( strpos($cond, '::') > 0)
  {
   if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match))
   {    
    // 1. extract typecast from $match[3]
    $t = explode('=', $match[3]);
    $typecast = trim($t[0]);
    // 2. remove 'typecast =' from $match[3]
    $match[3] = trim($t[1]);    
    // 3. replace $match[2] content with =
    $match[2] = ' = ';

    $match[1] = $this->_protect_identifiers($match[1]);
    $match[3] = $this->_protect_identifiers($match[3]);

    // 4. add :: and typecast to the end of $match[1]
    $match[1] .= '::'.$typecast;

    $cond = $match[1].$match[2].$match[3];
   }
  }
  else
  {
   if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match))
   {
    $match[1] = $this->_protect_identifiers($match[1]);
    $match[3] = $this->_protect_identifiers($match[3]);

    $cond = $match[1].$match[2].$match[3];
   }
  }




Theme © iAndrew 2016 - Forum software by © MyBB