DataMapper DMZ - constructing filter queries - HELP!

[eluser]madwhistler[/eluser]
Kudos & genuflections, first off, to OverZealous and all others who've helped organize and maintain a great product. It's a huge boon to info-modelers suffering with PHP.

My problem: I'm trying to construct complex queries to filter results (a pretty standard multi-field search), but with some filters drawn from related tables.

My model (simplified): Asset has-one Property, Property has-one Address. It's has-many in the other direction. I need to filter which Assets are in Georgia. One way:

Code:

$assets = new Asset();
$a = new Address();
$a->where('state','GA')->get();
$a->property->get();
$assets = $a->property->asset->get();

All well and good, but it's not exactly a SQL Where clause. What if I now want to add a second filter, for Asset->value > $10000? That's an easy query:

Code:

$assets->where('value >','10000')->get();

But, how can I construct a way to flexibly 'AND' these queries together? If I could figure out a way to do the first query as a WHERE clause, I could just construct a bunch of $asset->where() calls and run one big get() to apply the whole multi-filter.

Unfortunately, these relations have got me bollixed up. I don't really want to get into the horrible world of subqueries, and I'm not sure they'd help anyway. Any advice?

Thanks in advance...

[eluser]WanWizard[/eluser]
Read the section in the manual on querying deep relationships.

you can probably do something like

Code:

$assets = new Asset();
$assets->where_related('property/address', 'state', 'GA')->get();

(note: I write this before my morning coffee...)

[eluser]madwhistler[/eluser]
It works like a screaming banshee. Profound thanks to you for saving my career before your morning cup.
If I had bandwidth, I'd give you some...

[eluser]NeilyM[/eluser]
I've been getting my mind round some of the details on querying deep releationships.

One thing has flumoxed me though.

In the above example, how would you select all assets that had no related property?

If I get an answer to this, my career could be saved.

Cheers,

NeilyM

[eluser]madwhistler[/eluser]
I'm kind of hoping WanWizard might weigh in on this. There really isn't a "where_not_related_xxx" method, and I'm scratching my head on exactly how to do this query.

There might be a test for where_related('property','id =', NULL)?

It might also be possible to use the all_to_array() method from the array extension - generate one array for all records, another for the related records, and subtract, but that seems pretty inefficient.

Really what you want is an ability to generate the SQL 'where not in' clause, I think.

[eluser]NeilyM[/eluser]
Your thinking followed the same path as mine, first hoping there was a where_not_related function then thinking a where_not_in would do but finding no such function.

Hopefully we're missing something obvious and someone will humiliate us with the solution. Bring it on!

[eluser]madwhistler[/eluser]
I actually had the occasion to do one of these "not related" queries as a filter.
What I found was that where_related('property','id',NULL) seems to work.
Not exactly well-documented, but it seems to do the trick.

[eluser]WanWizard[/eluser]
I will add this to the documentation as a possible solution.

[eluser]NeilyM[/eluser]
Thanks for the information guys.

I just implemented the where_related('property','id',NULL) solution and it does seem to work a treat.