Search code examples
phpmysqlormkohana

How can i mix OR and AND in ORM queries

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.

Solution

  • 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();