API action list-devices-near is not quite working - it returns only the same sensor about air-quality-web HOT 8 CLOSED

On Fri, 1 May 2020, 23:20 Starbeamrainbowlabs, ***@***.***> wrote: Ah, I see @BNNorman <https://github.com/BNNorman>. If I change the query to this though: SELECTdevices.device_id AS id,devices.device_name AS name,devices.device_latitude AS latitude,devices.device_longitude AS longitude, ST_DISTANCE_SPHERE(POINT(53.750, -0.395), devices.lat_lon) AS distance_calc, devices.last_seen AS last_seenFROM devicesRIGHT JOIN readings ON readings.device_id = devices.device_idWHERE devices.lat_lon IS NOT NULLORDER BY ST_DISTANCE_SPHERE(POINT(53.750, -0.395), devices.lat_lon)LIMIT 5; ...I still get the same result: +----+----------------+-------------+-------------+-------------------+---------------------+ | id | name | latitude | longitude | distance_calc | last_seen | +----+----------------+-------------+-------------+-------------------+---------------------+ | 36 | CHASW-24AD43-1 | 53.75642593 | -0.36844245 | 1886.588853560329 | 2020-05-01 22:09:46 | | 36 | CHASW-24AD43-1 | 53.75642593 | -0.36844245 | 1886.588853560329 | 2020-05-01 22:09:46 | | 36 | CHASW-24AD43-1 | 53.75642593 | -0.36844245 | 1886.588853560329 | 2020-05-01 22:09:46 | | 36 | CHASW-24AD43-1 | 53.75642593 | -0.36844245 | 1886.588853560329 | 2020-05-01 22:09:46 | | 36 | CHASW-24AD43-1 | 53.75642593 | -0.36844245 | 1886.588853560329 | 2020-05-01 22:09:46 | +----+----------------+-------------+-------------+-------------------+---------------------+ ...even though I'm specifying a right join explicitly. @bsimmo <https://github.com/bsimmo>: This is using a local development server that's running the latest from the dev branch. Hrm. You're saying that this is a regression and it worked before? Thanks for that commit id - that;s really helpful for narrowing down what's gone wrong. I'm pretty sure the problematic change is in a678a89 #diff-de7016366166fa0b532d03d624168e27R151-R176 <a678a89#diff-de7016366166fa0b532d03d624168e27R151-R176> . Thinking about it, I'm not even sure why we're joining there in the first place, since it would appear that the only time the readings table is referenced is in the join itself. If I do this instead: SELECTdevices.device_id AS id,devices.device_name AS name,devices.device_latitude AS latitude,devices.device_longitude AS longitude, ST_DISTANCE_SPHERE(POINT(53.750, -0.395), devices.lat_lon) AS distance_calc, devices.last_seen AS last_seenFROM devicesWHERE devices.lat_lon IS NOT NULLORDER BY ST_DISTANCE_SPHERE(POINT(53.750, -0.395), devices.lat_lon)LIMIT 5; ...then I correctly get this: +----+------------------+-------------+-------------+--------------------+---------------------+ | id | name | latitude | longitude | distance_calc | last_seen | +----+------------------+-------------+-------------+--------------------+---------------------+ | 36 | CHASW-24AD43-1 | 53.75642593 | -0.36844245 | 1886.588853560329 | 2020-05-01 22:15:46 | | 32 | CHASW-56AB6D-1 | 53.767262 | -0.403431 | 1997.8605170536607 | 2020-05-01 06:00:56 | | 51 | CL-AW15CH6N | 53.7432075 | -0.3586957 | 2503.8574249443122 | 2020-03-12 23:00:00 | | 35 | CHASR-6F3BD82C-1 | 53.75776 | -0.436268 | 2847.053372632466 | 2020-05-01 22:17:15 | | 48 | HCCSENSOR03 | 53.739378 | -0.34838408 | 3285.0856113502487 | 2020-05-01 22:16:51 | +----+------------------+-------------+-------------+--------------------+---------------------+ ...does this look right to everyone? Have I missed something? I can't remember what I was thinking at the time, and the commit message doesn't give anything away. I feel like I'm missing something. If this altered SQL query looks ok, I'll go ahead and patch the SQL query by removing the JOIN altogether. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#61 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADY5NXMO2AYPGHYNS56JCQDRPNDMTANCNFSM4MXAIDZQ> .