[ome-devel] findObjects or obtainDBH for difficult queries

[Ilya Goldberg]

On Mar 15, 2006, at 11:20 AM, august wrote:

>
> re all,
>
> I'm trying to figger something out regarding SQL querying in OME.
>
>
> I need to make an OR'ed select on two different parts of a query
> that is built using mostly ANDs.  For example,  I have a ST called
> @Testing with a bunch of fields such as "name", "type", "description",
> etc.   I also have a set of 3 fields called "cell_01", "cell_02",
> "cell_03" where each of the entries in that field are from the same  
> list (
> "microglia", "muller", "ganglion", etc.) .   I then have another  
> set of
> 3 fields called "color_01", "color_02", "color_03"  where each of  
> those
> fields have the same entries ( either "red", "green", "blue", etc).
>
>
> So, what I want to do is search @Testing for name AND type AND
> description AND ( cell_01 OR cell_02 OR cell_03 ) AND (color_01 OR
> color_02 OR color_03)
>
> make sense so far?
>
> in SQL, I beleive I would do something like:
>
> SELECT * from testing WHERE name="something" AND type="something" AND
> description="something" AND ( cell_01="microglia" OR  
> cell_02="microglia"
> OR cell_03="microglia")  AND ( color_01="red" OR color_02="red" OR
> color_03="red" );
>
>
> Is this possible with findObjects?

Not the way its currently implemented - at least not in a single  
query.  You could of course do it in 3 queries (actually, probably 9  
in this case).

Another option is to slightly alter your datamodel by using references:
CellType
	CellNumber integer (here you would put 1, 2 and 3 - if needed)
	CellType string (here you'd have "microglia", "ganglion", etc)
	TheTesting reference to Testing
CellColor
	ColorNumber integer (1, 2, 3 again - if needed)
	Color string ("red", "green", etc)
	TheTesting reference to Testing

Testing
	Same as it was, except without cell_01, etc and without color_01, etc

Now, here's your findObjects query:
$factory->findObjects ('@Testing', {
	CellTypeList.CellType => "microglia",
	CellColorList.Color   => "red"
	});

Notice that you don't have a field in Testing called "CellTypeList",  
but you have a reference from CellType to Testing, so the parser  
interprets CellTypeList as a "reverse" reference.  These are not only  
inferred fields in queries, they're also implemented as methods in  
perl, so you can call @colors = $myTesting->CellColorList()

Depending on the specifics of what you're trying to model, this might  
even be a better datamodel than specifying everything in a single  
ST.  Or maybe not.  Supporting OR is possible, but realistically,  
we've always been able to get around this limitation.  We could  
discuss how to get objects using your own SQL query if you want to do  
that instead.  The ugly part of that is that you have to reach down  
into the implementation details (i.e. tables/columns) instead of the  
nice and generic OO ST view of your datamodel.
-Ilya