Active records - "smart" select |
[eluser]ibnclaudius[/eluser]
I have three tables: users(id,name), users_communities(user_id,community_id), communities(id,name) I know how to select the communities the user doesn't have, but I want a more efficient result... Here's my idea, I'll show an example. User A has these communities: PHP,JAVA,ORACLE User B: PHP,MYSQL,JAVASCRIPT User C: PHP,CODEIGNITER,SOCCER User D: PHP,CODEIGNITER,MYSQL Let's say I want to show to user A the top 2 communities he may want. In this case would be CODEIGNITER and MYSQL. What I did? Select all common users in communities that I have. Select the 2 most common communities in these users. I'm pretty sure that should be something much more complex, but this is the beginning...
[eluser]vitoco[/eluser]
I don't use active records, so i can only help you with plain sql : Code: $user_id = 1 ; Saludos
[eluser]ibnclaudius[/eluser]
I got it: http://sqlfiddle.com/#!2/c501e/47 But there's still a little problem, this return only communities that have a user that follow another community in common with me. I want to select all communities, but ordering than in a smart way, just like the sqlfiddle link... What should I change?
[eluser]ibnclaudius[/eluser]
Code: SELECT communities.*
[eluser]vitoco[/eluser]
I had to use a sub-select ( very fast if the table is well indexed ) but it works. http://sqlfiddle.com/#!2/444e5/12/0 Saludos
[eluser]vitoco[/eluser]
Code: // http://sqlfiddle.com/#!2/444e5/12 Saludos
|
Welcome Guest, Not a member yet? Register Sign In |