在选择查询cakephp中选择 [英] Select in select query cakephp
本文介绍了在选择查询cakephp中选择的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有2个表
1. feeds => id，name
2. feed_locations = id，feed_id，latitude，longitude，location_name
这是我的cakephp代码
$ radious ='10';
$ this-> paginate = array（
'joins'=> array（
array（'table'=>'feed_locations'，
'alias'=>'FeedLocation' ，
'type'=>'LEFT'，
'conditions'=> array（
'FeedLocation.feed_id = Feed.id'，
）
）
），
'limit'=> 10，
'fields'=> array（'id'，'name'，'（3959 * acos '））* cos（radians（FeedLocation.latitude））* cos（radians（FeedLocation.longitude） - radians（'。$ lng。'））+ sin（radians（'。$ lat。 （FeedLocation.latitude））））AS'distance`'），
'recursive'=> -1，
'order'=>'distance ASC'，
'group'=> 'Feed.id HAVING distance<'。$ radious
）;
它输出查询为
SELECT
`Feed`.`id`，
`Feed`.`name`，
（3959 * acos （cos（弧度（40.7127837））* cos（弧度（FeedLocation.latitude））* cos（弧度（FeedLocation.longitude） - 弧度（-74.00594130000002））+ sin ））））AS`distance`，
（从feed_locations中选择COUNT（id）WHERE feed_id =`Feed`.`id`）AS`location_count` FROM`feeds` AS`Feed`
LEFT JOIN `feed_locations` AS`FeedLocation` ON（`FeedLocation`.`feed_id`=`Feed`.`id`）
GROUP BY`Feed`.`id` HAVING distance< 10
ORDER BY`distance` ASC
我的查询正常工作， / p>
像一个Feed有10个位置，如1m，2m，3m，4m，5m，10m，100m的距离。我想找到5米距离所有饲料然后它的工作，但它显示我，这个饲料有5米。距离我，但结果应该是1m的距离。
我想用cakephp语法实现这个mysql查询
SELECT
a.id ，
a.name，
a.distance，
a.location_count
FROM
（SELECT
`Feed`.`id`，
`Feed`.`name`，
（3959 * acos（cos（radians（40.7127837））*
cos（radians（FeedLocation.latitude））*
cos（radians（FeedLocation。经度） -
弧度（-74.00594130000002））+ sin（弧度（40.7127837））*
sin（radians（FeedLocation.latitude））））AS`distance`，
（选择COUNT id'
FROM feed_locations
WHERE feed_id =`Feed`.`id`）AS`location_count`
FROM`feeds` AS`Feed`
LEFT JOIN`feed_locations` AS` FeedLocation` ON（`FeedLocation`.`feed_id` =`Feed`.`id`）
ORDER BY`distance` ASC）as a
GROUP BY a.id HAVING distance< 10;
此查询给出了精确的结果。请告诉我如何在cakephp中使用此查询
解决方案
我已尝试过此代码
function paginate（$ conditions，$ fields，$ order，$ limit，$ page，$ recursive，$ extra）{
$ db = $ this - > getDataSource（）;
$ modelName = $ this-> alias;
$ result = $ db-> fetchAll（
'SELECT
'。$ modelName。'。id，
'。$ modelName。'。name，
'。$ modelName。'。distance，
'。$ modelName。'。location_count
FROM
（SELECT
`Feed`.`id`，
`Feed `.`name`，
（3959 * acos（cos（radians（40.7127837））*
cos（radians（FeedLocation.latitude））*
cos（radians（FeedLocation.longitude）
radians（-74.00594130000002））+ sin（弧度（40.7127837））*
sin（radians（FeedLocation.latitude））））AS`distance`，
（选择COUNT b $ b FROM feed_locations
WHERE feed_id =`Feed`.`id`）AS`location_count`
从`feeds` AS`Feed`
LEFT JOIN`feed_locations` AS`FeedLocation` ON （`FeedLocation`.`feed_id` =`Feed`.`id`）
ORDER BY`distance` ASC）AS'。$ modelName。'
GROUP BY'。$ modelName。'。id HAVING距离< 10;'
）;
return $ result;
}
我在模型中管理了这个查询。我在模型中创建一个函数paginate并提及这个查询。那么它会自动分页数据。
I have 2 table
1. feeds => id,name 2. feed_locations => id, feed_id, latitude,longitude,location_name
This is my cakephp code
$radious = '10';
$this->paginate = array(
'joins' => array(
array('table' => 'feed_locations',
'alias' => 'FeedLocation',
'type' => 'LEFT',
'conditions' => array(
'FeedLocation.feed_id = Feed.id',
)
)
),
'limit' => 10,
'fields' => array('id','name','(3959 * acos (cos ( radians('.$lat.') ) * cos( radians( FeedLocation.latitude ) ) * cos( radians( FeedLocation.longitude ) - radians('.$lng.') ) + sin ( radians('.$lat.') ) * sin( radians( FeedLocation.latitude )))) AS `distance`'),
'recursive' => -1,
'order' => 'distance ASC',
'group' => 'Feed.id HAVING distance <'.$radious
);
It give me output query as
SELECT
`Feed`.`id`,
`Feed`.`name`,
(3959 * acos (cos ( radians(40.7127837) ) * cos( radians( FeedLocation.latitude ) ) * cos( radians( FeedLocation.longitude ) - radians(-74.00594130000002) ) + sin ( radians(40.7127837) ) * sin( radians( FeedLocation.latitude )))) AS `distance`,
(Select COUNT(id) FROM feed_locations WHERE feed_id = `Feed`.`id`) AS `location_count` FROM `feeds` AS `Feed`
LEFT JOIN `feed_locations` AS `FeedLocation` ON (`FeedLocation`.`feed_id` = `Feed`.`id`)
GROUP BY `Feed`.`id` HAVING distance < 10
ORDER BY `distance` ASC
My query is working but issue it that :
Like if a single feed have 10 location like 1m,2m,3m,4m,5m,10m,100m distance. and i want to find 5m distance all feed then it works but it shows me that this feed have 5m. distance from me but result should be 1m distance.
I want to implement this mysql query in cakephp syntax
SELECT
a.id,
a.name,
a.distance,
a.location_count
FROM
(SELECT
`Feed`.`id`,
`Feed`.`name`,
(3959 * acos (cos ( radians(40.7127837) ) *
cos( radians( FeedLocation.latitude ) ) *
cos( radians( FeedLocation.longitude ) -
radians(-74.00594130000002) ) + sin ( radians(40.7127837) ) *
sin( radians( FeedLocation.latitude )))) AS `distance`,
(Select COUNT(id)
FROM feed_locations
WHERE feed_id = `Feed`.`id`) AS `location_count`
FROM `feeds` AS `Feed`
LEFT JOIN `feed_locations` AS `FeedLocation` ON (`FeedLocation`.`feed_id` = `Feed`.`id`)
ORDER BY `distance` ASC) AS a
GROUP BY a.id HAVING distance < 10;
This query give me exact result. please let me know how can i use this query in cakephp
解决方案
I have tried this code
function paginate($conditions, $fields, $order, $limit, $page, $recursive, $extra){
$db = $this->getDataSource();
$modelName = $this->alias;
$result = $db->fetchAll(
'SELECT
'.$modelName.'.id,
'.$modelName.'.name,
'.$modelName.'.distance,
'.$modelName.'.location_count
FROM
(SELECT
`Feed`.`id`,
`Feed`.`name`,
(3959 * acos (cos ( radians(40.7127837) ) *
cos( radians( FeedLocation.latitude ) ) *
cos( radians( FeedLocation.longitude ) -
radians(-74.00594130000002) ) + sin ( radians(40.7127837) ) *
sin( radians( FeedLocation.latitude )))) AS `distance`,
(Select COUNT(id)
FROM feed_locations
WHERE feed_id = `Feed`.`id`) AS `location_count`
FROM `feeds` AS `Feed`
LEFT JOIN `feed_locations` AS `FeedLocation` ON (`FeedLocation`.`feed_id` = `Feed`.`id`)
ORDER BY `distance` ASC) AS '.$modelName.'
GROUP BY '.$modelName.'.id HAVING distance < 10;'
);
return $result;
}
It works perfectly for me.
I have manage this query in model . i create a function paginate in model and mention this query . then it automatic paginate the data.
这篇关于在选择查询cakephp中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
