Thread

@rideearthtom

I’m not sure of the exact source of your first error (though I suspect it’s #1 below), but there are three things to get out of the way before digging too deeply:

1. You don’t seem to be doing anything that actually requires sub-queries.  Just use where_related, or_where_related, not_where_in, etc. for what you’re doing. 

2. In your code:

$documents->where_related_subquery('product', 'id', $products_purchased)
            ->or_where_related_subquery('join_documents_locations_project', 'project_id', $join_projects_purchased);

you’re using $products_purchased and $join_projects_purchased in a scalar context.  When you did:

$accepted_documents->where_related('sme_profile', 'id', $this->sme_profile->id)
            ->get();

you correctly used the id field from sme_profile, but in the first block, you’re just using the objects themselves, which won’t work.  You did create an array of IDs for the $accepted_documents part of your code, so you seem to understand this in at least certain circumstances, but just realize that you can’t just pass DMZ objects directly to the where functions.

3. Why query IDs and then pass them to another query when there are relationships to use directly?  Why not just:

$documents = new Document();
        $documents->group_start() // Groups because of the "OR"
            ->where_related('product', 'id', $products_purchased) // Fix this and don't pass the object -- also notice not a sub-query
            ->or_where_related('join_documents_locations_project', 'project_id', $join_projects_purchased) // Fix this and don't pass the object -- also notice not a sub-query
            ->group_end()
            ->where_not_related('sme_profile', 'id', $this->sme_profile->id) // This should do the same thing as your $accepted_documents -> array of IDS -> call to where_not_in

That should take care of your $accepted_documents code.  Looks like you could also combine the $products_purchased as a deep relationship query.  Possibly the $join_projects_purchased part as well, although that looks like it might be better separated.

Regardless, there’s a lot of room for simplification in your code.

O ！ It’s usefull！！

@rideearthtom

I’m not sure of the exact source of your first error (though I suspect it’s #1 below), but there are three things to get out of the way before digging too deeply:

@TheJim

Thanks for your reply:

1. This was the first time I had found a need for subqueries. In particular, I needed an extra where() in the join_documents_locations_project query, which I could not find a way to replicate with joins (at least using DMZ - with raw SQL it would be pretty easy). Maybe there is functionality I’m unaware of.

2. This code does in fact work. DMZ’s user guide contains examples in the same format - selecting a single column, then using it as a subquery. So there are no problems with that code.

3. I’d forgotten the grouping functions - thanks for pointing this out. where_not_related() does not exist! But I think you mean where_related(‘sme_profile’, ‘id !=’, ...), which does work. We got there between us 😉

private function has_documents_to_accept()
    {
//        $accepted_documents = new Document();
//        $accepted_documents->where_related('sme_profile', 'id', $this->sme_profile->id)
//            ->get();
//        $ids_1 = array();
//        foreach ($accepted_documents as $item)
//        {
//            $ids_1[] = $item->id;
//        }
//        $products_purchased = new Product();
//        $products_purchased->select('id')
//            ->where_related('purchase', 'sme_profile_id', $this->sme_profile->id);
        $join_projects_purchased = new Join_documents_locations_project();
        $join_projects_purchased->select('id')
            ->where_related('project/purchase', 'sme_profile_id', $this->sme_profile->id)
            ->where('location_id', $this->sme_profile->location_id);
        $documents = new Document();
        $documents->group_start()
//            ->where_related_subquery('product', 'id', $products_purchased)
            ->where_related('product/purchase', 'sme_profile_id', $this->sme_profile->id)
            ->or_where_related_subquery('join_documents_locations_project', 'project_id', $join_projects_purchased)
            ->group_end()
            ->where_related('sme_profile', 'id !=', $this->sme_profile->id);
//        if (count($ids_1) > 0)
//        {
//            $documents->where_not_in('id', $ids_1);
//        }
        return $documents->count() > 0 ? TRUE : FALSE;
    }

As far as I can see, the original problem I posted about where_not_in_related_subquery() not working properly still stands.

Well, that’s what I get for writing a rushed reply.

Well, that’s what I get for writing a rushed reply.

Don’t get the impression I’m not grateful - thanks to your (rushed?) reply I cut 4 queries to 2 and removed half the lines of code from the function! Just wanted to clarify the points above and include corrections for the benefit of other people who may find it useful.

😊

Why not run a routine that will simply assign “NULL” (on-demand) for a particular {USER} without using a text field.

$someObject = new ............

You may also need to remove validation (“required”) from the last name field, or turn-off validation for this particular routine assignment…

Thank you for your reply. Still working on a solution that will work on any form and not just “users” as in this case 😊

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

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 ?

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

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

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

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.

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

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

@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!)

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

Yes, it works.

Thanks a lot!!