Can you include two models in one query in CakePHP?

Question

I am building a book review application.

In the Review model, I have book_id and several fields like author_rating and/or scary_rating.

In the Book model, I have a search() function that I'd like to use to search for books with certain characteristics, like an author_rating of above 5, for example.

What is the best way to accomplish this? I know these are probably wrong, but I could add the attributes (author_rating, scary_rating, etc) into the book model and update them (average them in) each time a review is submitted; or, I could run a cron task that updates those fields every so often.

But is there a better way where I could query both the Book and Review models to come up with Books and meet certain criteria defined by looking at the Reviews database?

Does that make sense?

Answer 1

you could use union, to combine two queries!!!

query 1......

union

query 2.....

order by ratings LIMIT 1

in this kind of format, if you want real life example, then im willing to give you one,

SELECT
               u.username, u.picture,m.id, m.user_note, m.reply_id, m.reply_name, m.dt
              FROM
                relationships r,
                notes m,
                user u
              WHERE
                m.user_id = r.leader
              AND
                r.leader = u.user_id
              AND
                r.listener = '$user_id'
             UNION
               select username, picture,id, user_note, reply_id, reply_name, dt
               from user u, notes b
               where u.user_id = b.user_id
               and
               b.user_id ='$user_id'
               ORDER BY dt DESC LIMIT 10";

Answer 2

It sounds like you have a bunch of reviews and you want to average them on the fly when someone searches, is this correct? If so, I think it would probably work better to save the averaged ratings as fields in the Book model.

The reason I say this is that it is likely your app will be doing more searching than saving reviews, and it's probably more important to have searches returned quickly than reviews saved quickly.

However, if you want to calculate these averages on the fly, you can use MySQL's AVG() function. Check this SO post for an example that I think is pretty close to your situation:

http://stackoverflow.com/questions/1627567/mysql-can-i-combine-these-2-sql-statements-combine-join-and-avg

Also, this is a situation where I'd probably just write the SQL and stick it into a query(), rather than trying to wrestle with Cake's ORM syntax:

http://book.cakephp.org/view/456/query