Filter by optional attributes.

[Heston James - Cold Beans]

Good morning guys,

 

I’m looking for a way in which I can query my database for records which meet multiple, optional arguments. I’m looking to encapsulate access to this using a service layer, I want to create a method like this:

 

    def get_foos(self, foo_id="", foo_firstname="", foo_lastname=""):

        foos = session.query(Foo).filter_by(foo_id=foo_id, foo_firstname=foo_firstname, foo_lastname=foo_lastname)

               

        return foos

 

Now, the thing I’m struggling with is how to make all those attributes optional. Because at the moment I call that method like so:

 

get_foos(foo_id=2)

 

I get no results returned because SQLAlchemy is looking for records that not only have an id of 2 but also have a first and last name which is an empty string.

 

What I would ideally like to do is only have it filter by the arguments for getfoos() which are not empty strings. This would allow me to build a more reusable query method.

 

Is this possible? And how would one achieve it?

 

Cheers,

 

Heston

[a...@svilendobrev.com]

use keywordargs as dictionary, i.e.
...query.filter( **kwargs)
where the kwargs are a dict made by u containing only the required
fields. e.g.

kwargs={}
if foo_id: kwargs['fooid']=fooid
...
u can make it more generic (but more dangerous) by passing any kwargs
coming to your func straight down to filter():

def myfunc( self, whateverotherstuff, **filterkwargs):
.. query.filter( **filterkwargs)

or some combination inbetween.
dont forget that filter( ...) does AND of the criterias.

svil

[Heston James - Cold Beans]

Hi Svil:

> use keywordargs as dictionary, i.e.
>...query.filter( **kwargs)
> where the kwargs are a dict made by u containing only the required
> fields. e.g.
>
> kwargs={}
> if foo_id: kwargs['fooid']=fooid

That sounds like a fair enough solution to me, seems safer than the more
generic version.

Thanks for the tip mate, sounds really great. I'll play around with that
concept.

Heston

[Heston James - Cold Beans]

Hello Again Svil:

> That sounds like a fair enough solution to me, seems safer than the more
> generic version.
>
> Thanks for the tip mate, sounds really great. I'll play around with that
> concept.
>
> Heston

I've tested this little concept and it works really nicely :-D thanks.

One quick question I'd like to pick your brain on. With filter(), is that
filter applied at the SQL level? Or are _all_ records returned from the
database and then SQLAlchemy applies the filter?

I'm just trying to get an idea of performance when we have many records, I'd
hate to be pulling them _all_ from the database with each query.

Heston

[a...@svilendobrev.com]

On Tuesday 15 July 2008 15:30:31 Heston James - Cold Beans wrote:
> Hello Again Svil:
> > That sounds like a fair enough solution to me, seems safer than
> > the more generic version.
> >
> > Thanks for the tip mate, sounds really great. I'll play around
> > with that concept.
> >
> > Heston
>
> I've tested this little concept and it works really nicely :-D
> thanks.
>
> One quick question I'd like to pick your brain on. With filter(),
> is that filter applied at the SQL level?

yes.
pass an echo=True to the create_engine() (or whereever else u could
pass that) and u'll see the sql.

[Heston James - Cold Beans]

Hi,

> pass an echo=True to the create_engine() (or whereever else u could
> pass that) and u'll see the sql.

Ok, I see! Perfect!

I've just configured logging on this so I can keep track, looks excellent.

Heston