Hey all...
New to PHP, Cake, and MySQL so bear with me.
I've been searching high and low for the best way to make this query
work, but just haven't gotten it yet.
Here's my setup:
I have models for Users, Subscriptions, Authors, and Posts where:
User HasMany Subscription (pk_User.id, fk_Subscription.user_id),
Author, HasMany Subscription (pk_Author.id,
fk_Subscription.author_id),
Author HasMany Post (pk_Author.id, fk_Post.author_id)
and all of the related BelongsTo's as well.
I want to search $this->User->Subscription->Author->Post (from the
UsersController) for all posts where the logged in user has a current
subscription to that(/those) author(s).
E.g. - ($this->Auth->user('id') = Subscription.user_id AND
Subscription.expiration_date >= date ('Y m d') AND
Subscription.author_id = Post.author_id).
Further, each Post contains a second field (INT) called
Post.access_level and this must be lower than the value stored in
Subscriptions.subscription_level.
The trick, of course, isn't just doing this, but doing it
efficiently. Here are the options I've thought of.
- I could query $this->User->Subscriptions for relevant subscriptions
and return either the full array (set to $subscriptions) or a 'list'
of key/value pairs where key => author_id and value =>
subscription_level. However, in issuing a second find() call to Post,
I don't know how I would compare the key/value pairs in the
$subscriptions array with the values for 'Post.author_id' and
'Post.access_level' where the evaluation occurs at the row level in
$subscriptions. With the find('all') array I mentioned first,
$subscriptions returns an array with [key][Subscription][field] so I
can't set conditions for 'Post.author_id' and 'Post.access_level'
without a foreach() which I don't want because of the extra database
queries it would generate.
-Alternately, I could use BindModel() to create a HABTM (or I could
just create it permanently in the models) relationship between
Subscription and Post. This option requires an extra join table in my
database though, and may result in slower database queries as all the
joins are performed. Additionally, there are other models (File,
Event, etc) that are owned by Author and each of these would require
an extra join table and HABTM relationship. I could be wrong, but
doing all of this seems somehow redundant and there should be a more
elegant solution.
-There are also probably other ways using PHP functions to manipulate
the arrays after they have been returned from find calls (e.g.
Subscriptions->find w/ conditions and Post->find w/ conditions and
then some PHP array functions to compare those two arrays), but I'm
too new to this to know where to even start with that.
There's got to be a simple method I'm missing (or just don't know
about yet). Any ideas?
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
