[TYPO3-mvc] Queries for a localized vendor search service

[Henjo Hoeksma]

On 2011-09-19 08:58:13 +0000, Matthias Nagl said:

> I am going to implement a localized search that shows a user the closest
> vendors for a special service.
> 
> To realize this I have a database table containing  vendors and their
> coordinates (latitude, longitude). The search results shall be presented
> ordered by distance. As the website only covers vendors in a rather small
> region it would be perfectly sufficiant to compute Euclidean distances.
> In MySQL syntax the query should look like this:
> SELECT ... ORDER BY SQRT(POW(lat - userlat, 2)*latweight + POW(lng - userlng,
> 2)*lngweight);
> in this query userlat and userlng would be the user's coordinates and
> latweight as well as lngweight the average distance between degrees of
> lat/longitude.
> 
> My Question:
> Is it possible zu create such an ordering using the extbase query object or is
> it at least possible to inject the ordering-part of SQL code into a query
> object without creating the whole with the $query->statement() method.
> 
> Cheers
> 
> Matthias

Hi Matthias,

this is how I solved it:

    public function findByDistance($lat, $lng, $distance, $group=NULL, 
$metric = 'km', $items = '10') {
	// $this->settings['search']['numberOfItems']
	if ($metric == 'km') {
	    $metric = '6371'; // Kilometres
	} else {
	    $metric = '3959'; // Miles
	}

	
	// Using the query statement is not an option. Unfortunately.
	$result = $GLOBALS['TYPO3_DB']->sql_query('
	     SELECT uid,( ' . $metric . ' * acos( cos( radians(' . $lat . ') ) 
* cos( radians( lat ) ) * cos( radians( lng ) - radians(' . $lng . ') ) 
+ sin( radians(' . $lat . ') ) * sin( radians( lat ) ) ) ) AS distance
	     FROM tt_address
	     HAVING distance < ' . $distance . '
	     ORDER BY distance LIMIT 0,' . $items);
	while ($row = mysql_fetch_assoc($result)) {
	    $uids[] = $row['uid'];
	}

	if (count($uids) > '0') {
	    $query = $this->createQuery();
	    if (empty($group)) {
		$addresses = $query->matching($query->in('uid', $uids))->execute();
	    } else {
		$groupRepository = 
$this->objectManager->get('Tx_Googlelocator_Domain_Repository_AddressGroupRepository'); 

		$group = $groupRepository->findOneByUid($group);
		$constraints[] = $query->in('uid', $uids);
		$constraints[] = $query->contains('addressgroup', $group);
		$addresses = $query->matching($query->logicalAnd($constraints))->execute();
		if (count($addresses) == '0')
		    $addresses = NULL;
	    }
	} else {
	    $addresses = NULL;
	}
	return $addresses;
    }