elgg_get_entities() and MySQL syntax

Hi,

Could you please help me figure this one out. 

This works as expected:

$params = array(

    'types' => $type,

    'subtypes' => $subtype,

    'owner_guids' => $owner,

    'container_guids' => $container,

    'limit' => $limit,

    'offset' => $offset,

'selects' => array("(((acos(sin(($latitude*pi()/180)) * sin((msv1.string*pi()/180))+cos(($latitude*pi()/180)) * cos((msv1.string*pi()/180)) * cos((($longitude - msv2.string)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance"),

'metadata_name_value_pairs' => array(

        array('name' => 'geo:lat', 'value' => '', 'operand' => "!="),

array('name' => 'geo:long', 'value' => '', 'operand' => "!=")

),

'order_by' => "distance ASC",

);

$entities = elgg_get_entities_from_metadata($params);

 

But, if I add the following 'wheres' I get an error - Unknown column 'distance' in 'where clause':

'wheres' => array("(distance > 5000) AND (distance < 7000)")

 

I assume the problem is with the order of sql implementation. How do I solve this? 

Thanks tons

  • msv2.string)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance ?
    does not correlate to the (yet unseen) generated sql query -
    if you dumped the query - you'll most likely see the bad fileld aint nowhere there.
    most likely needs to be more (properly) qualiifed -
    i.e.
    msv2.string is already a sql qualification for tbl as msv2's col string
    --> so to say 'distance' - which is named col is not == quite mellow-jello ;)
    try dropping that 'as distance' and coding the ordering predicate 'msv2.string asc'

     

    oopss that last line needs a little more thought

    gimme 10..

  • what a l-o-n-g calc !!

    Try ;-} maybe lucky
     ORDER BY 
        acos(
              sin(($latitude*pi()/180)) 
            * sin((msv1.string*pi()/180))
            + cos(($latitude*pi()/180)) 
            * cos((msv1.string*pi()/180)) 
            * cos((($longitude - msv2.string)*pi()/180))
     
    Also - because ordering is on calculated value -
    be warned - there will most likely be a performance hit.



  • Thanks, Dhrup. As I've mentioned the code works as is, the ordering is fine. My next step is to limit the results to particular distance margin. where clause doesn't seem to work, so I am looking for an alternative. 

    Your latter suggestion is what I'd written first, it worked, so I moved it to selects to be able to limit the results to just a specific to - from

  • it worked ??? lolz ;-) wild 1/2 %s`ed lazy sql !!! wheww & wow btw - i wud have initially split up that elongated code -- too much code sometimes many times leads to blind-sight.. limiting ? -- might need similar 'inline formula' for limiting -- lolz ;-P long long long lines of c-o-d-e tee hee !! have funz ! btw - is this code w.r.t HypARgZ-Maps| or similar ?

  • Thanks, Mr. So much help lolz wheww & wow :)

    Yup, the new hypeMaps with a super cool UI ;)

  • Kinda off topic but has anyone noticed tht Dhrup writes alot like a kid?  With all those funny text and stuff? LOL :D I think he spends too much time with those kids on FBFK!

    You still the man Dhrup! ;)

  • @flexx - kids are q. vewy wonderful people, too ;-) they make life worth the ride! btw - some of those fbfk`ers actually write now & then - mis bebés lindos!! get some of your own, man and let them eat yr brains out.. 2 ! ;-;oO;XP

  • lo0l! they are indeed a set to enjoy! :) i an see it.. Cheers mate!