
Wil works for Visual Lizard Inc. Wil is a web developer. Wil has been a web developer for over eight years. Wil likes working on web sites. Wil makes web pages in his spare time. Wil is one of those lucky people who get’s paid for doing what he loves doing. Wil would happily work on web sites for free. But Wil needs to pay bills. Luckily, developing web sites pays Wil’s bills. Lucky Wil.
Wil is influenced by many things, and it is reflected in Wil’s work. Wil likes comic books. Wil continues to collect graphic novels from a variety of publishers to this day. Wil likes movies, even the cheesy bad ones. Wil is one of those people that keep Hollywood in business, despite the drek they put out. Wil plays video games. Wil owns a Gamecube, and defies anyone to convince him he made the wrong choice. Wil plays roleplaying games, both table top and live action. Wil likes roleplaying because it’s like playing pretend as kids.
Wil was a kid in Thompson, Manitoba, Canada. Wil was very cold there. Wil moved down to Winnipeg, Manitoba, Canada to continue his schooling. Wil is less cold here. Wil attended the University of Manitoba and Robertson College. But many of Wil’s skills were self taught. Wil spent many a moment in front of a computer. Wil read web sites. Wil learned web sites. Wil built web sites.
Wil entered the web industry in 2000. Wil learned about dynamic content and database oriented design at his job. Wil pushed and prodded at scripting languages and datatable layout. Wil discovered a lot about what makes a content managed web site tick. Wil made it sit up and beg. Wil also worked alongside a world class web designer. Wil learned about grids and colours and layout. Wil learned how important proper navigation and content and presentation was. Wil built some excellent web sites for very important people.
Wil joined Visual Lizard Inc in 2005. Wil learned about open source software. Wil learned MySQL and PHP. Wil discovered many smart people working with the same tools, and learned a lot from them. Wil wrangled code, working alongside already brilliant Visual Lizard staff to improve Catalyst, their content management system.
Wil works with Visual Lizard to build web sites. Wil gets paid, and buys comic books and video games.
Lucky Wil!
Working on a CakePHP application, I found I needed to associate two models, but not in the regular way. In most cases, I could make a hasMany relationship by putting an appropriately-named foreignKey in the other datatable. So, if a Model named "Post" hasMany "Comment", then you just needed to add a "post_id" field to the "Comment" datatable and populate it correctly.
However, I came across what was technically a hasAndBelongsToMany relationship between two models. One was "Pulldown", and each row represented a select pulldown field. The other was "Option", and each row was an option in the select pulldown fields.
| id | pulldown |
|---|---|
| 1 | What is your gender? |
| 2 | Do you live in canada? |
| id | option |
|---|---|
| 1 | male |
| 2 | female |
| 3 | unknown |
| 4 | yes |
| 5 | no |
Since each Pulldown would use multiple Options, and in most cases, each set of Options would be used in more than one Pulldown, the regular CakePHP way would be to create a "pulldowns_options" lookup datatable... but for my application, managing that lookup datatable was incredible cumbersome. There had to be another way of linking this data.
The finderQuery option seemed to be exactly what I was looking for. I could define the relationship as a hasMany, and then write my own SQL to get the exact Option rows I wanted for each Pulldown rows. I easily set up some extra fields...
| id | use_set | pulldown |
|---|---|---|
| 1 | sex | What is your gender? |
| 2 | bool | Do you live in canada? |
| id | set | option |
|---|---|---|
| 1 | sex | male |
| 2 | sex | female |
| 3 | sex | unknown |
| 4 | bool | yes |
| 5 | bool | no |
Perfect! Now I could group my Option rows into "sets", and just define which set I would like to use for each Pulldown. Now, when defining my hasMany relationship in my Model, I could set the finderQuery variable...
class Pulldown extends AppModel {
var $hasMany = array(
’Option’ => array(
’className’ => ’Option’,
’finderQuery’ => ’SELECT Option.* FROM options AS Option WHERE Option.set="sex"’
)
);
}
Wait, another problem. This will work if I only ever want specifically my "sex" set of Option rows. What I needed was some sort of placeholder or variable that I could put into my finderQuery SQL, something like this...
class Pulldown extends AppModel {
var $hasMany = array(
’Option’ => array(
’className’ => ’Option’,
’finderQuery’ => "SELECT Option.* FROM options AS Option WHERE Option.set=$PulldownUseSet"
)
);
}
So I did some googling, and found that there is a variable available for this purpose! It was called {$__cakeID__$} and could just be put into the SQL. The down side was that it represented the class model’s ID value per row. So in my case, it would be equal to Pulldown’s "id" value. That’s not helpful at all, I could just do a regular hasMany in that case!
Ah, but no panic! The finderQuery allows me to write any amount of SQL to get exactly what I want. There’s no reason I couldn’t write the SQL and use the {$__cakeID__$} placeholder to find the current Pulldown row and join it to my Option datatable. So, I changed the relationship to this...
class Pulldown extends AppModel {
var $hasMany = array(
’Option’ => array(
’className’ => ’Option’,
’finderQuery’ => ’SELECT Option.* FROM pulldowns, options AS Option WHERE pulldowns.id={$__cakeID__$} AND pulldowns.use_set=Option.set’
)
);
}
So a quick test and yes, the CakePHP debug was showing that the SQL was running exactly as expected. So now, my "What is your gender?" Pulldown row was doing another SQL call, and getting three rows from the Option model without any errors. Excellent. Except when I went to look at my select pulldowns, I still had no options. What gives?
More poking and I discovered that, although the SQL was being run correctly, and the three rows were being found correctly, the results were not being put into my data result. The association was there, but it was always empty! Why? It had the rows, it even said so! Why wasn’t it available in my Model find() result?
More googling and digging in the cakePHP lib files, and I found that, even when rows for the association is found, CakePHP still checks to see if the Option foreignKey matched the Pulldown id field. If it didn’t match (which it couldn’t, since I did not have a pulldown_id field in my Option datatable), it wouldn’t add it to the returned data. Argh! Are you kidding me? If that field was in there like that, then I wouldn’t need the finderQuery!
Luckily, my co-worker Dwayne calmed me down and told me how to correct this. By just specifying the foreignKey in the association as "false", then that check would not happen...
class Pulldown extends AppModel {
var $hasMany = array(
’Option’ => array(
’className’ => ’Option’,
’foreignKey’ => false,
’finderQuery’ => ’SELECT Option.* FROM pulldowns, options AS Option WHERE pulldowns.id={$__cakeID__$} AND pulldowns.use_set=Option.set’
)
);
}
Huzzah, at last! This works as I expected! For each Pulldown row, I can specify a hasMany relation using SQL that I specify. The important thing is that foreignKey must be specified, and it must be false, not null or 0. Hope this helps out anyone else trying to do the same! :)
Thanks a lot for the post, extremely useful for non conventional database designs.
Thanks Wil :)
Sorted out a problem that was giving me a nasty headache!
Folklorama
Aug 01, 2010
Scott Pilgrim vs The World
Aug 13, 2010
Resident Evil Afterlife
Sep 10, 2010
Iron Man 2
Sep 28, 2010
Urbanized
Gary Hustwit next film in his design trilogy, after Helvetica and Objectified.
Linked by Wil Alambre
Apple looking into iOS4 and 3G issues?
My iPhone 3G has become frustrating to use since upgrading to iOS4. What happened to the silky smooth experience that sold me on the hardware in the first place?
Linked by Wil Alambre
Office for Mac 2011 beta 5
Linked by Wil Alambre
W3C's Unicorn Validator Checks Multiple Standards at Once
It's described as brutally unforgiving and that it's near impossible to get a perfect score. Sounds like a challenge! ;).
Linked by Wil Alambre
The Gamer's Alphabet
Q is for Q-bert's curse filled speech bubble... he was a bit of a potty-mouth, wasn't he?
Linked by Wil Alambre
YouTube to increase upload limit from 10 to 15 minutes
Looking forward to bigger, longer internet meme music videos. Oi. :).
Linked by Wil Alambre
The Longest Photographic Exposures in History
Some people take long exposures for what... hours? Days? How about months? Or years!
Linked by Wil Alambre
Flower Warfare - Psychedelic Action Scene
Linked by Wil Alambre
Thanks a million for this post.
One note: You MUST name your results to match the class name. As in the example above "Options.*" matchs className Options.