我有一个查询,该查询将一些元数据连接到用户。
SELECT
users.*,
gender.meta_value AS `gender`,
sexual_orientation.meta_value AS `sexual_orientation`,
relationship_status.meta_value AS `relationship_status`,
interest_1.meta_value AS `interest_1`,
interest_2.meta_value AS `interest_2`,
interest_3.meta_value AS `interest_3`,
interest_4.meta_value AS `interest_4`,
interest_5.meta_value AS `interest_5`,
interest_6.meta_value AS `interest_6`,
address.address_line_1,
address.address_line_2,
address.town,
address.county,
address.postcode,
address.country,
address.longitude,
address.latitude
FROM
`users`
JOIN
`storage_varchars` AS `gender`
ON
gender.user_id = users.id AND gender.meta_name = 'gender'
JOIN
`storage_varchars` AS `sexual_orientation`
ON
sexual_orientation.user_id = users.id AND sexual_orientation.meta_name = 'sexual_orientation'
JOIN
`storage_varchars` AS `relationship_status`
ON
relationship_status.user_id = users.id AND relationship_status.meta_name = 'relationship_status'
JOIN
`storage_varchars` AS `interest_1`
ON
interest_1.user_id = users.id AND interest_1.meta_name = 'interest_1'
JOIN
`storage_varchars` AS `interest_2`
ON
interest_2.user_id = users.id AND interest_2.meta_name = 'interest_2'
JOIN
`storage_varchars` AS `interest_3`
ON
interest_3.user_id = users.id AND interest_3.meta_name = 'interest_3'
JOIN
`storage_varchars` AS `interest_4`
ON
interest_4.user_id = users.id AND interest_4.meta_name = 'interest_4'
JOIN
`storage_varchars` AS `interest_5`
ON
interest_5.user_id = users.id AND interest_5.meta_name = 'interest_5'
JOIN
`storage_varchars` AS `interest_6`
ON
interest_6.user_id = users.id AND interest_6.meta_name = 'interest_6'
JOIN
`payments` AS `address`
ON
address.user_id = users.id
我现在想使用Haversine公式使用它来搜索最近的距离。
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;
如何合并为两个?
问候
最佳答案
好的,我找到了一种方法,但是有人可以告诉我这是否正确吗?
$usersLatitude = '53.1765254';
$usersLongitude = '-1.1954137';
$distance = 20;
$query = "
SELECT main.* FROM
(
SELECT
users.*,
gender.meta_value AS `gender`,
sexual_orientation.meta_value AS `sexual_orientation`,
relationship_status.meta_value AS `relationship_status`,
interest_1.meta_value AS `interest_1`,
interest_2.meta_value AS `interest_2`,
interest_3.meta_value AS `interest_3`,
interest_4.meta_value AS `interest_4`,
interest_5.meta_value AS `interest_5`,
interest_6.meta_value AS `interest_6`,
address.address_line_1,
address.address_line_2,
address.town,
address.county,
address.postcode,
address.country,
address.longitude,
address.latitude,
( 3959 * acos( cos( radians( {$usersLatitude} ) ) * cos( radians( address.latitude ) ) * cos( radians( address.longitude ) - radians( {$usersLongitude} ) ) + sin( radians( {$usersLatitude} ) ) * sin( radians( address.latitude ) ) ) ) AS distance
FROM
`users`
JOIN
`storage_varchars` AS `gender`
ON
gender.user_id = users.id AND gender.meta_name = 'gender'
JOIN
`storage_varchars` AS `sexual_orientation`
ON
sexual_orientation.user_id = users.id AND sexual_orientation.meta_name = 'sexual_orientation'
JOIN
`storage_varchars` AS `relationship_status`
ON
relationship_status.user_id = users.id AND relationship_status.meta_name = 'relationship_status'
JOIN
`storage_varchars` AS `interest_1`
ON
interest_1.user_id = users.id AND interest_1.meta_name = 'interest_1'
JOIN
`storage_varchars` AS `interest_2`
ON
interest_2.user_id = users.id AND interest_2.meta_name = 'interest_2'
JOIN
`storage_varchars` AS `interest_3`
ON
interest_3.user_id = users.id AND interest_3.meta_name = 'interest_3'
JOIN
`storage_varchars` AS `interest_4`
ON
interest_4.user_id = users.id AND interest_4.meta_name = 'interest_4'
JOIN
`storage_varchars` AS `interest_5`
ON
interest_5.user_id = users.id AND interest_5.meta_name = 'interest_5'
JOIN
`storage_varchars` AS `interest_6`
ON
interest_6.user_id = users.id AND interest_6.meta_name = 'interest_6'
JOIN
`payments` AS `address`
ON
address.user_id = users.id
) AS `main`
WHERE
`main`.distance < {$distance}
ORDER BY
`main`.distance
";