+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Active records - "smart" select (/showthread.php?tid=52554)
Pages:
1
2
Active records - "smart" select - El Forum - 06-15-2012
[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...
Active records - "smart" select - El Forum - 06-15-2012
[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
Active records - "smart" select - El Forum - 06-15-2012
[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?
Active records - "smart" select - El Forum - 06-15-2012
[eluser]vitoco[/eluser]
i get nothing from the sqlfiddle link
Active records - "smart" select - El Forum - 06-15-2012
[eluser]ibnclaudius[/eluser]
http://sqlfiddle.com/#!2/c501e/41
Active records - "smart" select - El Forum - 06-15-2012
[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;Active records - "smart" select - El Forum - 06-15-2012
[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
Active records - "smart" select - El Forum - 06-15-2012
[eluser]ibnclaudius[/eluser]
Get nothing from the link... =/
Active records - "smart" select - El Forum - 06-15-2012
[eluser]vitoco[/eluser]
Code:
// http://sqlfiddle.com/#!2/444e5/12
// the link tag deletes the "!"Saludos
Active records - "smart" select - El Forum - 06-15-2012
[eluser]ibnclaudius[/eluser]
Thanks, is it possible to make it using join?