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.
This is the error report
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.
Can someone help me in finding where I am going wrong?
info@elgg.org
Security issues should be reported to security@elgg.org!
©2014 the Elgg Foundation
Elgg is a registered trademark of Thematic Networks.
Cover image by RaĆ¼l Utrera is used under Creative Commons license.
Icons by Flaticon and FontAwesome.
- Jeroen Dalsem@jdalsem
Jeroen Dalsem - 0 likes
- Sarath@sarathsince85
Sarath - 0 likes
- Jeroen Dalsem@jdalsem
Jeroen Dalsem - 0 likes
- Sarath@sarathsince85
Sarath - 0 likes
- iionly@iionly
iionly - 0 likes
- Sarath@sarathsince85
Sarath - 0 likes
- Sarath@sarathsince85
Sarath - 0 likes
You must log in to post replies.I see a stray 'elgg_' after your INNER JOIN
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.
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.