I am am getting some challenge with a PostgreSQL query
Things I want is a listing of "Campaigns", purchased by distance, and inside a minimum distance from the location [in_lat, in_lng, in_radius] Now, I'm locating the length (with distance_in_km(...) ) two times. Once for ordering, once for checking when the campaign is at my preferred radius.
Also, It is possible that the campaign has multiple "Campaignreceiver" records (receivetype [EMAIL, WEBSITE, etc...])
And I wish to retrieve the campaign only once. hence the DISTINCT ON.
So now you ask ,, how do i get sometime like "WHERE distance < in_radius" to operate. My prediction is adding an organization BY and Getting however i did some tests and did not get would be to work.
ANY Assistance Is Greatly APPRECIATED!!!
SELECT DISTINCT ON (c.campaign_uuid, distance) c.campaign_uuid, prl.lat, prl.long, distance_in_km(prl.lat, prl.long, in_lat, in_lng) AS distance FROM usercampaignrelations AS ucr LEFT JOIN campaignreceivers AS cr ON ucr.usercampaign_uuid = cr.usercampaign_uuid LEFT JOIN campaigntargetgrouprelations AS ctg ON cr.campaigntargetgroup_uuid = ctg.campaigntargetgroup_uuid LEFT JOIN campaigns AS c ON ucr.campaign_uuid = c.campaign_uuid LEFT JOIN companycampaignrelations AS cc ON c.campaign_uuid = cc.campaign_uuid LEFT JOIN pointradiuslocations AS prl ON c.location_uuid = prl.location_uuid WHERE ucr.user_uuid = in_user_uuid AND (cr.status = 'SENT' OR cr.status = 'RETRIEVED') AND distance_in_km(prl.lat, prl.long, in_latitude, in_longtitude) < in_radius ORDER BY distance LIMIT in_limit
AFAIK, distance_in_km() is going to be known as only one time (per row), as lengthy while you utilize it with similar arguments, thus WHERE and ORDER BY won't review it again.