CODEWITHSUNDEEP
phpmysqlsql
Valeriu Mazare
Valeriu Mazare

Reputation: 323

Connect 2 mysql queries

I have 2 tables:

users

id | user

likes

id | owner | date

And this query:

$owner_result = $connector->query("SELECT id FROM users");
while($owner = $connector->fetchArray($owner_result))
{
$rs = $connector->query("SELECT count(id) AS num FROM likes 
WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK) 
AND owner='$owner[id]' 
ORDER BY num DESC LIMIT 5");
while($rw = $connector->fetchArray($rs)) 

{echo "This owner ($owner[id]) has $rw[num] likes this week";}

}

What i want i to do is to return me 5 users.id rows with the most big ammount of rows in likes table. My query returns me just $owner[id] but not ammount of likes for each one. And i think the performance of my query is very low as well because my query checks amount of likes for every users.id in likes table, but it may be that likes table doesn't contain some users.id .

Any advice for solve my problem or improve my query will be very welcome. Thank you.

Upvotes: 0

Views: 61

Answers (3)

spencer7593
spencer7593

Reputation: 108430

I think a single query would get you the specified result, for example:

SELECT u.id
     , COUNT(t.owner) AS cnt_likes
  FROM users u
  JOIN likes t
    ON t.owner = u.id
 WHERE t.date > DATE_SUB(NOW(), INTERVAL 1 WEEK)
 GROUP BY u.id
 ORDER BY 2 DESC
 LIMIT 5

An appropriate index on the likes table should improve performance:

... ON likes (owner, date)

Alternatively, this will give an equivalent result, possibly with (slightly) better performance:

SELECT u.id
     , t.cnt_likes
  FROM users u
  JOIN ( SELECT s.owner
              , COUNT(1) AS cnt_likes
           FROM likes s
          WHERE s.date > DATE_SUB(NOW(), INTERVAL 1 WEEK)
          GROUP BY s.owner
       ) t
    ON t.owner = u.id
 ORDER BY t.cnt_likes DESC
 LIMIT 5

You would get even better performance if you were guaranteed that all the values of the owner column in the likes table were actually id values in the user table... because you could avoid the join to the users table, and get the entire result from the likes table:

SELECT s.owner
     , COUNT(1) AS cnt_likes
  FROM likes s
 WHERE s.date > DATE_SUB(NOW(), INTERVAL 1 WEEK)
 GROUP BY s.owner
 ORDER BY cnt_likes DESC
 LIMIT 5

But, that query doesn't do the check to verify the value returned from the owner column exists as in the id column of the user table.

Upvotes: 1

VMai
VMai

Reputation: 10336

You should join the two tables so you can get your data in one single statement:

SELECT
    users.id,
    COUNT(likes.id) AS mylikes     -- specify a name for the computed column
FROM users
INNER JOIN likes
ON users.id = likes.owner
WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK)
GROUP BY users.id
ORDER BY COUNT(likes.id) DESC      -- MySQL would allow the alias name 
LIMIT 5                            -- because you want the top 5

If there's a tie, then you will lose a record (or more).

Upvotes: 1

Varvarigos Emmanouil
Varvarigos Emmanouil

Reputation: 757

You have to do a group by owner in the second query. You can't count rows without group them first.

$owner_result = $connector->query("SELECT id FROM users");
while($owner = $connector->fetchArray($owner_result))
{
$rs = $connector->query("SELECT count(id) AS num FROM likes 
WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK) 
AND owner='$owner[id]'
Group BY owner 
ORDER BY num DESC LIMIT 5");
while($rw = $connector->fetchArray($rs)) 

{echo "This owner ($owner[id]) has $rw[num] likes this week";}

}

Upvotes: 1

Related Questions