OR in Where Conditions

martin@mp-networks.net

Hey @ all,
in some of my projects I had the problem, that there is no or support in where clauses like:

WHERE name='allan' OR ( location='Scotland' AND name='Carter' ) or
WHERE name='allan' AND ( location='Scotland' OR location='Canada' )

So I updated the php/Editor/Editor.php and the php/Database/Query.php files and got a solution for the problem.
You can download it here: http://www.megafileupload.com/en/file/545771/Where-with-or-zip.html

How it works:

The following will produce
WHERE name='allan' AND ( location='Scotland' OR location='Canada' )

$query
->where( 'name', 'allan' )
->where( function ($q) {
$q->where( 'location', 'Scotland','=' );
$q->where( 'location', 'Canada','=',true );
});

The following will produce
WHERE name='allan' OR ( location='Scotland' AND name='Carter' )

$query
->where( 'name', 'allan' )
->where( function ($q) {
$q->where( 'location', 'Scotland' );
$q->where( 'name', 'Carter' );
},null,null,true );

Have fun with it ;)
Martin

allan

Hi Martin,

Nice one! Thanks for sharing this with us. Have you made any modifications to the insert / update parts of the code to handle this as well? The main reason I've got AND only support at the moment is that it is easy to add to the insert / update!

Allan

martin@mp-networks.net

Hi Allan,
no, at the moment I'm involved in another project, so my time is limited....
Where is the Insert/Update part in your code? Maybee I can look for it in the next weeks...

allan

If you have a look at the _insert and _update functions you'll be able to see the flow of the code. It ends up in the function _insert_or_update_table where there is a check to see if _where is used and if so, setting the required fields.

Allan

rainolf

Hi,
i'm really intrested on this.
However seems to nor work for me.

Both query doesn't produc eany result.

Is there any update on OR in WHERE clause at mainstream development?

Thank you

rainolf

In deep i would like to have something like that:
->where( 'owner', $sql_value1,'=' )
->where( function ($q) {
$q->where( 'owner', 'public','=',true);
} )

but it doesn't produce the OR condition...

Anything wrong?

Thank you

allan

Use the or_where() method to built up an OR condition.

Allan

rainolf

Thank you,
could you pointing me to the right syntax or a link where i can find any info?

And ....sice which version or_where() is present?ù

Thank you

rainolf

I've found some of yours comments .
The or_where() works fine .. however i need to change this:
->where( function ( $q ) {
$q
->or_where( 'owner', 'john')
->or_where( 'owner', 'public');
} )

into this, passing variables instead of value:
->where( function ( $q ) {
$q
->or_where( 'owner', $value1)
->or_where( 'owner', $value2);
} )

but as u see the function returns me undefined variabale....

Any way to do this?

Thank you

allan

In PHP you need to use the use() option of a closure. See the PHP documentation for more details.

e.g.:

function ($q) use( $value1, $value2 ) {
   ...
}

Allan

rainolf

Oh great...works nice....

Thank again..