Today I came accross an interesting ‘feature’ in Zend DB Queries.

I was doing a join onto a table to fetch a row, and then trying to delete it. Only every time I tried to delete, it was throwing a “Cannot refresh row as parent is missing. Zend Framework” error. After some debugging I realised that the data in the fields in the row that was retuning from the select did not match the fields in the table that I was selecting from.

Now the interesting, and somewhat illogical result, is what Zend DB does when it tries to join onto another table that has one or more identical field names.

If you specify what fields you require from the join, and give them specific field names, then everything runs smoothly. If you do not however, then Zend DB will actually replace the contents of the original field name’s values with the values of the table you are joining onto.

For example

user
{id, active, username, email_address}

client
{id, active, user_id, client_id, client_code}
and I have a row in each table
user_row
(
	id => 2, 
	active => 1,
	username => jSmith,
	email_address => someaddress
)

client_row
(
	id => 14, 
	active => 1,
	username => jSmith,
	user_id => 2
	client_code => 1223112
)

If I do a simple query to get a user only if it is a client (as opposed to getting the user attached to the client)

$select = $this->select()
	->from(array('u'=>'user'))
	->join(array('c'=>'client'), 'c.user_id = u.id')
	->where('u.id = ?', 2)
	->setIntegrityCheck(false);

If you run this query through mysql, you get

row (
	id => 2 ,
	active => 1,
	username => jSmith,
	email_address => someaddress,
	id1 => 14,
	active1 => 1,
	user_id => 2,
	client_code =>1223112,
)

However, if you run this query through Zend DB, what you get is

row (
	id => 14 ,
	active => 1,
	username => jSmith,
	email_address => someaddress,
	user_id => 2,
	client_code =>1223112,
)

The big difference being that it has actually overwritten the id with the id from the table being joined. Now assuming that I do not have a user row with an id of 14, I cannot delete the row that I have just returned (or worse yet, if I do have a user row with id 14 and I try and delete the row I have just returned, it will delete the wrong row.

To fix the code above to work correctly, depending on what data you actually need back, you could use either of the next 2 lines of code instead of the line of code above that does the join.

->join(array('c'=>'client'), 'c.user_id = u.id', array())
This will return only the data from the user_table.

->join(array('c'=>'client'), 'c.user_id = u.id', array('second_id' => 'c.id'))
This will return the row from the user table with the field 'id' from the client table appended as 'second_id'.

The lesson from this, always specify the fields and fieldnames every time you use a join.


We'd love to
hear from you

We've prepared a simple project planner to get started.

Lets get started

Or send us a
message

Phone: (07) 3878 1151

Address: Office 2, 59 Hardgrave Rd,
West End, Brisbane, 4101, QLD, Australia

  • This field is for validation purposes and should be left unchanged.
css.php