Discussie: [Android] Developers

SELECT
	ld.id,
	ld.username,
	ld.firstName,
	ld.lastName,
	CONCAT( ld.firstName, ' ', ld.lastName ) AS fullName,
	ld.email,
	ld.subtitle,
	ld.signedUp,
	ld.inClass,
	cl.yearDiff AS startYear,
	ld.avatarLoc,
	ld.allowed,
	ld.confirmed,
	IF( ld.allowed + ld.confirmed = 2, 1, 0 ) AS active,
	COUNT( lg.userId ) AS sessionCount,
	ld.signature,
	ls.endTime AS lesTo,
	ls.lokaal AS lesIn,
	ls.les AS lesType,
	ls.docent AS lesDocent,
	IF( ISNULL( lg.lastActivity ), ld.signedUp, MAX( lg.lastActivity ) ) AS lastActive,
	TIMEDIFF( NOW(), IF( ISNULL( lg.lastActivity ), ld.signedUp, MAX( lg.lastActivity ) ) ) AS timeInactive,
	lg.ipAdress AS lastIpAdress,
	lg.requestURI AS lastPage,
	IF( RIGHT( lg.hostname, 9 ) = ".avans.nl", 1, 0 ) AS atSchool 
FROM
	leden AS ld
	LEFT JOIN classes AS cl ON
		cl.className = ld.inClass
	LEFT JOIN lessen AS ls ON
		ls.class = ld.inClass AND
		ls.startTime < NOW() AND
		ls.endTime > NOW()
	LEFT JOIN logonSessions AS lg ON
		lg.userId = ld.id
	LEFT JOIN logonSessions AS lg2 ON
		lg2.userId = ld.id AND
		lg2.lastActivity < lg.lastActivity		
GROUP BY id

SELECT
	tracker.*,
	IF( ISNULL( l.lastID ), 'inactive', IF( l.lastID < tracker.lastReactID, 'unread', 'read' ) ) AS readStatus
FROM
	(
		(SELECT
			'react' AS actionType,
			CONCAT_WS( '::', 'react', a.section, a.entry, id ) AS identifier,
			a.section AS section,
			a.entry AS entry,
			lastAction,
			id AS lastReactID,
			r.message AS lastReactMessage,
			r.posterId AS lastReactPoster,
			GROUP_CONCAT( r.posterId ) AS posters,
			GROUP_CONCAT( r.escapedMessage ) AS messages,
			CONCAT( 
				IF( r.section = 'fotos', ( SELECT storeLocation FROM fotos WHERE id = a.entry LIMIT 1 ), '' ),
				IF( r.section = 'user', ( SELECT avatarLoc FROM leden WHERE id = a.entry LIMIT 1 ), '' ) 
				) AS image, 
			IF( r.section = 'fotos', ( SELECT inAlbum FROM fotos WHERE id = a.entry LIMIT 1 ), NULL ) AS albumID,		
			IF( r.section = 'fotos', ( SELECT name FROM fotoAlbums WHERE id = ( SELECT inAlbum FROM fotos WHERE id = a.entry LIMIT 1 ) LIMIT 1 ), NULL ) AS albumName
		FROM
			recentReactions AS a
			LEFT JOIN ( SELECT *, REPLACE( message, ',', ',' ) as escapedMessage FROM reactions ORDER BY postedOn DESC ) AS r
				ON ( r.section = a.section and r.entry = a.entry )
		GROUP BY r.entry
		ORDER BY lastAction DESC)
	UNION
		(SELECT
			'shout' AS actionType,
			CONCAT( 'shout::0::', id ) AS identifier,
			'shout' AS section,
			'0' AS entry,
			postedOn AS lastAction,
			id AS lastReactID,
			message AS lastReactMessage,
			authorID AS lastReactPoster,
			authorID AS posters,
			message AS messages,
			NULL AS image,
			NULL AS albumID,
			NULL AS albumName
		FROM 
			shouts 
		ORDER BY 
			postedOn DESC
		LIMIT 25) 
	) AS tracker
	LEFT JOIN lastRead AS l ON
		( l.section = tracker.section AND l.entry = tracker.entry AND userID = 11 )
ORDER BY
	lastAction DESC