DMZ 1.7.1 (DataMapper OverZealous Edition)

[eluser]rideearthtom[/eluser]
Still having issues with subqueries, as posted before (simplified with other clauses removed):

Code:

$accepted_documents = new Document();
$accepted_documents->select('id')
    ->where_related('sme_profile', 'id', $this->sme_profile->id);
$documents = new Document();
$documents->where_not_in_subquery('id', $accepted_documents);
return $documents->count() > 0 ? TRUE : FALSE;

Gives me this:

A Database Error Occurred

Error Number: 1054

Unknown column 'tdb_documents_subquery.id' in 'field list'

I also tried

Code:

$documents->where_related('sme_profile', 'id !=', $this->sme_profile->id);

but this just returns zero rows, despite there being no related sme_profile rows with the given ID.

Here's the full generated query:

Code:

SELECT COUNT(*) AS `numrows`
FROM (`tdb_documents`)
LEFT OUTER JOIN `tdb_join_documents_products` tdb_join_documents_products ON `tdb_documents`.`id` = `tdb_join_documents_products`.`document_id`
LEFT OUTER JOIN `tdb_products` tdb_products ON `tdb_products`.`id` = `tdb_join_documents_products`.`product_id`
LEFT OUTER JOIN `tdb_join_products_purchases` product_tdb_join_products_purchases ON `tdb_products`.`id` = `product_tdb_join_products_purchases`.`product_id`
LEFT OUTER JOIN `tdb_purchases` product_tdb_purchases ON `product_tdb_purchases`.`id` = `product_tdb_join_products_purchases`.`purchase_id`
LEFT OUTER JOIN `tdb_join_documents_locations_projects` tdb_join_documents_locations_projects ON `tdb_documents`.`id` = `tdb_join_documents_locations_projects`.`document_id`
LEFT OUTER JOIN `tdb_join_documents_sme_profiles` tdb_join_documents_sme_profiles ON `tdb_documents`.`id` = `tdb_join_documents_sme_profiles`.`document_id`
LEFT OUTER JOIN `tdb_sme_profiles` tdb_sme_profiles ON `tdb_sme_profiles`.`id` = `tdb_join_documents_sme_profiles`.`sme_profile_id`
WHERE  (
`product_tdb_purchases`.`sme_profile_id` = 1
OR tdb_join_documents_locations_projects.id IN (SELECT `tdb_join_documents_locations_projects`.`id`
    FROM (`tdb_join_documents_locations_projects`)
    LEFT OUTER JOIN `tdb_projects` tdb_projects ON `tdb_projects`.`id` = `tdb_join_documents_locations_projects`.`project_id`
    LEFT OUTER JOIN `tdb_join_projects_purchases` project_tdb_join_projects_purchases ON `tdb_projects`.`id` = `project_tdb_join_projects_purchases`.`project_id`
    LEFT OUTER JOIN `tdb_purchases` project_tdb_purchases ON `project_tdb_purchases`.`id` = `project_tdb_join_projects_purchases`.`purchase_id`
    WHERE `project_tdb_purchases`.`sme_profile_id` = 1
    AND `tdb_join_documents_locations_projects`.`location_id` = '1')
)
AND `tdb_sme_profiles`.`id` != 1

There are no related sme_profiles with ID=1, but remove the final WHERE and I get 8 rows returned.

So basically there are two ways to get the result set I want - the subquery way and the join way - and neither of them work as expected, although I imagine the join way is to do with my query, not DMZ. But it looks like the subquery problems is a DMZ bug.

EDIT: Just realised why the join method won't work - it's because I want to select all documents WITHOUT a related sme_profile where ID = 1. This query will select documents which HAVE a related sme_profile where id != 1. Hence zero rows.

The idea is that an sme_profile will be related to zero-many documents, and I want to select this set's relative complement which also satisfies numerous other conditions.

Looks like the non-functional subquery is my only option that fits the DMZ way of doing things. I guess I'm going back to building an array of document IDs elsewhere and passing it to where_not_in().