Re: Tough find query!
On Jun 12, 7:16 pm, lavin 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 cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: Tough find query!
Although I am new to cake, this SQL query could work with the query() function SELECT t1.prod_id FROMHABTMtable t1 WHERE t1.user_id=1 AND t1.prod_id IN (SELECT t2.prod_id FROMHABTMtable t2 WHERE t2.user_id=2) Second SELECT gets all products from user2, while first SELECT gets products that belong to user 1 and are in the second query (belonging to both users) On 9 jun, 10:40, Céryl wrote: > Allright, > I;ve been cracking my head on this. > > I have a HABTM relation between users and products. Users can select a > product to put on his favorite list. Everything works great (A lot of > thanks to this Googlegroups for that!) but now I need something heavy. > > So I have a users table, products table and the HABTM table with > user_id and product_id. From this table I can get, using Containable > behaviour) the products belonging to a certain user. > > Now I want to match the lists of two users together. So using two id's > from two different users, I need to get a list of products from the > table that occur in both users list. > > So: > User1 User2 > Product1 Product2 > Product3 Product3 > Product5 Product6 > Product6 > > Needs to return an array containing Product 3 and product 6. I've been > reading and re-reading containable behaviour and the internet, and > though about getting both lists from the database and just use a PHP > query to match. Is there an easy solution, or do you recommend using > PHP instead of SQL for this, timewise? > > Thanks a million! --~--~-~--~~~---~--~~ 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Tough find query!
Allright, I;ve been cracking my head on this. I have a HABTM relation between users and products. Users can select a product to put on his favorite list. Everything works great (A lot of thanks to this Googlegroups for that!) but now I need something heavy. So I have a users table, products table and the HABTM table with user_id and product_id. From this table I can get, using Containable behaviour) the products belonging to a certain user. Now I want to match the lists of two users together. So using two id's from two different users, I need to get a list of products from the table that occur in both users list. So: User1 User2 Product1 Product2 Product3 Product3 Product5 Product6 Product6 Needs to return an array containing Product 3 and product 6. I've been reading and re-reading containable behaviour and the internet, and though about getting both lists from the database and just use a PHP query to match. Is there an easy solution, or do you recommend using PHP instead of SQL for this, timewise? Thanks a million! --~--~-~--~~~---~--~~ 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
