Thread

I’m pretty sure you have an incorrectly defined relationship.  Verify your relationships are correctly configured (see the top of the troubleshooting page).

Otherwise, all of the examples in the guide work.

Hi,

I find DMZ to be a tremendously helpful tool, but I have one concern/feature suggestion.

In my current project, I need to run alot of INSERTS on my database, i.e. I parse text files, process them, filter them and insert data records into the DB.
When I use DMZ to create DataMapper objects and then run save() for every object the performance is quiet sluggish, as expected, since it creates one query for each record, which creates a lot of overhead on the database.

On the other hand, if I manually build an SQL query with multiple data records in one query, I can process my datasets up to 10 - 15x faster.

So, my problem is that DMZ seems to lack a way to process batch inserts in an efficient way. I haven’t dug into the DMZ code alot yet, so I can’t quiet estimate how complicate it would be or if it is possible at all to add such a save_all() function that saves a collection of DataMapper objects in one query. I realize that there are some pitfalls, for example all the objects would need to set either all of the table fields or at least the exact subset of fields, but right now DMZ the lack of batch inserts is almost a deal breaker for my type of application.

I’d love to hear your opinion on this topic and thanks again for developing such a great tool to begin with.

@Conerck

That seems like an easy thing to make into an extension.  I would recommend looping through all objects and checking validate, to ensure that the pre-processing rules are run.  If one or more objects returns FALSE, return that and exit.

Then manually build an insert query.  DMZ uses the CodeIgniter ActiveRecord methods, so you might be able to use those to build the query.

One thing that would be a real problem (and why I wouldn’t include this as a core function) is that there is no way to retrieve the IDs of the newly inserted rows.  This makes it difficult to support, since you cannot modify or reference new items.

@Conerck

That seems like an easy thing to make into an extension.  I would recommend looping through all objects and checking validate, to ensure that the pre-processing rules are run.  If one or more objects returns FALSE, return that and exit.

Then manually build an insert query.  DMZ uses the CodeIgniter ActiveRecord methods, so you might be able to use those to build the query.

One thing that would be a real problem (and why I wouldn’t include this as a core function) is that there is no way to retrieve the IDs of the newly inserted rows.  This makes it difficult to support, since you cannot modify or reference new items.

Thanks for the input. I’ll look into the extension option.

But… I don’t think the IDs are a problem. You could use the MySQL function LAST_INSERT_ID (see: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html and http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id). Since the value is connection specific and you don’t have multi-threading in PHP you can pretty much garuantee that the return value belongs to your last INSERT and can extrapolate the IDs of the other entries from that (unless the DB is setup with an autoincrement offset other than 1, but even that could be coded around if necessary).

I think the problem of making sure that all new objects have the same fields set is much more difficult or at least expensive, since you’d have to compare every object with each other, giving you at least an n² complexity (I think).
If some objects have some fields left blank you can’t use a multiple-row insert.

Edit: After looking into it a bit more it seems that CI’s ActiveRecord doesn’t support bulk inserts either, so I’d have to built the SQL Query from scratch, which isn’t very pretty, even when done in an DMZ extension…

@Conerck

DMZ already uses LAST_INSERT_ID, but I don’t know that any database guarantees that multiple inserts are atomic.  On a high-traffic website, the resulting IDs may not actually be serial.

(Also, even if one or more DB does do atomic inserts, many users use different databases.  I, for example, use PostgreSQL almost exclusively, except when testing DMZ.)

Generating the insert should really be pretty simple 😊  :

$success = TRUE;
foreach($obj->all as $o) {
    $o->validate();
    if(!$o->valid) {
        $success = FALSE;
    }
}
if(!$success) {
    $obj->error_message('query', 'Unable to validate.');
    return FALSE;
}

// alternatively, you could accept the fields_to_set as a parameter.
$fields_to_set = array();
$fields_to_check = $obj->fields;
foreach($obj->all as $o) {
    foreach($fields_to_check as $index => $f) {
        // keep track of which fields have been added
        $remaining_fields = array();
        if( ! empty($o->{$f})) {
             $fields_to_set[] = $f;
        } else {
             $remaining_fields[] = $f;
        }
    }
    $fields_to_check = $remaining_fields;
}

$sql = 'INSERT INTO ';
$table_name = $obj->prefix . $obj->tablename;
$sql .= $obj->db->protect_identifiers($table_name);
$sql .= ' (';
foreach($fields_to_set as $index => $field) {
    if($index > 0) {
        $sql .= ',';
    }
    $sql .= $obj->db->protect_identifiers($field);
}
$sql .= ")\n VALUES";

$first = TRUE;
foreach($obj->all as $o) {
    $sql.= $first ? '' : ",\n       ";
    $first = FALSE;
    $sql .= ' (';
    foreach($fields_to_set as $index => $field) {
        $sql .= $obj->db->escape($o->{$field});
    }
    $sql .= ')';
}

$success = $obj->db->simple_query($sql);

if(!$success) {
    $obj->error_message('query', 'Unable to run the query.');
}

return $success;

Barring any typos, that should work.

Hey,

So im having an issue.

I have a few tables

inventories
parts
datacenters

inventories are a many to one relationship to parts and datacenters

Table:
inventories {
id
part_id
datacenter_id
}

For some reason when I try and save it however it says cannot relate to model (which is another table I have)

Parts are a many to many with models, however I don’t see why this is causing an issue with the save…

