Thread

Can someone help me figure out how to sort results by a related count?

First, before I post anything, don’t load models!  They are already loaded automatically.  It might not say this in the manual (although I thought it did, but I couldn’t find it).

Also, if you’ve already called get(), don’t call count(), because that runs another query.  Instead use:

$my_count = count($t->report->all);

I haven’t tested it, but try this using include_related_count (you’ll need v1.6):

$s = new Statute();
$s->include_related_count('report')->order_by('report_count', 'DESC');
$s->get();
// to limit to the top 5, do this instead:
// $s->get(5);

foreach($s->all as $t){
    $t->report->get();
    $t->description;
    // because we used include_related_count, we can do this:
    $report_count = $t->report_count;
    // alternatively, if we hadn't used include_related_count:
    // $report_count = count($t->report);
}

Got a weird bug here, hopefully somebody will be able to shed some light on it.

I’m working on an XML extension plugin for the DMZ library. Calling to $obj->from_xml($xml_string) will convert an xml string into dm objects.

function from_xml($object, $xml_str, $fields = '', $callback = NULL)
{
        // get object class name
        $class = get_class($object);
        
        // have fields been defined to limit import?
        if(empty($fields))
        {
            // no, use all fields
            $fields = $object->fields;
        }
        
        // check for callback
        if(empty($callback))
        {
            $result = array();            
        }
        else
        {
            $result = 0;
        }
        
        // parse xml string into xml object
        $xml = new SimpleXMLElement($xml_str);
        
        // loop through all records in the xml document
        foreach($xml->{$object->table}->children() as $record)
        {
            // create the object to save
            $o = new $class();
            
            // loop through all defined fields
            foreach($fields as $field)
            {
                // check field element is present
                if (isset($record->{$field}))
                {
                    $o->{$field} = $record->{$field};
                    //log_message('error', 'Record = '.$record->{$field});
                }
            }
            
            if(empty($callback))
            {
                $result[] = $o;
            }
            else
            {
                $test = call_user_func($callback, $o);
                
                if($test === 'stop')
                {
                    break;
                }
                if($test !== FALSE)
                {
                    $result++;
                }
            }
        }
            
        return $result;        
}

This seems to be working fine, i’ve used a similar technique to the included csv and json extensions. The method returns an array of new dm objects with each field set.

In my controller code when I loop through this array and attempt to save each object I get a CI database error!

INSERT INTO `people` (`first_name`, `last_name`, `email`, `created`, `updated`) VALUES (Terry, Test, .(JavaScript must be enabled to view this email address), '2009-12-15 11:26:12 +0000', '2009-12-15 11:26:12 +0000')

Obviously the error here is that the constructed sql is not escaping the name and email string values… Why???

@The Hamburgler

I’m guessing the values you are assigning from the XML object aren’t actually strings.  You might need to force coersion for this line:

$o->{$field} = $record->{$field};

becomes:

$o->{$field} = (string)$record->{$field};

You can verify this by using var_dump on the result.

If that isn’t the case, then you have a problem in your controller or model.

arrrgh damit!
Yup you’re right, The SimpleXMLElement does not return a string.

Casting the value to a string:

(string)$record->{$field};

Resolved the issue, thanks!

What would be an optimal way to get objects that are missing an association?

Example: I have a question object. Each question could have many responses (associated to many response objects). I want to get all of the questions that have no responses.

This is probably a task for subqueries, but is there a faster, more optimal way of doing this?

What would be an optimal way to get objects that are missing an association?

This is probably a task for subqueries, but is there a faster, more optimal way of doing this?

Try this:

$q = new Question();
$q->where_related('response', 'id', NULL);
$q->get();

If that doesn’t work, here’s an example with subqueries:

$r = new Response();
$r->select('COUNT', '*', 'count');
$r->where_related('question', 'id', '${parent}.id');

$q = new Question();
$q->where_subquery($r, 0);
$q->get();

It’s a bit manual, but it should work.  I might look into making a special case for ${query}_related_count that is based on include_related_count.

Try this:

$q = new Question();
$q->where_related('response', 'id', NULL);
$q->get();

Amazingly, this works on join tables. Thanks a lot.

I might look into making a special case for ${query}_related_count that is based on include_related_count.

This sounds like a winner.

First, Phil, thanks a lot for DMZ.  I’m glad I found it, because I liked DM, but I was getting frustrated with its limitations, and you’ve basically done most of what I was starting to plan out in my mind.  Best of all, you’ve implemented it all rather well.

That said, I do have some bug fixes to contribute.  Since they’re relatively small, I think they should fit in the post just fine, but if it’s not clear, I’d be happy to send a patch or whatever works for you.  Maybe you’ve already caught these, but here we go:

(Line 1400)
    function _delete($related_field, $arguments)
    {
        $this->delete($arguments[0], $related_field);
    }

