• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Active records - "smart" select

#1
[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...

#2
[eluser]vitoco[/eluser]
I don't use active records, so i can only help you with plain sql :

Code:
$user_id = 1 ;
$sql = "
SELECT
  c.id AS community__id ,
  c.name AS community__name ,
  COUNT( DISTINT( c.user_id ) AS num_users_per_community
FROM
  communities AS c
LEFT JOIN
  user_communities AS uc
ON
  (
   uc.community_id = c.id AND
   (
    user_id <> ".$user_id." OR
    user_id IS NULL
   )
  )
GROUP BY
  c.id
ORDER BY
  num_users_per_community DESC
LIMIT
  0 , 2
";

Saludos

#3
[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?

#4
[eluser]vitoco[/eluser]
i get nothing from the sqlfiddle link

#5
[eluser]ibnclaudius[/eluser]
http://sqlfiddle.com/#!2/c501e/41

#6
[eluser]ibnclaudius[/eluser]
Code:
SELECT communities.*
    -- users who share at least one community
    FROM users
    JOIN users_communities ON (
        users_communities.user_id = users.id
        AND user_id <> 1
    )
    JOIN users_communities AS c_u_communities ON (
        c_u_communities.community_id = users_communities.community_id
        AND c_u_communities.user_id = 1
    )
    -- (end of) users who share at least one community
    -- these other users communities
    JOIN users_communities AS o_u_communities ON (
        o_u_communities.user_id = users.id
        AND o_u_communities.user_id <> 1
    )
    JOIN communities ON (
        communities.id = o_u_communities.community_id
    )
    LEFT JOIN users_communities AS c_u_not_communities ON (
        c_u_not_communities.community_id = o_u_communities.community_id
        AND c_u_not_communities.user_id = 1
    )
    WHERE c_u_not_communities.community_id IS NULL
    GROUP BY (communities.id)
    ORDER BY COUNT(communities.id) DESC
    LIMIT 2;

#7
[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

#8
[eluser]ibnclaudius[/eluser]
Get nothing from the link... =/

#9
[eluser]vitoco[/eluser]
Code:
// http://sqlfiddle.com/#!2/444e5/12
// the link tag deletes the "!"

Saludos

#10
[eluser]ibnclaudius[/eluser]
Thanks, is it possible to make it using join?


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.