Skip to content

Instantly share code, notes, and snippets.

@birarda
Created May 7, 2012 18:27
Show Gist options
  • Save birarda/2629479 to your computer and use it in GitHub Desktop.
Save birarda/2629479 to your computer and use it in GitHub Desktop.
SELECT
c.id checkin_id,
u.id,
u.nickname,
u.status_text,
u.photo,
u.major_job_category,
u.minor_job_category,
l.linkedin_data AS headline,
p.filename,
c.lat,
c.lng,
UNIX_TIMESTAMP(c.checkout_date) AS checkout,
c.checkout_date,
c.checkout_date > NOW() as checked_in,
v.id AS venue_id,
(SELECT COUNT(*) FROM " . CHECKINS . "
WHERE venue_id = v.id AND userid = c.userid) checkin_count,
(SELECT SUM(UNIX_TIMESTAMP(checkout_date) - UNIX_TIMESTAMP(checkin_date))
FROM " . CHECKINS . "
WHERE venue_id = v.id
AND userid = c.userid
AND checkout_date > NOW() - INTERVAL 7 DAY) checkin_time
FROM " . CHECKINS . " c
INNER JOIN " . USERS . " AS u ON u.id = c.userid
LEFT JOIN " . PHOTOS . " AS p ON u.photo = p.photo
LEFT JOIN " . VENUES . " AS v ON v.id = c.venue_id
LEFT JOIN " . USERS_LINKEDIN . " AS l ON l.linkedin_id = u.linkedin_id AND l.field = 'headline'
WHERE
u.`active` = 'Y' AND
v.id in (" . $venue_IDs_string . ")
$checkin_interval_sql
GROUP BY u.id, v.id
ORDER BY c.checkin_date DESC";
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment