[Solar-talk] multiple SQL predicates

[Paul M Jones]

On 06 Dec 2007, at 12:46, Kilbride, James P. wrote:

> what's the problem with the first method using the models?

Not sure if there *is* a problem with it, per se ... the reason I  
suggested a "plain" SQL query was to see if it returned anything at  
all, and if it didn't, then the problem would have been with the  
values in the DB or something about the query itself.

Personally, I've never used "BETWEEN ... AND ..." clauses before.  Me,  
I'd use "field >= :low_end AND field <= "high_end", like so:

     $params = array('where' => array(
         'field >= ?' => $low_end,
         'field <= ?' => $high_end,
     ));

Or you could do this if you really like BETWEEN:

     $params = array(
         'where' => array('field BETWEEN :low_end AND :high_end'),
         'bind'  => array(
             'low_end' => '07710',
             'high_end' => '07734',
         )
     );


> Also how do
> you define difference between and and or statements when fetching
> models?  I noticed with the magic selects you can do and, anyway to  
> do an
>>
> or statement?

No support for ORs in magic selects.  :-(  The problem is that it's  
hard to generalize an OR because of its precedence level.  It might  
mean "foo = 1 AND (bar = 1 OR zim = 1)", *or* it might mean "(foo = 1  
AND bar = 1) OR zim = 1", or various other combinations.

If you need ORs, you'll have to write your own fetch method in your  
model, build the query semi-manually using Solar_Sql_Select inside  
that, and feed it back to the protected _fetchAll() method for it to  
fetch the values (and that'll join up the relateds for you too).  You  
can see an example of a complex semi-manual query in  
Solar_Model_Tags::fetchAllByOwnerHandle().

I know that's not a lot of help; if you want to, send along the query  
you want to execute and I can show you how to build it with  
Solar_Sql_Select.


> I'm trying to get folks away from writing the sql because
> then they get bound to specific conventions of the different  
> databases.

On that point I heartily agree, but there's no getting away from it  
entirely; you'll almost always need to write "snippets" of SQL, even  
if you're doing it through some abstraction layer that mimics SQL.


-- pmj