On Tue, Jul 13, 2010 at 4:57 AM, Snaky Love <[email protected]> wrote: > Hi, > > I have an "interesting" problem here - in a given mysql database > scheme some sql wizard used comma-separated values in a text-field and > with that values lookups have to be done. The data itself is simple > numbers like 1,34,25,66,78,134 and so on. So what I have is something > like this: > > id | username | groups | more... > --------------------------------------------- > 1 | name | 1,23,4,55,6 | ... > 2 | name2 | 3,2,4,5 | ... > > The groups string can be very long. there is also a table "groups" > with "id, name", as expected. > > Yes, this is bad design. No, I can not immediately change the design, > I will try to, but atm I have to handle the situation as it is. > > Of course normally this would be solved with an intersection table and > a many-to-many relation. For anybody interested: there is a book by > Bill Karwin called "SQL Antipatterns" - he names the described design > Jaywalking and it is the first antipattern in the book. I was > surprised to find it in real life.... > > So my question is: how to handle this with cakephp? I really would > like to use cake to bake a nice management interface on top of that > tables, but currently I do not know how to go on with that jaywalking > antipattern in my way. > > My first idea was to create my own intersection table - but the > process of [re-]converting data forth and back does only work in a > static environment - but the data is heavily used and so > transformations of tabledata would be neccassery on every request to > mirror the live situation... so I am looking for a good way to > implement some kind of layer that would translate this filed for > cakephp into an intersaection table so that I can use cake models - > maybe a kind of datasource? Will this work? > > What do you think? How to handle this?
Run, don't walk! heh. Well, off the top of my head, I think you could add some beforeSave/afterSave logic to keep things synced. Say you provide a $data array with all the Group info correctly formatted for Cake. When you save, the data for the Group join is also saved. Then, in the afterSave callback, your code might fetch all Group.id for this User, and, separately, the CSV string for this silly User.groups column. Explode the latter into its own tmp array, sort both sets of data, and then either add or remove values from the join table or the CSV list and re-save. Something like that, anyway. This assumes that you can create your own join table and the association is set up. But get rid of that User.groups column as soon as possible. If you can create your own table, why can't you change the User table? If some other part of the app is depending on User.groups being a string like this, then you should address that first to make it more Cake-like, if possible. Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
