Help with complex find() spanning several models
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 cake-php@googlegroups.com
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
-~--~~~~--~~--~--~---
Re: Help with complex find() spanning several models
On Oct 31, 3:28 pm, teknoid [EMAIL PROTECTED] wrote:
That's a lot to read, but I can point you in the direction of checking
out the Containable behavior (in the manual).
... as well as really carefully reading up on the model associations
and data retrieval.
On Oct 31, 2:30 pm, 33rtp [EMAIL PROTECTED] wrote:
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 cake-php@googlegroups.com
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
-~--~~~~--~~--~--~---
Re: Help with complex find() spanning several models
Thanks for the help teknoid. I've done that, and I think there are
some possibilities there, but they are a little longer than I'd like.
For those who might come after this and not feel like reading the full
situation above, my question in a nut-shell really is:
How do you run a find on a field in a Model that is two associations
away rather than just one?
E.g. - When querying Post which belongsTo Artist which hasMany
Subscription, how do I compare a field in Post to a field in
Subscription? Cake doesn't seem to want to create a second JOIN in
the SQL output.
On Oct 31, 3:28 pm, teknoid [EMAIL PROTECTED] wrote:
That's a lot to read, but I can point you in the direction of checking
out the Containable behavior (in the manual).
... as well as really carefully reading up on the model associations
and data retrieval.
On Oct 31, 2:30 pm, 33rtp [EMAIL PROTECTED] wrote:
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 cake-php@googlegroups.com
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
-~--~~~~--~~--~--~---
Re: Help with complex find() spanning several models
Awesome. Thanks.
On Oct 31, 4:37 pm, teknoid [EMAIL PROTECTED] wrote:
By default joins are only built for hasOne or belongsTo.
Here's how to trick cake into building joins for deep model
bindings:http://teknoid.wordpress.com/2008/07/17/forcing-an-sql-join-in-cakephp/
there is another post on my blog if you search, which has a slightly
more advanced example of the same principal.
On Oct 31, 4:28 pm, 33rtp [EMAIL PROTECTED] wrote:
Thanks for the help teknoid. I've done that, and I think there are
some possibilities there, but they are a little longer than I'd like.
For those who might come after this and not feel like reading the full
situation above, my question in a nut-shell really is:
How do you run a find on a field in a Model that is two associations
away rather than just one?
E.g. - When querying Post which belongsTo Artist which hasMany
Subscription, how do I compare a field in Post to a field in
Subscription? Cake doesn't seem to want to create a second JOIN in
the SQL output.
On Oct 31, 3:28 pm, teknoid [EMAIL PROTECTED] wrote:
That's a lot to read, but I can point you in the direction of checking
out the Containable behavior (in the manual).
... as well as really carefully reading up on the model associations
and data retrieval.
On Oct 31, 2:30 pm, 33rtp [EMAIL PROTECTED] wrote:
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 cake-php@googlegroups.com
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
-~--~~~~--~~--~--~---
