Getting documents based on IDs in external table

145 Posts

digitalime Reply #1, 17 years, 1 month ago

Hey there,

I am building a store finder in Evolution and have created a custom table that contains the latitudes and longitudes of all the stores. I have created a modx document for each store (so that template variables for each one can be edited easily).

Each one of my lat/long rows also contains a corresponding docid for the corresponding store.

Now I want to find the 10 stores nearest me, so I search the lat/long table which spits out the top 10 docids. The question is, how can I use this to output the data in the modx documents.

Can I use the getDocuments API? What’s the best option?

Any help would be mightily appreciated.

Thanks

5,888 Posts

Bruno17 Reply #2, 17 years, 1 month ago

The easy way is to use Ditto with the ditto-parameter

&documents=`[[getDocidsOfNearestStores_commaseperated]]`

or use

$params['documents']=$DocidsOfNearestStores_commaseperated;
$output=$modx->runSnippet('Ditto',$params);

in your snippet.

of course you can also use

$docs = $modx->getDocuments($DocidsOfNearestStores_asArray, 1, 0,$fields);

in case you need tvs you can get them with

$modx->getTemplateVarOutput($idnames= array (), $docid= "", $published= 1)

in a loop.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

145 Posts

digitalime Reply #3, 17 years, 1 month ago

That’s excellent Bruno. Thanks for you help. I even managed to set the placeholder [+distance+] so I can use it in the template chunk. My loop now looks like this

foreach($stores AS $store) {  
							


$params['documents']=$store['docid'].',';
$params['tpl']='listtpl';
$output=$modx->runSnippet('Ditto',$params);

$modx->setPlaceholder('distance',number_format($store['distance'],2));


echo $output;

Will shout if I come up against another wall.

Thanks again.

5,888 Posts

Bruno17 Reply #4, 17 years, 1 month ago

I think its better to collect first all datas into an array, then run ditto once after your loop:

<?php

$docids=array();
foreach($stores AS $store) {  

$modx->setPlaceholder($store['docid'].'.distance',number_format($store['distance'],2));
$docids[]=$store['docid'];

}

$params=array();
$params['documents']=implode(',',$docids);
$params['tpl']='listtpl';
return $modx->runSnippet('Ditto',$params);

?>

in your template:

[+[+id+].distance+]

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

145 Posts

digitalime Reply #5, 17 years, 1 month ago

Hmmm...very odd. Having done that, the sorting seems to a bit off.

This is the SQL function (which also takes care of the sorting)

<?php
$query = "SELECT *,
						(((acos(sin((".$latitude."*pi()/180)) * sin((`lat`*pi()/180))
						+cos((".$latitude."*pi()/180)) * cos((`lat`*pi()/180)) 
						* cos(((".$longitude."- `lng`)*pi()/180))))*180/pi())*60*1.1515) 
						as distance 
							FROM `markers`
							ORDER BY distance ASC
							LIMIT 0,10
					";
			$stores = $this->db->executeQuery($query);
			$stores = $stores['result'];			
			
			return $stores;
?>

But I can’t see how that is being overwritten by the Ditto call.

5,888 Posts

Bruno17 Reply #6, 17 years, 1 month ago

ditto-sorting is normally createdon. If you want your sorting as in &documents you need my docsort-extender.

See here:
http://modxcms.com/forums/index.php/topic,38711.msg233570.html#msg233570

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

145 Posts

digitalime Reply #7, 17 years, 1 month ago

Well I was hoping to bypass Ditto sorting altogether by using the SQL query above.

I want to order them by distance, with the nearest one at the top.

Another problem has arisen. It seems that the first distance calculation is skipped by the loop. Any ideas what this might be?

Thanks

5,888 Posts

Bruno17 Reply #8, 17 years, 1 month ago

Well I was hoping to bypass Ditto sorting altogether by using the SQL query above.

with my extender you cannot bypass dittos own sorting, but it resorts the documents as they came from parameter &documents.

It seems that the first distance calculation is skipped by the loop. Any ideas what this might be?

in your or in my solution? You can try to change the order of your codelines and set the placeholder before calling ditto:

foreach($stores AS $store) {  
							

$modx->setPlaceholder('distance',number_format($store['distance'],2));
$params['documents']=$store['docid'].',';
$params['tpl']='listtpl';
$output=$modx->runSnippet('Ditto',$params);




echo $output;

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

145 Posts

digitalime Reply #9, 17 years, 1 month ago

Changing the order of the code lines worked (I was trying my solution). Thanks.

However, I would much prefer to try your solution, as I tested them against each other:

Mine - 186 queries
Yours - 9 queries

No contest really.

Applied your customsort extender and everything seems to work great! Vielen dank für deine Hilfe.