检查连接中是否存在记录并分配和计数

Im making an application where its possible to vote on pictures. Im doing some joins and would like to check each picture i get, if the user logged in has voted on it.

My votes table setup is like this:

+-------------------------+
| id | user_id | photo_id |
+-------------------------+
|  1 |    2    |     6    |
+-------------------------+
|  2 |    4    |     5    |
+-------------------------+
|  3 |    3    |     5    |
+-------------------------+
|  4 |    1    |     6    |
+-------------------------+

Im joining 3 tables:

users, photos and votes

SELECT 
    users.*, 
    photos.*, 
    users.id as user_userid, 
    photos.id as photo_photoid, 
    COUNT(votes.id) as totalvotes 
FROM
    photos 
LEFT JOIN 
    votes 
ON 
    votes.photo_id = photos.id 
LEFT JOIN
    users 
ON 
    users.id = photos.author_id 
GROUP BY
    photos.id 
ORDER BY
    totalvotes 
DESC

I would like to make a query inside this sql that does something like this:

+---------------------------------------------------------------------------------+
| photo_photoid | user_userid | totalVotes | currentUserHasVotetThisAmountOfTime  |
+---------------------------------------------------------------------------------+
|        6      |      1      |      2     |                 1                    |
+---------------------------------------------------------------------------------+
|        5      |      1      |      2     |                 0                    |
+---------------------------------------------------------------------------------+

So i guess im looking for a count of the records, where votes.user_id = $MyLoggedInUser AND votes.photo_id = photo.id

Any suggestions?

Here is your request :

SELECT
  v.photo_id,
  COUNT(v.id) AS total_votes,
  (SELECT COUNT(id) FROM vote WHERE photo_id = v.photo_id AND user_id = 1) AS currentUserHasVotetThisAmountOfTime  
FROM
  vote AS v
GROUP BY
  v.photo_id
ORDER BY
  total_votes
  DESC

Just replace user_id = 1 by your own ID (in the sub-request line 4).

I get ride of the user_id column, since this is your something you provide there is imo no point to return this in the query as well.

If you want to test it by yourself : http://sqlfiddle.com/#!2/ba2a1/16/0