[SOLVED] Subquery help

[Sepph]

Hi,

 

I need some help understanding subqueries. I've tried reading general documentation but it's not really helping. The best way I think I'll understand is by seeing the code for what I need a subquery.

 

 

So,

 

I want to display the number of users that have posted more than X photos in the past X days.

 

The related tables with their relevant fields for this are users (id) and photos (id, user_id, created_at etc).

 

 

 

 

Another subquery I'd need is to count all photos with more than X tags. tags and users are linked by a taggings table.

 

The related tables of the db for this subquery would be users (id), tags (id), taggings (photo_id, tag_id).

 

 

I'm used to doing queries with the sprintf function, using mysql_real_escape_string, so I'd best understand if these were used in the explanation.

 

 

Any help would be appreciated, thanks in advance!

 

 

[Mchl]

These should work

 

SELECT 
  COUNT(*) AS userCount
FROM (
  SELECT
    user_id, COUNT(*) AS photoCount 
  FROM 
    photos 
  WHERE 
    created_at > DATE_ADD(NOW(), INTERVAL -? DAY) 
  GROUP BY
    user_id
  HAVING 
    photoCount > ?
  ) AS subQuery

 

SELECT
  COUNT(*) AS photoCount
FROM (
  SELECT
   photo_id,
   COUNT(*) AS photoCount
  FROM
    taggings
  GROUP BY
   photo_id
  HAVING
    photoCount > ?
) AS subQuery
  

 

[Sepph]

They work like a charm, thanks a lot mate, never thought I actually don't even need the users or photos tables.

 

 

 

[Sepph]

BTW is it possible to do this with JOINs?

[Mchl]

Most likely, but it would be overcomplicating things.

[Sepph]

Alright, another question if I may...

 

I want to count the photos with no votes. Problem is the votes table contains votes already given, so it doesn't contain photo id's for photos with no votes, which means I can't count them. I've just been using a separate table column with the vote cache, but is there a workaround inside the query?

 

The votes table contains: id, photo_id, ip, created_at.

[Mchl]

For example like this

 

SELECT
  COUNT(*) AS photoCount
FROM
  photos
WHERE
  id NOT IN (SELECT DISTINCT photo_id FROM votes)

 

or

 

SELECT
  COUNT(*) AS photoCount
FROM
  photos AS p
LEFT JOIN
  votes AS v
ON
  (p.id = v.photo_id)
WHERE
  v.photo_id IS NULL

[Sepph]

Thanks again, they both work great

[Sepph]

Bumped into another problem...

 

Trying to select users that have a minimum of X photos with a minimum of Y votes.

 

This is what my query looks like:

 

		$query = sprintf("SELECT firstname,
					 		 lastname
					  FROM users, photos
					  WHERE (SELECT COUNT(*) AS photoCount
							 FROM photos
							 WHERE (SELECT COUNT(*) AS votesCount
									FROM votes, photos
									WHERE votes.photo_id=photos.id
									) >= '%s'
							 ) >= '%s' AND
					  users.id = photos.user_id",
					  				mysql_real_escape_string($votes),
									mysql_real_escape_string($photos)
									);

 

 

Not getting the right results...

[Sepph]

Still haven't managed to get this to work. Any help as to how to link everything together would be appreciated!

[Mchl]

Ok... so first let's try to select photos with min Y votes

 

SELECT
  photo_id,
  COUNT(*) AS voteCount
FROM
   votes
GROUP BY
   photo_id
HAVING
  voteCount > ?

 

 

Now let's try to select users that have at least X of these photos

 

SELECT
  user_id,
  COUNT(*) AS photoCount
  FROM
   photos AS p
  INNER JOIN
  (
   -- here put the query above
  ) AS votesSubQuery
  ON
   (votesSubQuery.photo_id = p.id)
  GROUP BY
   user_id
HAVING
  photoCount > ?

 

At last, let's select their first and last names

 

SELECT 
  firstname,
  lastname
FROM 
  users AS u
INNER JOIN
  (
   -- here put the query above
  )  AS photosSubQuery
ON
  (photosSubQuery.user_id = u.ID)

 

 

 

[Sepph]

Thanks yet again!