How to write an SQL with custom Join query?

I need a custom join query for elgg_get_entities(). Following is the most simplified version of my code. But I am getting sql error when executing this. 

<?php
    $options['type'] = 'user';
    $options['joins'][] = "JOIN (SELECT guid, 
                                    (6371 * acos( cos( radians(8.504211861184023) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(76.95055328511964) ) + sin( radians(8.504211861184023) ) * sin( radians( latitude ) ) ) ) 
                                AS distance 
                                FROM elgg_entity_geo_data 
                                HAVING distance < 20
                            ) egd ON egd.guid = e.guid";
    echo elgg_list_entities($options);
?>                            

 

This is the error report

An exception occurred while executing 'SELECT DISTINCT e.* FROM elgg_entities e INNER JOIN elgg_(SELECT guid, (6371 * acos( cos( radians(8.504211861184023) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(76.95055328511964) ) + sin( radians(8.504211861184023) ) * sin( radians( latitude ) ) ) ) AS distance FROM elgg_entity_geo_data HAVING distance < 20) egd ON egd.guid = e.guid WHERE (e.enabled = ?) AND (e.type = ?) ORDER BY e.time_created desc, e.guid desc LIMIT 10' with params ["yes", "user"]:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(SELECT guid, (6371 * acos( cos( radians(8.504211861184023) ) * cos( radians(...' at line 1

Log at time 2020-07-31T00:49:38+05:30 may have more data.

 

SELECT DISTINCT e.* FROM elgg_entities e INNER JOIN elgg_(SELECT guid, (6371 * acos( cos( radians(8.504211861184023) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(76.95055328511964) ) + sin( radians(8.504211861184023) ) * sin( radians( latitude ) ) ) ) AS distance FROM elgg_entity_geo_data HAVING distance < 20) egd ON egd.guid = e.guid WHERE (e.enabled = :qb1) AND (e.type = :qb2) ORDER BY e.time_created desc, e.guid desc LIMIT 10
array (
  ':qb1' => 'yes',
  ':qb2' => 'user',
)


I have a custom table for geodata. If I run following SQL via phpMyadmin, its returning the correct result. 

SELECT guid, 
                                    (6371 * acos( cos( radians(8.504211861184023) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(76.95055328511964) ) + sin( radians(8.504211861184023) ) * sin( radians( latitude ) ) ) ) 
                                AS distance 
                                FROM elgg_entity_geo_data 
                                HAVING distance < 20

Can someone help me in finding where I am going wrong?

  • I see a stray 'elgg_' after your INNER JOIN

    INNER JOIN elgg_(

    That is causing the invalid syntax issue.

    The joins array in elgg_get_entities assumes you are joining an existing table (and not a query). You might be able to do things via a custom where (something like 'e.guid IN (your subquery)' )