[eluser]Stolz[/eluser]
I'm using DM for the first time and I'm surprised how easy it makes CRUD operations when using together with array and htmlforms extensions. So far both extensions worked well for all my models but today I'm facing a weird problem for one model.
When I try to save an object I get this SQL error:
Code: A Database Error Occurred
Error Number: 1054
Unknown Column 'orderitems.orderitem_id' in where clause
SELECT COUNT(*) AS `numrows` FROM (`orderitems`) WHERE `orderitems`.`order_id` = 2 AND `orderitems`.`orderitem_id` NOT IN (8, 9)
Filename: libraries/datamapper.php
Line Number: 2526
I think the correct SQL should be ...AND `orderitems`.`id` NOT IN (...) becasue the Orderitem model is referencing itself and not another related model. This error is triggered inside the count() method which is triggered by the save() method which is triggered by the from_array() method of the array DM extension.
Here is my controller code
Code: //Get order
$order = new Order(2);
//Fields to render in the form
$fields = array('orderitem');
//Save changes from $_POST
if($post = $this->input->post())
{
$order->trans_begin();
if ( ! $order->from_array($post, $fields, TRUE) OR $order->trans_status() === FALSE)
$order->trans_rollback();
else
$order->trans_commit();
}
//Load view
$data = array(
'order' => $order,
'fields'=> $fields,
);
$this->load->view('order/edit', $data);
Here is my view code
Code: echo $order->render_form($fields);
And here are the implied models
Code: class Order extends DataMapper {
public $has_one = array('orderstatus', 'place', 'user', 'paymenttype');
public $has_many = array('orderitem');
...
/*SQL:
CREATE TABLE IF NOT EXISTS orders (
id mediumint(4) unsigned NOT NULL auto_increment PRIMARY KEY,
user_id mediumint(1) unsigned, FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT,
orderstatus_id tinyint(1) unsigned, FOREIGN KEY (orderstatus_id) REFERENCES orderstatuses(id) ON UPDATE CASCADE ON DELETE RESTRICT,
place_id tinyint(1) unsigned, FOREIGN KEY (place_id) REFERENCES places(id) ON UPDATE CASCADE ON DELETE SET NULL,
paymenttype_id tinyint(1) unsigned, FOREIGN KEY (paymenttype_id) REFERENCES paymenttypes(id) ON UPDATE CASCADE ON DELETE SET NULL,
total float NOT NULL DEFAULT 0
) ENGINE = InnoDB;
*/
Code: class Orderitem extends DataMapper {
public $has_one = array('order', 'orderitemstatus');
public $has_many = array('orderitemextra');
...
/*SQL:
CREATE TABLE IF NOT EXISTS orderitems (
id int(1) unsigned NOT NULL auto_increment PRIMARY KEY,
order_id mediumint(1) unsigned, FOREIGN KEY (order_id) REFERENCES orders(id) ON UPDATE CASCADE ON DELETE CASCADE,
orderitemstatus_id tinyint(1) unsigned, FOREIGN KEY (orderitemstatus_id) REFERENCES orderitemstatuses(id) ON UPDATE CASCADE ON DELETE RESTRICT,
name varchar(128) NOT NULL,
price float NOT NULL,
) ENGINE = InnoDB;
*/
As I said I had no problems with other models so I don't know if it's a DM problem or a problem with my code or any of my models definitions.
I'll appreciate any kind of help.
Thanks!
[eluser]Maglok[/eluser]
Hey all I have a problem.
I have a datamapper model person:
Code: 'organisations' => array(
'class' => 'organisation',
'other_field' => 'people',
'join_self_as' => 'person',
'join_other_as' => 'organisation',
'join_table' => 'crmj_person_organisation'),
I have a datamapper model organisation:
Code: 'people' => array(
'class' => 'person',
'other_field' => 'organisations',
'join_self_as' => 'organisation',
'join_other_as' => 'person',
'join_table' => 'crmj_person_organisation'),
I can easily relate the two with the join table. The join table has a column 'role' though and this is where it gets funky. Due to the fact my relationships are called 'organisations' and 'people' I can't seem to save the join field like so:
Code: $person = new Person();
$person->get_by_id(2);
$orga = new Organisation();
$orga->get_by_id(3);
$person->save_organisations($orga);
//This is what doesn't work
$person->set_join_field($orga, 'role', 'Tester');
The darnest thing is, it will save if I name my relationships like the class name, but that is why I am including 'other_field' and 'class' advanced relationship parameters is it not?
The error I get as to why it doesn't work is this:
Code: Unknown column '_id' in 'where clause'
UPDATE `crmj_person_organisation` SET `role` = 'Test' WHERE `_id` = 2 AND `_id` = 3
Why would it use '_id'? Why is this only happening on advanced relationships with a 'custom' name? This oughta be possible right?
[eluser]WanWizard[/eluser]
@jordanarseno,
You can define the "auto_populate" property on the relation to control it that way.
And you can do it at runtime by just modifying the $has_one / $has_many arrays directly (there is no method to do that).
[eluser]WanWizard[/eluser]
@Stolz,
Looked at it, but I can't see a reason why save() would call count(), and where it would get the "NOT IN (8.9)" bit from. That doesn't make sense to me...
[eluser]WanWizard[/eluser]
@Maglok,
Looking at the code, it uses $this_model.'_id', so you'll get that if $this_model is empty or false. It retrieves that value from the related properties 'join_self_as' field. The second one is similar, $other_model.'_id', which is retrieved from 'join_other_as'.
So it looks like retrieval of the correct relation properties fails, and you don't get an PHP notice that you're accessing non-existent array values for some reason.
Properties are looked up using a _get_related_properties call, which returns NULL if the properties can't be located. But this NULL value isn't caught in set_join_field.
You might have to do some debugging as to why the relation can not be found.
[eluser]Stolz[/eluser]
[quote author="WanWizard" date="1358444868"]@Stolz,
Looked at it, but I can't see a reason why save() would call count(), and where it would get the "NOT IN (8.9)" bit from. That doesn't make sense to me...[/quote]
Thanks for the interest WanWizard. Here is the backtrace print (I removed file extension to avoid forum anti-spam system):
Code: #0 DataMapper->count() called at {system/libraries/datamapper:5348}
#1 DataMapper->_count_related() called at {system/libraries/datamapper:5588}
#2 DataMapper->_related_required() called at {system/libraries/datamapper:2254}
#3 DataMapper->validate() called at {system/libraries/datamapper:1568}
#4 DataMapper->save() called at {application/third_party/datamapper/extensions/array:209}
#5 DMZ_Array->from_array()
#6 call_user_func_array() called at {system/libraries/datamapper:1200}
#7 DataMapper->__call()
#8 Order->from_array() called at {application/controllers/orders:130}
#9 Orders->edit()
#10 call_user_func_array() called at {system/core/CodeIgniter:359}
#11 require_once(system/core/CodeIgniter) called at {application/htdocs/index:208}
It seems save() calls count() because of some of my validation rules. The ""NOT IN (8,9)" bit comes from the array extensions. I'm trying to save an Order which already has two Orderitems with the ids 8 and 9. I generate the form with the htmlform extension which renders 2 checked checkboxes for the orderitem relationship. Those checkboxes have values 8 and 9, so that is the reason they are in the query.
Here is my full model:
Code: class Order extends DataMapper {
public $has_one = array('orderstatus', 'place', 'user', 'paymenttype');
public $has_many = array('orderitem');
public $validation = array(); //Gettext only available on run-time so I use __construct() to set the validation labels
function __construct($id = NULL)
{
parent::__construct($id);
$this->validation = array(
/*...
Non-relationships validation skipped*/
...*/
//Relationships validation
'orderstatus' => array(
'label' => _('Status'),
'rules' => array('required')
),
'place' => array(
'label' => _('Place'),
'rules' => array('required')
),
'user' => array(
'label' => _('User'),
'rules' => array('required')
),
'orderitem' => array(
'label' => _('Items'),
'rules' => array('required')
),
);
}
...
I tracked it down to the last validation rule. If I remove it I have no problem (but the htmlform extension doesn't render checkboxes for the orderitem and my form becomes pointless)
[eluser]Maglok[/eluser]
@Stolz: Sorry no clue!
As for my problem I have done some tracing and debugging and found the following. I think I know where the problem is, I don't have a (quick) fix for it (yet). I think it would involve changing how set_join_field's parameters work.
In datamapper the set_join_field library's first parameter is $related_field. This is (in my case) an object, specifically the object you have a relation to. So far so good. In set_join_field it then determines the name of the related_field by saying $object->model. This in itself is not bad.
The problem comes in when the related properties are determined like this:
Code: $related_properties = $this->_get_related_properties($related_field);
I checked _get_related_properties and it basically does this:
Code: if (isset($this->has_many[$related_field]))
{
return $this->has_many[$related_field];
}
So it tries to use the object modelname as the has_many key. This is incorrect since I have named my key something else using advanced relationships.
If I think about it the set_join_field function might need a reference to the relation it is trying to get the name from? I'll continue thinking on it, but to me it seems the set_join_field function just doesn't 'support' has_many keys that are not the same as the modelname.
[eluser]WanWizard[/eluser]
@Stolz,
What does the Orderitem model look like?
The count query generated is created on on instance of this model, and therefore uses that models relation definitions. So my assumption at the moment is that there is something wrong with the relationship definition from orderitem to order, causing the incorrect SQL.
[eluser]WanWizard[/eluser]
@Maglok,
Some changes have been made to the join_field methods, can you pull the latest version from bitbucket and see if that fixes this issue?
[eluser]Maglok[/eluser]
I grabbed the newest changes and did a few tests. It seems I am now able to set the join field without any problem on advanced relationships with a key that is not the same as the class name being related to.
Bottom line: Success! Thank you.  *cheer*
|
