On Jun 12, 7:16 pm, lavin <[email protected]> wrote:
> Although I am new to cake, this SQL query could work with the query()
> function
>
> SELECT t1.prod_id
> FROM HABTMtable t1
> WHERE t1.user_id=1
> AND t1.prod_id IN
> (SELECT t2.prod_id
> FROM HABTMtable t2
> WHERE t2.user_id=2)
This sort of approach is likely to be nicer to the db, especially if
one or the other user has lots of favorites:
SELECT
*
FROM
products_users as user1s_faves
RIGHT JOIN
products_users as user2s_faves ON (
user1s_faves.user_id = 2 AND
/* the magic bit */
user1s_faves.product_id = user2s_faves.product_id
)
OR similarly:
SELECT
*
FROM
products
RIGHT JOIN
products_users as user1s_faves ON (
user1s_faves.product_id = products.id AND
user1s_faves.user_id = 1
)
RIGHT JOIN
products_users as user2s_faves ON (
user1s_faves.product_id = products.id AND
user1s_faves.user_id = 2 AND
/* the magic bit */
user1s_faves.product_id = user2s_faves.product_id
)
Be sure to checkout mark story's "deepfinding" blog post, or the
bakery article by nate on how to achieve this sort of sql (very
easily) using find so that, amongst other things, you can paginate the
results and protect yourself against injection. What joins to what
depends on what you're going to do with the result of course
hth
AD
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
