Re: Help with complex find() spanning several models

Awesome. Thanks.

On Oct 31, 4:37 pm, teknoid <teknoid.cake...@gmail.com> 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 <aaronb...@gmail.com> 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 <teknoid.cake...@gmail.com> 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 <aaronb...@gmail.com> 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 cake-php+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---