[Deprecated] DMZ 1.5.4 (DataMapper OverZealous Edition)

[eluser]OES[/eluser]
Is this type of Query possible with DMZ ?

Code:

SELECT
    orders.*,
    COALESCE(overs, 0) AS some_total
FROM orders
LEFT JOIN
    (
    SELECT order_id, SUM(overs) AS overs
     FROM extras
     ) o
    ON  orders.id = o.order_id
WHERE orders.user_id = xx

If so how would you go about it.

Orders have a relationship with extras

[eluser]OverZealous[/eluser]
@OES
DMZ (or ActiveRecord, for that matter) has no special provisions for subqueries.

The best bet is to build the subquery manually and add it using AR methods.

Code:

$order->db->join(...);
$order->where_related($user);
$order->get();

You might be better off adding your column using a select statement, though, since it isn't used in the query elsewhere:

Code:

$sql = '(SELECT SUM(overs) FROM extras WHERE extras.order_id = orders.id) as overs';
$order->select($sql);
$order->where_related('$user);
$order->get();

Also, I believe group_by can be used for this, as just discussed the other day, but I make no promises. I think the group_by method is faster for the database.

Of course, I'm somewhat talking out my *** about performance. ;-)

[eluser]OES[/eluser]
TY Overzealous.

I already had the opinion of using sub-querys but just wondered if there was a another way. I will do some test on TIMES from normal SQL v DMZ.

:-)

[eluser]BrianDHall[/eluser]
Say Mr O-to-the-Z, I'd like your opinion on my technique here.

I wanted to use the htmlform to automatically build forms for me, but I wanted a simple option where I could blacklist certain fields for use in some forms. For instance in a "create new xyx" form I would not want the id, updated, or created fields to show for any model. I also wanted to be lazy and not to have to define what fields I want to show - just define what fields I DO NOT want to show.

So I implemented this as a custom function in my model:

Code:

var $forbidden_form_fields = array('id', 'created', 'updated');

function public_form_fields()
{
    $public_fields = $this->fields;

    foreach ($public_fields as $field_key => $field_value)
    {
        if (in_array($field_value, $this->forbidden_form_fields))
        {
            unset($public_fields[$field_key]);
        }
    }

    return $public_fields;
}

Now in my view I generate my form thusly:

Code:

<?= $model->render_form($model->public_form_fields()); ?>

Aaaaand...done! Only non-blacklisted fields will be shown, so if you add a new field like "secondary contact phone number" it will automagically show up on existing forms unless you choose to have it blacklisted.

If you want to dynamically add blacklisted fields you can do that, too, such as:

Code:

// Hide email and phone number
$model->forbidden_form_fields[] = array('email', 'phone');

// Whitelist 'id' for editing, but keep other fields hidden
if ($model->forbidden_form_fields['id'])
{
unset($model->forbidden_form_fields['id']);
}

Am I missing a built-in function or anything?

It works really great, I'm manually putting them in a few models for now and if goes well I'll probably rip out and put into an extension instead.

[eluser]OverZealous[/eluser]
@BrianDHall
That's a great solution.

If I had designed it, I probably would have built-up (meaning, only add "approved" fields), but the nice thing about using unset is you have a much cleaner setup. I think I like your solution better.

Whether that code should be in the model (which, IMO, is easier to maintain) or the controller is up to the MVC die-hards to debate ;-)

Thanks for sharing!

[eluser]freshface[/eluser]
I need to perform a query like this:

Code:

$this->db->distinct();
        $this->db->select('date');
        $this->db->where('event_id','1');
        $this->db->order_by('date','asc');
        $distinct = $this->db->get('event_items');

How do i perform something like this?

Never mind, used the search

[eluser]freshface[/eluser]
Can i add hooks to actions?
I have this piece of code:

Code:

$i = new Item();
$i->name = "item name";
$s->save();

But when i perform a save(); I want to create a hash from the name and also insert it.
But can I do something like that in my model?

