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)' )

  • Thanks Jeroen. My Initial thought was also on that. 

    I am upgrading an elgg 2.0 site to 3.x version. But in the 2.0 version, this subquery works fine with the join clause. So do you think this needs to be reported as a bug?

  • Dear Sarath,

    it is not a bug. There have been changes in Elgg 3.0 that affect the way the joins should be used. You can read about those changes here: http://learn.elgg.org/en/stable/appendix/upgrade-notes/2.x-to-3.0.html#changes-in-elgg-get-entities-elgg-get-metadata-and-elgg-get-annotations-getter-functions

     

  • Hi Jeroen, thanks for the help. I got it working with the following changes as you advised.

        $options['wheres'][] = "e.guid IN (SELECT guid FROM {$dbPrefix}entity_geo_data 
                                            WHERE (6371 * acos( 
                                                                cos( radians(8.504211861184023) ) * 
                                                                cos( radians( latitude ) ) * 
                                                                cos( radians( longitude ) - radians(76.95055328511964) ) 
                                                                + sin( radians(8.504211861184023) ) * sin( radians( latitude ) ) 
                                                            ) 
                                                    ) < 20 
                                        )";

    But now I am unable to sort the results based on the distance. Any help will be appreciated.

  • Isn't that a similar (the same?) kind of query as given as an example at http://learn.elgg.org/en/stable/guides/search.html#custom-search-types for implementing a custom search? The example has been updated for Elgg 3 already and seems to include an order_by clause, too.

  • Amazing iionly. I missed that totally.. Thanks a lot for the help. 

  • Just one more question, will there be any performance benefit my moving geodata search alone to a custom table? 

    Along with geodata, I am using some "metadata_name_value_pairs" for additional filters. But because of the complexity of geodata search, I moved geosearch to a separate table.

    Just concerned about performance now.