EllisLab text mark
Advanced Search
12 of 62
12
   
DMZ 1.7.1 (DataMapper OverZealous Edition)
Posted: 16 April 2010 08:01 PM   [ # 171 ]   [ Rating: 0 ]
Joined: 2009-12-15
35 posts

@rideearthtom

Right, that was probably unclear.  I was just referring to the mistakes in my earlier post, not to any negative tone I was inferring from your response or anything like that.  Just so we’re clear.

@Overzealous and whoever else

So, to anyone interested in many-to-many reciprocal self relationships (say, documents related to each other where saving/deleting the relationship in one direction should also maintain the relationship in the reverse direction), the following may be useful. And when Phil gets some time, it might be small enough and handy enough to warrant permanent inclusion in DMZ.  It’s one of those things that seems to come up fairly often, and I think it makes sense for DMZ to manage it.

Anyway, the implementation that seemed convenient to me was to set up an additional option (‘reciprocal’) in the $has_many definition.

class Document extends DataMapper
{
    
public $has_many = array('comment',
                
'related_document'=>array(
                        
'class'=>'document',
                        
'other_field'=>'document',
                        
'reciprocal'=>TRUE
                    
),
                 
'document'=>array(
                        
'other_field'=>'related_document'
                    
)
        );

The changes necessary are pretty simple.  In DMZ 1.7.1:

Starting at Line 649

if( ! isset($rel_props['join_other_as']))
{
    
// add the key as the model to use in queries if not set
    
$rel_props['join_other_as'$related_field;
}
if(isset($rel_props['reciprocal']))
{
    
// only allow a reciprocal relationship to be defined if this is a has_many self relationship
    
$rel_props['reciprocal'= ($rel_props['reciprocal'&& $arr == 'has_many' && $this_class == $rel_props['class']);
}
else
{
    $rel_props[
'reciprocal'FALSE;
}
$new[$related_field] 
$rel_props
Starting at Line 4847

else if (isset($object->has_many[$other_field]))
{
    
// We can add the relation since this specific relation doesn't exist, and a "has many" to "has many" relationship exists between the objects
    
$this->db->insert($relationship_table$data);

    
// Self relationships can be defined as reciprocal -- save the reverse relationship at the same time
    
if ($related_properties['reciprocal'])
    
{
        $data 
= array($this_model '_id' => $object->id$other_model '_id' => $this->id);
        
$this->db->insert($relationship_table$data);
    
}

    
return TRUE;
Starting at Line 4980

else
{
    $data 
= array($this_model '_id' => $this->id$other_model '_id' => $object->id);

    
// Delete relation
    
$this->db->delete($relationship_table$data);

    
// Delete reverse direction if a reciprocal self relationship
    
if ($related_properties['reciprocal'])
    
{
        $data 
= array($this_model '_id' => $object->id$other_model '_id' => $this->id);
        
$this->db->delete($relationship_table$data);
    
}

Unless I’m mistaken, that’s all it takes, but of course I’m open to ideas if I’ve missed an important detail or there’s some other avenue for improvement.

 
Posted: 17 April 2010 07:29 PM   [ # 172 ]   [ Rating: 0 ]
Joined: 2007-07-19
10 posts

Hi guys,

A little problem… I cannot “translate” a SQL query into DMZ format.

I would like to have this query :

SELECT *
FROM (`users`)
WHERE `users`.`activated` = 0
AND UNIX_TIMESTAMP(`users`.`created`) < 1271370014 

With the following, I cannot use the < operator :

$this->where('activated'0);
$this->where_func('UNIX_TIMESTAMP', array('@created'), $max_time); 

It returns me this :

SELECT *
FROM (`users`)
WHERE `users`.`activated` = 0
AND UNIX_TIMESTAMP(`users`.`created`) = 1271370014 

How can I do ?

 
Posted: 19 April 2010 12:20 PM   [ # 173 ]   [ Rating: 0 ]
Avatar
Joined: 2008-10-08
1039 posts

@All
Sorry I haven’t been around.  I must have forgotten to visit the forums, and the forum stopped emailing me.  smile

@TheJim
That’s great.  That feature has been on my todo list for some time, but I never bothered to add it.  I’ll try to get it into the next release, but that might be awhile.  I might be able to work on DMZ next week, when I’m back home.

@Lord_Jago
You can make it work, but it’s a little hackish, like this:

$this->where('activated'0);
$func $this->func('UNIX_TIMESTAMP', array('@created'));
$this->where($func ' <' $max_time); 

This is a flaw in the SQL function code design, but I don’t really have an easy solution at this time.

 Signature 

Phil DeJarnett
  OverZealous Creations, LLC

 
Posted: 20 April 2010 03:19 PM   [ # 174 ]   [ Rating: 0 ]
Joined: 2007-07-19
10 posts
OverZealous - 19 April 2010 04:20 PM

@Lord_Jago
You can make it work, but it’s a little hackish, like this:

$this->where('activated'0);
$func $this->func('UNIX_TIMESTAMP', array('@created'));
$this->where($func ' <' $max_time); 

This is a flaw in the SQL function code design, but I don’t really have an easy solution at this time.

Thank you, it works perfectly. I hope you’ll find a way to fix this little flaw.
And thanks for DMZ !!

 
Posted: 21 April 2010 11:02 AM   [ # 175 ]   [ Rating: 0 ]
Avatar
Joined: 2007-08-29
94 posts

Congrats to the new version 1.7.1!!

I just upgraded my library to use the “get_paged_iterated” method.

This is my controller:

$u = new User();
$u->where('username'$this->input->post('username'));
$u->where('password'$this->input->post('password'));
$u->get();

if ( 
$u->exists() ) {
  $u
->post->where('status <'9);
  
$u->post->order_by('_created''DESC');
  
$posts $u->post->get_paged_iterated();

I have a user and log him in. If the user exists (means he is logged in) I want to get all posts of this user in a pages and iterated way.

With the old DMZ version I just used “get()” and did the paging in a different way.

Now when I run the code I get the following error:

Error Number: 1054

Unknown column ‘tbl_posts.status’ in ‘where clause’

SELECT COUNT(*) AS `numrows` FROM (`jn_posts_users`) WHERE ( `tbl_posts`.`status` < 9 ) AND `user_id` = 1

The column status exists in tbl_posts.

Is there a misunderstanding in this feature or what could be the problem?
Thanks for a hint.

 Signature 

Visit the german-speaking CI-community at http://codeigniter.ch
FreeBSD- and Server-Tutorials at http://serverzeit.de
The event-calendar for Mini-Drivers at http://miniyourlife.com

 
Posted: 21 April 2010 12:04 PM   [ # 176 ]   [ Rating: 0 ]
Avatar
Joined: 2008-10-08
1039 posts

@Benedikt

It looks like that is a bug in the way the get_paged method handles counting the results.  I’ll take a look at it when I get some time.

For now, I don’t have a workaround that allows you to use get_paged.  :(

 Signature 

Phil DeJarnett
  OverZealous Creations, LLC

 
Posted: 21 April 2010 12:09 PM   [ # 177 ]   [ Rating: 0 ]
Avatar
Joined: 2007-08-29
94 posts

Too bad. Is there an older version where get_paged worked?

 Signature 

Visit the german-speaking CI-community at http://codeigniter.ch
FreeBSD- and Server-Tutorials at http://serverzeit.de
The event-calendar for Mini-Drivers at http://miniyourlife.com

 
Posted: 21 April 2010 12:40 PM   [ # 178 ]   [ Rating: 0 ]
Avatar
Joined: 2008-10-08
1039 posts

@Benedikt

No, it’s new to 1.7, and I didn’t change the way it works with 1.7.1.  The bug is because get_paged does two things:

1) It gets a count of how many items match the non-paged query.
2) It gets all of the items for the current page.

The first step is the issue, because it is trying to count the total number of items.

BUT, I just thought of a possible work-around!  cheese

Re-write your queries like this, and it might work:

if ( $u->exists() ) {
  $posts 
= new Post();
  
$posts->where_related($u);
  
$posts->where('status <'9);
  
$posts->order_by('_created''DESC');
  
$posts->get_paged_iterated();

Basically, you are tricking DMZ to not try to be so smart and short-circuit the count query.

Let me know if that works!  (If so, I think I need to fix the count query!)

 Signature 

Phil DeJarnett
  OverZealous Creations, LLC

 
Posted: 21 April 2010 01:12 PM   [ # 179 ]   [ Rating: 0 ]
Avatar
Joined: 2007-08-29
94 posts

I knew you wouldn’t leave me alone with this smile

Yes, it works.

Thanks a lot!!

 Signature 

Visit the german-speaking CI-community at http://codeigniter.ch
FreeBSD- and Server-Tutorials at http://serverzeit.de
The event-calendar for Mini-Drivers at http://miniyourlife.com

 
Posted: 22 April 2010 01:21 PM   [ # 180 ]   [ Rating: 0 ]
Avatar
Joined: 2007-08-29
94 posts

... Just found out that there is a “or_where_related”-function.

Sorry, missed that.

 Signature 

Visit the german-speaking CI-community at http://codeigniter.ch
FreeBSD- and Server-Tutorials at http://serverzeit.de
The event-calendar for Mini-Drivers at http://miniyourlife.com

 
Posted: 23 April 2010 04:15 AM   [ # 181 ]   [ Rating: 0 ]
Joined: 2009-12-16
24 posts

Still having issues with subqueries, as posted before (simplified with other clauses removed):

$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() > TRUE FALSE

Gives me this:

A Database Error Occurred

Error Number: 1054

Unknown column ‘tdb_documents_subquery.id’ in ‘field list’

I also tried

$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:

SELECT COUNT(*) AS `numrows`
FROM (`tdb_documents`)
LEFT OUTER JOIN `tdb_join_documents_productstdb_join_documents_products ON `tdb_documents`.`id` = `tdb_join_documents_products`.`document_id`
LEFT OUTER JOIN `tdb_productstdb_products ON `tdb_products`.`id` = `tdb_join_documents_products`.`product_id`
LEFT OUTER JOIN `tdb_join_products_purchasesproduct_tdb_join_products_purchases ON `tdb_products`.`id` = `product_tdb_join_products_purchases`.`product_id`
LEFT OUTER JOIN `tdb_purchasesproduct_tdb_purchases ON `product_tdb_purchases`.`id` = `product_tdb_join_products_purchases`.`purchase_id`
LEFT OUTER JOIN `tdb_join_documents_locations_projectstdb_join_documents_locations_projects ON `tdb_documents`.`id` = `tdb_join_documents_locations_projects`.`document_id`
LEFT OUTER JOIN `tdb_join_documents_sme_profilestdb_join_documents_sme_profiles ON `tdb_documents`.`id` = `tdb_join_documents_sme_profiles`.`document_id`
LEFT OUTER JOIN `tdb_sme_profilestdb_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_projectstdb_projects ON `tdb_projects`.`id` = `tdb_join_documents_locations_projects`.`project_id`
    
LEFT OUTER JOIN `tdb_join_projects_purchasesproject_tdb_join_projects_purchases ON `tdb_projects`.`id` = `project_tdb_join_projects_purchases`.`project_id`
    
LEFT OUTER JOIN `tdb_purchasesproject_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` != 

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

 
Posted: 23 April 2010 07:33 AM   [ # 182 ]   [ Rating: 0 ]
Avatar
Joined: 2007-06-10
39 posts

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:

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

And in movie model I got this:

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

 
Posted: 23 April 2010 08:05 AM   [ # 183 ]   [ Rating: 0 ]
Avatar
Joined: 2008-10-08
1039 posts

@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.

 Signature 

Phil DeJarnett
  OverZealous Creations, LLC

 
Posted: 23 April 2010 08:10 AM   [ # 184 ]   [ Rating: 0 ]
Avatar
Joined: 2007-06-10
39 posts

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 smile

 
Posted: 23 April 2010 08:58 AM   [ # 185 ]   [ Rating: 0 ]
Avatar
Joined: 2007-06-10
39 posts

*edit*
Sorted. sorry smile

 
12 of 62
12