How can i mix OR and AND in ORM queries

Question

I am developing a site using kohana 2.3 and am having trouble with a specific ORM query. Essentially what I am doing is the following query

SELECT *
   FROM 'records'
   WHERE ('ServRepSupervisor' = name AND 'Followup_read' = 0) OR ('ServRepSupervisor' = name AND `read` = 0)

when I try the ORM query like this...

$unread = ORM::factory('record')
   ->where(array('ServRepSupervisor' => Auth::instance()->get_user()->name, 'Followup_read' => 0))
   ->orwhere(array('ServRepSupervisor' => Auth::instance()->get_user()->name, 'read' => 0))
   ->find_all();

the query i end up with is

SELECT `records`.*
   FROM (`records`)
   WHERE `ServRepSupervisor` = 'name' AND `Followup_read` = 0
   OR `ServRepSupervisor` = 'name'
   OR `read` = 0

How can I rework that ORM query to produce the intended result?

EDIT: I managed to get it to work but it does not seem like a very elegant solution.

$unread = ORM::factory('record')
   ->where('(\'ServRepSupervisor\' = \'' . Auth::instance()->get_user()->name . '\' AND \'Followup_read\' = 0) OR (\'ServRepSupervisor\' = \'' . Auth::instance()->get_user()->name . '\' AND \'read\' = 0)')
   ->find_all();

This returns the dataset I want but it's just ugly code. There must be a better way of doing this.

Eirena

Answer 1

Well after a bit of research, I have found a patch for altering KO2 ORM to have open_paren() and close_paren() but the default behavior of orwhere in KO2 does not allow the desired operation so the parens will not help me. I have resigned myself to the fact that the detailed where() method that I am using is the best option for me given my time constraints. I have altered the query slightly to accommodate some peculiarities in my mysql instance.

This works and is the final query for this project.

$unread = ORM::factory('record')
   ->where('(ServRepSupervisor = \'' . Auth::instance()->get_user()->name . '\' AND Followup_read = 0) OR (ServRepSupervisor = \'' . Auth::instance()->get_user()->name . '\' AND records.read = 0)')
   ->find_all();

Eirena

Answer 2

ko3 has methods like ->where_open() and ->where_close(), ko2 doesn't

so that's the best solution

ps: i would cache the user btw

$current_user = Auth::instance()->get_user();

Offish

unfortunately I cannot "upgrade" this project to KO3 due to time constraints and caching the user isn't really needed. This application gets exactly 2 hits per week. But thank you just the same for the input. – Eirena

Answer 3

When using Kohana 2 you can do the following:

in file system/libraries/ORM.php:223 inside __call() add 'or_open_paren' in array at

if (in_array($method, array('open_paren', 'close_paren', 'enable_cache', 'disable_cache'))) {

or if that file is not yet patched, change case 0: to look like this:

case 0:
    if (in_array($method, array('open_paren', 'or_open_paren', 'close_paren', 'enable_cache', 'disable_cache'))) {
        $this->db->$method();
    } else {
        return $this->db->$method();
    }
break;

add the following code to system/libraries/Database.php

public function open_paren()
{
    $this->where[] = $this->get_where_count() ? 'AND (' : '(';
    return $this;
}
public function or_open_paren()
{
    $this->where[] = $this->get_where_count() ? 'OR (' : '(';
    return $this;
}
public function close_paren()
{
    $this->where[] = ')';
    return $this;
}
protected function get_where_count()
{
    $lastWhen = end($this->where);
    if ( $lastWhen === false )
        return 0;
    $lastWhen = trim( str_replace( array('AND ', 'OR '), '', $lastWhen ) );
    return $lastWhen === '(' ? 0 : count($this->where);
}

and replace all instances of count($this->where) with $this->get_where_count() in the same file except for in the new method get_where_count() (obviously) and and in the method delete($table, $where) (11 total replacements).

Greisen