This should have a return so we pass through the success/failure when deleting relations.

Next, I believe the array handling in _save_itfk should really be:

(Line 1112)
        if(is_array($o))
        {
            $this->_save_itfk($o, $rf);
            if (empty($o))
            {
                unset($objects[$index]);
            }
            else
            {
                $objects[$index] = $o;
            }
        }

so that any unsets that take place recursively are carried through.

And finally, another ITFK problem that I’m not positive about.  I might be wrong about this, but instantiating a related object when relating via ITFK seems to leave out the related object’s ID because of the field collision detection (as the related record’s ID field has the same alias as the ITFK field).  The changed code with the instantiation check moved up:

(Line 3726)
        foreach ($fields as $field)
        {
            $new_field = $append . $field;

            if($instantiate) {
                $property_map[$new_field] = $field;
            }

            // prevent collisions
            if(in_array($new_field, $this->fields)) {
                continue;
            }
            if (!empty($selection))
            {
                $selection .= ', ';
            }
            $selection .= $table.'.'.$field.' AS '.$new_field;
        }

That way we include the related object’s ID into the instantiation field map, but we still don’t select the matching field in the query.  Like I said, this one I’m not positive about.  Maybe I’m not noticing something, but it seems to me that this is the way it should work.

Additionally, I hope I don’t sound too critical, but in using DMZ on a site where I sometimes to have instantiate a couple hundred objects for a page (online store with product category galleries with images via include_related), I wasn’t completely thrilled with the performance (which is a downside of DM in general).  So, I spent a few hours with a profiler to see if I could get the low-hanging fruit, and if you’re interested in the results of that, I’d be happy to share with you my findings and code changes.  There are some significant performance gains that can be had with fairly minor code changes.  But those changes would probably be better communicated outside of a forum post.

Thanks again for all your work on DMZ.

Jim

And, not to take over the thread, but someone might be interested in this.  Like I said above, DM could be more efficient when instantiating more than a few objects.  Besides general performance enhancement, I wanted to optimize the common case of looping over objects, maybe accessing some class methods on them or whatever, but not caring about the objects outside of the loop.  That is, storing a hundred objects in the all array (and going through full instantiation on each)—although it makes for a very simple API—is often a bit of a waste of both memory and processing, so I came up with the following.  Unfortunately, it won’t work in an extension, as _process_query has to be overloaded:

class Extended_DataMapper extends DataMapper
{
    protected $capture_query = FALSE;
    protected $query = NULL;

    // Same as get, except that in many cases, we're looping through results and don't need to store an object
    //   beyond that, so this will return an iterator that can be used to loop without storing objects
    function get_streaming($limit = NULL, $offset = NULL)
    {
        $this->capture_query = TRUE;
        $this->get($limit, $offset);
        $this->capture_query = FALSE;

        $iterator = new DM_Iterator($this, $this->query);
        $this->query = NULL;

        return $iterator;
    }

    function _process_query($query)
    {
        if ($this->capture_query)
        {
            $this->query = $query;
            return;
        }

        parent::_process_query($query);
    }
}

// Used with get_streaming
class DM_Iterator implements Iterator, Countable
{
    protected $object;
    protected $result;
    protected $count;
    protected $pos;

    function __construct($object, $query)
    {
        $this->object = $object->get_clone();
        $this->object->clear();

        $this->result = $query->result();
        $this->count = count($this->result);
        $this->pos = 0;
    }

    function current()
    {
        $this->object->_to_object($this->object, $this->result[$this->pos]);
        return $this->object;
    }

    function key()
    {
        return $this->result[$this->pos]->id;
    }

    function next()
    {
        $this->pos++;
    }

    function rewind()
    {
        $this->pos = 0;
    }

    function valid()
    {
        return ($this->pos < $this->count);
    }

    function count()
    {
        return $this->count;
    }
}

which could be used as:

class User extends Extended_DataMapper
{
...
}

$u = new User();
$iterator = $u->where(...)->order_by(...)->get_streaming();
echo count($iterator), ' user record(s):<br>';

foreach ($iterator as $user)
{
    echo $user->name, '<br>';
}

So, the iterator that get_streaming returns can be mostly used in place of the “all” array, with the obvious exception that you can’t access a particular element using array bracket notation.  But foreach and count work as expected, and when it fits what you’re doing, it’s a large performance gain.  Of course, since the iterator changes the values of a single object, you wouldn’t want to store the returned objects and access them outside the loop without doing some cloning (and I don’t think you’d want to do that either, as you’d have basically reinvented the standard get).

I haven’t tested all functionality on the resulting object, so it’s possible that some changes would have to be made to support everything DMZ does, but I believe since I just capture the get and use _to_object to copy a new row to the object, any necessary changes should be simple.  In my mind though, I associate get_streaming with read-only loops just in case.

