DMZ 1.7.1 (DataMapper OverZealous Edition)

[eluser]Benedikt[/eluser]
... Just found out that there is a "or_where_related"-function.

Sorry, missed that.

[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().

[eluser]modano[/eluser]
Hello,
Im having a bit of trouble getting my head around Many-to-Many realtionships in DMZ.
This is what I got,
Many Users can have Many movies.

In user model I got this:

Code:

var $has_many = array(
        'movies' => array(
            'class' => 'movie',
            'other_field' => 'movie_id'
        )
    );

And in movie model I got this:

Code:

var $has_many = array(
        'users' => array(
            'class' => 'user',
            'other_field' => 'user_id'
        )
    );

Is that correct? Is it enough info for it to write to the table users_movies (can the join table be named movies_users ?)

Second question,
In a html form where I create new USERS, i have a multi select list where one can choose movies. As it is a multi select, it will give php an array, so it has to be named movies[] doesnt it? How do I get that into the relationship in the model?

Right, time to continue trying but would be grateful for any help,
modano

[eluser]OverZealous[/eluser]
@rideearthtom

The problem may be using count, which has some oddness to it. Have you tried using ->get(1) (limit 1), and then check result_count, to see if that works for you?


modano
Not to be rude, but everything you are asking is explained in the manual, as well as there are numerous examples in the manual AND in the example app. There is no reason for the advanced relationships format with a normal many-to-many join. Please read the pages under Getting Started first. Also, don't name your fields users or movies, just name them user and movie. It tends to break things otherwise.

The HTMLForm extension is no longer supported.

[eluser]modano[/eluser]
I have spent lots of time reading the manual (believe i have read it all), and it is great, not saying different. Im sorry but i just couldnt get my head around it.
Will give it another go

[eluser]modano[/eluser]
*edit*
Sorted. sorry

[eluser]Unknown[/eluser]
I updated to_array() to turn related objects into nested data (rather than arrays of IDs) -- and automatically loads related tables' data if you don't specify fields when calling to_array().

Updated array.php attached (in a ZIP file).

Hope this helps you as much as it helps me!
Sean Gilbertson

[eluser]PoetaWD[/eluser]
Hello Phil,

This is kind a advanced problem that I cannot solve since I am not that good with SQL syntax...

My problem is that I want to perform a search in the entire object so I did like you said:

Code:

//Check if Search data is sent
            if($this->input->post('sSearch'))
            {                
                //Initiate a new SQL Query group
                $obj->group_start();
                
                //Create a loop to go over each column
                foreach($columns as $position => $column)
                {

                    //Add a the SQL statment for searching in that column to the Query Group
                    $obj->or_like($column, $this->input->post('sSearch'));
                }
                
                //Close the SQL Query Group
                $obj->group_end();
            }

It will perform a search in every column of the table (object).

My problem is when I include related objects...

Is it possible to search in there also using the above function?

It gives me this error when I try it:

Code:

A Database Error Occurred

Error Number: 1054

Unknown column 'avaliator_name' in 'where clause'

SELECT `browsers`.*, `avaliators`.`name` AS avaliator_name FROM (`browsers`) LEFT OUTER JOIN `avaliators` avaliators ON `avaliators`.`id` = `browsers`.`avaliator_id` WHERE ( `browsers`.`engine` LIKE '% Microso%' OR `browsers`.`browser` LIKE '% Microso%' OR `browsers`.`platform` LIKE '% Microso%' OR `browsers`.`version` LIKE '% Microso%' OR `avaliator_name` LIKE '% Microso%' ) ORDER BY `browsers`.`engine` asc

Is there a way of making it happen ?

Also, I am able to sort by the include_related column..... why not searching ?

Thanks.

[eluser]OverZealous[/eluser]
@Poetawd
If you want to search related items, you need to use or_like_related():

Code:

//Check if Search data is sent
if($this->input->post('sSearch'))
{                
    //Initiate a new SQL Query group
    $obj->group_start();
                
    //Create a loop to go over each column
    foreach($columns as $position => $column)
    {
        //Add a the SQL statment for searching in that column to the Query Group
        $obj->or_like($column, $this->input->post('sSearch'));
    }

    // You need some way to know which related object to look at
    // Here, I've chosen to have an array of related objects and their columns.
    foreach($related as $related_object => $columns)
    {
        foreach($columns as $column) {
            //Add a the SQL statment for searching in that column to the Query Group
            $obj->or_like_related($related_object, $column, $this->input->post('sSearch'));
        }
    }
          
    //Close the SQL Query Group
    $obj->group_end();
}

Hopefully that helps.

[eluser]PoetaWD[/eluser]
WHOA !

Thanks man ! I did NOT know that it was a function just for that !!

EDIT: Does this work for deep relationships ????

BTW, how is the new job going ?

I am still developing that code to generate grid data on-the-fly !

Here is how the code works:

Code:

//Variable with columns of the table
    var TableColums = [{ "sName": "engine" },
                       { "sName": "browser" },
                       { "sName": "platform" },
                       { "sName": "version" },
                       { "sName": "avaliator_name" } //Deep relationship column
                      ];
    
    //Initiate the table
        //The paramters are:
        //The id of the table container, the object to be loaded, the columns of the table
    
        initTableGrid("#browser", 'browser', TableColums);

That is it ! You will have a NICE working table-grid working just by addin that lines of code in the HTML.... no php coding required !

My next step is finding a way to add where clauses in the object query by the javascript....