🚀 go-pugleaf

Wayne Niddery [TeamB] wrote:
> Mitch wrote:
>> How can I easily query an integer column to get all the rows matching
>> an integer or also allow all rows to be fetched? EG:
>>
>> Item.Project (integer)
>> Item.Location (integer)
>>
>> (Case 1)
>> Select * from Item where
>> Project = :ProjectID and Location = :LocationID
>>
>> (Case 2)
>> Select * from Item where
>> Project = :ProjectID and Location = all locations
> 
> If you really cannot change the SQL to drop the location condition, then the 
> only way is to test the parameter in the SQL as well, but doing this will 
> mess up use of indexes and thus may result in poor performance - you'll have 
> to test this:
> 
> Select * from Item where
> (Project = :ProjectID or :ProjectID is null)
> and
> (Location = :LocationID or :LocationID is null)
> 
>

Thanks Wayne. This seems to be working:
WHERE
  ProjectID = :ProjectID and (LocationID = :LocationID or :all_locations 
=1 )

If I set :all_locations = 0 then only locationID shows.
If I set :all_locations = 1 then all locationIDs show.