...
$inventory->qty = $this->input->post('qty');

$datacenter_id = $this->input->post('datacenter_id');
$part_id = $this->input->post('part_id');

$datacenter = new Datacenter();
$datacenter->get_by_id($datacenter_id);

$part = new Part();
$part->get_by_id($part_id);

if ($inventory->save(array($part,$datacenter)))
...

It gets a valid part and a valid datacenter.

// Inventory Relations
var $has_one = array('datacenter','part');

// datacenter relations
var $has_many = array('server','inventory','swap');

// part relations
var $has_many = array('model','inventory','swap');

any ideas? or need more info?
Cache is off, checked all the relations and they are all proper and spelt right, and saves properly.

Thanks, Phil, for the quick and extensive help. I hacked something together myself, but your code gave me some insights and hints that I would have overlooked on my first draft.
One thing you were missing though, is a check to make sure that all elements of $obj are of the same class 😊 And I’m pretty sure that your ‘fields_to_set’ solution would cause some unexpected behavior in regards to NULL and/or default column values. I opted for the fields_to_set as parameter approach and ignore all additional fields. If you are interested I can email you the code, once I tested it some more.

And I always assumed that multiple-row insert queries would be treated atomically on account that on transactional tables the entire query is rolled back if one insert fails, but I guess I was wrong then.

Thanks again for the fast assistance.

@OverZealous

Hey Phil,

I was just noticing that since get_sql (rightly) doesn’t call clear, query_related doesn’t get reset, which can cause some confusion when using another related query after a call to get_sql. Just a quick note for anyone who may have run into that.

Jim

@tdktank59

Hey I had a similar problem with my e-commerce shop just couple of days ago. I had a table called products and one field was “model”. I was using where_related. I got quite “interesting” sql. I changed the field to model2 and which made things work again. Sorry OverZealous, didnt have the time to do a bug report because of deadline and I dont have the time right now give proper details. But tdktank59, I just wanted to inform you are not alone with the problem 😊

Hello again,

I have a little question about “select_func”.

I need to do somthing like:

FUNCTION_1 (
  ARG_1_1,
  FUNCTION_2 (
    ARG_2_1,
    ARG_2_2,
    ARG_2_3
  ),
  ARG_1_3,
  ARG_1_4
);

on a select statement.

Should the code be like…

$object->select_func(
    'FUNCTION_1', 'ARG_1_1',
    array('FUNCTION_2' => array('ARG_2_1', 'ARG_2_2', 'ARG_2_3'),
    'ARG_1_3', 'ARG_1_4',
    'FUNCTION_1_ALIAS'
);

???

that gives me errors.

Thanks!

PD: Some arguments are going to be column names, or join fileds…

@mrtavo
First, (I’m sure this is a typo in the example only) your example has unbalanced parentheses on the third line.

Ignoring that, what error, specifically, are you getting?  What does the generated query look like?

It should work, but without more information I can’t really help much.  :-(

(Another option, in the short run, is to look at defining a custom SQL function.  That’s especially useful in more complex cases such as this.  Usually done with CREATE FUNCTION on most DB servers.)

Well yes, that was a typing error; i’m sorry about it.

I’m sorry for giving you no examples on generated code too. Here is one.

Don’t worry about field names or join names, they are not relevant on this case.

Just look after the strong text in both querys. I don’t know why it’s doing that…

SELECT `A`.*, `B`.`city` AS B_city, `B`.`country` AS B_country, `B`.`t` AS B_t, FUNCTION_1(`A`.`arrival_date`, FUNCTION_2(`B`.`t`, `‘4’`, `‘0’`, `’:’)`, `’+00:00’)` AS arrival_GMT_0 FROM (`trip_step`) LEFT OUTER JOIN `B` as B ON `B`.`id` = `A`.`B_id`

It should be…

SELECT `A`.*, `B`.`city` AS B_city, `B`.`country` AS B_country, `B`.`t` AS B_t,
FUNCTION_1(`A`.`a_d`, FUNCTION_2(`B`.`t`, ‘4’, ‘0’, ‘:’), ‘+00:00’) AS arrival_GMT_0 FROM (`A`) LEFT OUTER JOIN `B` as B ON `B`.`id` = `A`.`B_id`

an that one works.

It seems to work fine with 1 parameter, after the first one it start to do something with ’ and `.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’ `’+00:00’)` AS arrival_GMT_0 FROM (`A`) LEFT OUTER JOIN `B` as Bi’ at line 1

It’s doing something extrange with function closure.

Thanks again!

This is the definition code with DMZ of that part.

$trip_s->select_func(
        'FUNCTION_1', '@arrival_date',
    array('FUNCTION_2' => array( '@B/t', '4', '0', ':')),
    '+00:00', 
    'arrival_GMT_0'
);

At first I though it looks like it’s CI’s escaping mechanism getting in the way again.

Now I’m wondering if my example is wrong.  Try this instead:

$trip_s->select_func(
    'FUNCTION_1',
    '@arrival_date',
    'FUNCTION_2' => array( '@B/t', '4', '0', ':'),
    '+00:00',
    'arrival_GMT_0'
);

Just to see if that fixes it.

Gives this error on Line “FUNCTION_2” => array(...),

Parse error: syntax error, unexpected T_DOUBLE_ARROW

I think I can’t use that kind of asignation, in a non array.