I hope someone finds that useful for dealing with a lot of objects.

Jim

@TheJim

I’m looking over your suggestions.  The first is a simple mistake, thanks for catching it.

I don’t agree that your second suggestion is necessary:
1) In the first case (an empty array), there is no harm, because at most you might get an extra function call.  Since this is saving (not getting), there isn’t any real performance boost.

2) In the second case (not empty), the array should always be edited by reference in PHP 5+, which is all that is supported.  Re-assigning it isn’t really necessary.

3) Finally, the saving isn’t really intended to truly be recursive.  It’s just better than the old method (manually supporting N-levels deep).

I think you are correct in your third suggestion.  However, using the solution you provided will not work, as all fields will be copied over, even if there is a collision.  I’ll work up a solution.

——————————

As for your speed improvement suggestion, I think that has some real possibility.  However, I don’t think you really need to override the built-in DMZ method.  Instead, take advantage of the get_sql method introduced in DMZ 1.6.

Your code (as an extension) would look like this:

function get_streaming($object, $limit = NULL, $offset = NULL)
{
    $sql = $object->get_sql($limit, $offset);
    $query = $object->db->query($sql);
    $iterator = new DM_Iterator($object, $query);
    return $iterator;
}

It has one limitation (which you can manually copy from the get method, or just document): traditional related queries won’t work.  This means that queries like this:

$user->bug->get_streaming();

Would have to be rewritten as:

$bug = new Bug();
$bug->where_related($user)->get_streaming();

If you want to duplicate the existing functionality, look at the first if block of the get method.

I don’t agree that your second suggestion is necessary:
1) In the first case (an empty array), there is no harm, because at most you might get an extra function call.  Since this is saving (not getting), there isn’t any real performance boost.

2) In the second case (not empty), the array should always be edited by reference in PHP 5+, which is all that is supported.  Re-assigning it isn’t really necessary.

I agree with #2.  Actually, I only added that in when I posted in the forum.  For some reason, I felt like I was missing a case when I wrote the post, but yes, references should take care of it.

However, I still think the empty case is necessary, not for performance reasons, but because if the related object is saved with ITFK and passed in an array, but not unset, then when we get to

(Line 1096)
        // Check if a relationship is being saved
        if ( ! empty($object))
        {
            // save recursively
            $this->_save_related_recursive($object, $related_field);

            $trans_complete_label[] = 'relationships';
        }

$object isn’t empty, so _save_related_recursive raises an error.  I don’t remember exactly where it happened, but this is what I came across when integrating DMZ into my work-flow, so I could back-track and give a more detailed example if necessary.

I think you are correct in your third suggestion.  However, using the solution you provided will not work, as all fields will be copied over, even if there is a collision.  I’ll work up a solution.

I think in my mind I wasn’t considering those situations because it would be kind of a bad field naming scheme.  But it could definitely happen, so you’re right; you would want to keep what you had before with a special case only for the ITFK.

I’ll check into get_sql; I was unaware of its existence.

Thanks.

Jim

Can someone help me figure out how to sort results by a related count?

First, before I post anything, don’t load models!  They are already loaded automatically.  It might not say this in the manual (although I thought it did, but I couldn’t find it).

This worked perfectly, thanks!

And thanks for the other tips as well, I thought sure I had read in the docu (For 1.5.4) that you needed to call models, but I’m no longer doing so as per your info.

Thanks again.

@TheJim
Thanks for the more in-depth info on the ITFK bug.  I’ll add in the object unsetting.  Always hate those one-off errors!

get_sql is brand new to 1.6.  It’s part of the system that allows for subqueries.  That is, however, why get_sql ignores the traditional relationship structure.

So, I keep getting this:
Fatal error: Class ‘vote’ not found in /mydirectoryhere/application/libraries/datamapper.php on line 1288

The thing is, I’m not using a vote table anymore. There’s no vote model, there’s no vote table, and there’s no production cache. Why is it even trying to look for the class?

Nevermind I figured it out. It was still referenced in one of my relationships. Don’t know how I missed it.

I have a problem with DMZ.

My problem is that it’s just too awesome for me to calm down about it. Seriously, keep up the good work 😊
It’s a shame I had to go to all the effort of learning Ruby on Rails before I figured out the benefits of using a Database Object Mapper system.

I’ve mocked up some quick sketches that clearly shows the benefits of using DMZ

=== Development Time ===

Before DMZ - ========================>
Using DMZ - ==========>

=== Code Reuse ===

Before DMZ - ==========>
Using DMZ - ========================>

=== The amount of frustration when people ask me to make changes to the application late in the development process

Before DMZ - ========================>
Using DMZ - =====================>

=== Client stupidity ===

Before DMZ - ========================>
Using DMZ - =========================>

I think the last figure can be attributed to the new generation of clients that ask more (I didn’t think it was possible) stupid and pointless questions than before.