Hi
I have complex SQL query, which itself has no issue, except, Phprunner cannot detect the ID key column. It's only detecting last_online so I am unable to create view pages. Is there any special syntax I need to use in complex queries so all the columns can be read by Phprunner?
This is my SQL query
SELECT
users.ID,
users.username,
photos.photo1,
photos.photo1_type,
profile.title,
profile.description,
profile.age,
profile.gender,
profile.ethnicity,
profile.height,
profile.build,
profile.likes,
profile.smoker,
location.travel,
location.host,
(TIMEDIFF(NOW(),profile.last_active)) AS last_online,
location.latitude,
location.longitude,
location.distance_from_home,
GREATEST(distance_calculation.distancecalc,privacy_settings.closest_distance) AS distance
FROM users
LEFT OUTER JOIN profile ON users.ID = profile.ID
LEFT OUTER JOIN location ON users.ID = location.ID
LEFT OUTER JOIN photos ON users.ID = photos.ID
LEFT OUTER JOIN privacy_settings ON users.ID = privacy_settings.ID
LEFT OUTER JOIN
(
select ID,
latitude,
longitude,
(SELECT latitude FROM location WHERE (ID = ':session.UserID')) AS mylatitude,
(SELECT longitude FROM location WHERE (ID = ':session.UserID')) AS mylongitude,
((SELECT ST_Distance_Sphere(point(longitude, latitude),point(mylongitude, mylatitude)) * .000621371192)) AS distancecalc
from location
) as distance_calculation ON users.ID = distance_calculation.ID
WHERE ((user_group = "commercial_business") OR (user_group = "commercial_user") OR (user_group = "premium_user") OR (user_group = "user") OR (user_group = "VIP_user")) AND (profile.complete = 1) AND (photos.photo1_approval = 1) AND (location.complete = 1) AND (users.user_active = 1) AND (users.searchable = 1) AND (users.ID <> ':session.UserID')
ORDER BY distance ASC
