cakephp counterCache

[244863]

Hi,

 

I have a belongsTo relationship with a counterCache set correctly. The problem being that this relationship is not always there.

 

For example Users don't always have a link to an address so the DB allows nulls.

 

But the counterCache is always trying to update, even when the foreign key is NULL.

 

E.g.

 

UPDATE [addresses] SET [user_count] = 0 WHERE [id] IS N''

 

Is there something I can put into the counterScope to stop this error from happening on NULL id's??

 

Thanks in advance.

 

[Jessica]

Probably.

 

[244863]

Any ideas on what? I have been trying everything I can think of...

[244863]

I have just looked into it more and I only seem to get the error when deleting. It does not come up when adding / editing??

[Jessica]

How should we be able to help? You've posted no code.

 

You also said it was an UPDATE before, now it's a DELETE? If you don't even know what your code is doing, how should we?

[244863]

Hi,

 

No, it is the counterCache which is trying to do the UPDATE.

 

I have 3 functions in my UsersController:

 

add which does a $this->User->save()

edit which does a $this->User->save()

delete which does a $this->User->delete()

 

when I use the delete function, it deletes the User. But the counterCache tries to UPDATE the address_count field on the Users table with an id of NULL.

 

The save()'s on the add / edit do not produce this error and run correctly.

 

Hope this explains it better.

[Jessica]

That makes no sense. When you delete a user, why would you attempt to update their Address count? You should call that when you delete an ADDRESS, not a user.

[244863]

Sorry got myself all in a muddle, it should of read...

 

"No, it is the counterCache which is trying to do the UPDATE.

 

I have 3 functions in my UsersController:

 

add which does a $this->User->save()

edit which does a $this->User->save()

delete which does a $this->User->delete()

 

when I use the delete function, it deletes the User. But the counterCache tries to UPDATE the user_count field on the Addresses table with an id of NULL.

 

The save()'s on the add / edit do not produce this error and run correctly.

 

Hope this explains it better."

 

Sorry.

[Jessica]

That makes even less sense. An address has multiple users???

[244863]

Yes as I wanted to make the DB relational so I split the tables so an Address hasMany Users.

 

This is for a building management issue logging system. So we may have 40 people that work out of one building at one address that need to log building issues.

 

For example: My light bulb needs replacing.

 

Thanks,

[DavidAM]

We have no idea how your database is laid-out and we have no idea what $this->User->delete() actually does, so we have no idea how to help.

 

In a relational design with the requirements you describe, I would have the following three tables:

 

Users: ID (Primary Key), detail user data

Addresses: ID (Primary Key), detail address data

UserAddresses: UserID (Foreign Key), AddressID (Foreign Key)

 

Then the delete process would be:

1) Delete from UserAddress Where UserID = <the user being deleted>

2) Delete from Users Where ID = <the user being deleted>

 

You would then have to deal with Addresses that are left with no Users assigned. Depending on your requirements, you might leave them, or use some process to "clean them out".

 

I don't know why or where you have an "UserCount" column. In the design above, to find out how many Users are associated with any given address, you do a Select COUNT() query with a Join. You should not try to maintain a count field.

[Jessica]

If you'd explained some of this up front it would have gone a lot faster.

It probably has to do with the configuration of your relationships, which you haven't shown.

I suggest you read the links in my signature on how to get help.

 

David: counterCache is supposed to handle that count automatically - hence why it's trying to update the userCount automatically when the users table is updated. It's a tool for CakePHP.

[244863]

Hi,

 

Addresses -> $hasMany -> Users

Users -> $belongsTo -> Addresses

 

I do not need a UserAddresses table as that would suggest a HABTM relationship which is not needed here.

 

All I want to know is why the counterCache is trying to update when I use the $this->Model->delete() and not when I use $this->Model->save

when the foreign_key is NULL???

Edited June 4, 2013 by 244863

[244863]

app/lib/Cake/Model/Model.php Line 1963

I wrapped the updateAll function with:

if (!empty($keys[$foreignKey])) {

Works a treat.