[eluser]OverZealous[/eluser]
@freshface
Check out Custom Validation rules. They are automatically executed just before a save.

For an example of almost exactly what you requested, see the include example code for hashing passwords.

[eluser]cube1893[/eluser]
Hi guys,

I've designed a database model and I'm racking my mind about how to code an easy way of accessing the data with DMZ 1.5.4.


There is one main table, called module and several tables which have the prefix plugin. Each module can have several plugin types and each Plugin can have different settings. For instance, if there is a plugin of type 'Text', one instance can have the label 'Preview' and another on can have the label 'Content'. each Plugin has it's own position in the module.

Example:

Code:

Module A
----------
Plugin Text ('Preview'), Position: 1
Plugin Date ('Current Date'), Position: 2
Plugin Text ('Content'), Position: 3

Each Module has many Entries. The allowed plugins for the entry are already defined, that means each entry is related to different plugincontent tables, one for each plugin.

Example:

Code:

Module A
--------
Entry 1:
Plugin Content Text: 'Lorem ipsum dolor'
Plugin Content Date: 2009-10-25
Plugin Content Text 'Lorem ipsum dolor'

Now, I want to select all Entries and the related contents of the active plugins, sorted by the position.

The Problem is, that each plugin hast different types of settings and different types of values, for instance dates, textfield option fields etc.

How can I realize this with DMZ and is there a name for these relationships where not only a foreign key is the important column for the relation, for example the pluginid, but also the table name?!

Any help would be appreciated!
Daniel

[eluser]BrianDHall[/eluser]
Shew! Me and my bright ideas to save time!

Well, I finally got it working, so here is what I was thinking.

I was thinking the HTMLForm class is so nice to display forms, why can't it use its super-powers to display results? So if you have a form that allows people to submit special event information, why can't it be minorly modified to display that information back in an similarly magical fashion?

Put simply, why not have a $object->render_view_form() sort of ability? So I made it, sort of

It took a few hours of poking, but I think I found the path of least resistance.

First, a little bug for you OverZealous:

Code:

$org->render_form(array('fields'), '', array(), 'view_form/form', 'view_form/row');

That works just fine. But this breaks:

Code:

$org->render_form($org->public_form_fields('span'), '', '', 'view_form/form', 'view_form/row');

The third field must be an array, even an empty one, or it breaks.

Anyhoo, here is how it works. So I generate my form creation code in my view like this:

Code:

<?= $org->render_form($org->public_form_fields(), '', array('save_button' => 'Register Organization', 'reset_button' => 'Clear')); ?>

Ah, one-line form creation, love it! OK, so now lets see what organizations we have now. In my controller I do this:

Code:

$org = new Organization();
        $org->get();

        foreach ($org->all as $obj)
        {
            $data['org'] = $org;
            $data['content'] .= $this->load->view('view_charity_partial', $data, true);
        }

Now in my partial view I simply do this:

Code:

<?= $org->render_form($org->public_form_fields('span'), '', array(), 'view_form/form', 'view_form/row'); ?>

One-line form display, w00t!

To make this work, first you set a 'type' for all your fields by creating an array of the form:

Code:

$fields = array('id', 'name' => 'span', 'email' => 'span');

Now you need to create a helper function in HTMLForm to allow it process the 'span' type. I copied the function _input_textarea to make it work like I wanted. I also wanted to skip blank fields entirely rather than display a blank field, so I modified my code to work like this:

Code:

function _input_span($object, $id, $value, $options)
    {
        if (!$value)
        {
            return '';
        }

        if(isset($options['value']))
        {
            $value = $options['value'];
            unset($options['value']);
        }
        $a = $options + array(
            'name' => $id,
            'id' => $id
        );
        return $this->_render_node('span', $a, htmlspecialchars($value));
    }

Now this works just fine, and you can actually stop here - the only problem is you end up with submit buttons at the bottom of each entry because there is no option to turn the buttons off. So I just modified the form.php template to get rid of form and button stuff.

And that's all! Now you can not only create an html form with one line of code, but you can then display it right back with only one line of code!