where and where_raw on same query

[jrivero]

I think that using where_raw clause eliminates where clauses. Is possible this?

$items = ORM::for_table('profiles');
if ($gender) $items = $items->where("gender", $gender);    
if ($region) $items = $items->where("region_slug", $region);    
if ($age_from AND $age_to) $items = $items->where_raw('(age >= ? AND age <= ?)', array($age_from, $age_to));    
$items = $items->offset(0)->limit(15);
$items = $items->find_many();

There is another way to do it?

Regards

[j4mie]

Hi,

This behaviour is really by design - once a raw where clause has been introduced to the query, Idiorm really has no way to know how to combine the where clauses correctly. Should the raw part be placed after the others, or before? How should it be combined with the existing parts of the query?

It's possible that I'm being too cautious here, but I suspect that changing the API in this case would require some significant refactoring, and may result in the API becoming unpredictable.

I'm open to suggestions / forks if you can think of a clean way of adding this. Otherwise, I'll do some experimentation when I get some free time and see if I can come up with any ideas.

[j4mie]

The latest HEAD contains some extra methods which should help in your particular case, by removing the need to use where_raw at all:

if ($age_from AND $age_to) $items = $items->where_gte('age', $age_from)->where_lte('age', $age_to);    

I'm not closing this issue just yet because this doesn't actually address the problem itself..

[jrivero]

great j4mie!

[j4mie]

FYI, I have just pushed a bunch of changes which fix this issue.

where_raw now acts like any other where_* method and can be chained alongside other methods. The WHERE fragments are just ANDed together. Simple approach.