union查询到laravel雄辩查询

cbjzeqam 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(348)

需要帮助将此查询转换为laravel雄辩的查询吗

select `pin`.*,`pin_reviews`.`review`,( 3959 * acos( cos( radians('27.788870') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('72.978920') ) + sin( radians('27.788870') ) * sin( radians( lat ) ) ) ) AS distance,COUNT(`pin_reviews`.`pin_id`) as `totalUserRating`,SUM(`pin_reviews`.`rating`) / (COUNT(`pin_reviews`.rating * 5)) as `rating`,`ads`.`product_name`,`ads`.`id` as 'adId',`ads`.`notes`,`ads`.`add_status`,`ads`.`description` as 'adsDescription',`c`.`name` as 'catName',`c`.`id` as 'catId',`sc`.`id` as 'subcatId', `sc`.`name` as 'subName',`ssc`.`id` as 'SubsubId',`ssc`.`name` as 'subsubName' from ( 

  ( SELECT
        *
    FROM
        pin WHERE `subscription_plan` = 'Premium' LIMIT 2)
    UNION 
       ( SELECT
            *
        FROM
            pin WHERE `subscription_plan` = 'Platinum' LIMIT 1)
    UNION
        (SELECT
            *
            FROM pin WHERE `subscription_plan` = 'Free' LIMIT 10)
)

 AS pin
left join `pin_reviews` on `pin_reviews`.`pin_id` = `pin`.`id` left 
join `ads` on `ads`.`pin_id` = `pin`.`id` left join `category` as `c` 
on `c`.`id` = `ads`.`category_id` left join `category` as `sc` on 
`sc`.`id` = `ads`.`sub_category_id` left join `category` as `ssc` on 
`ssc`.`id` = `ads`.`sub_sub_category` where `pin`.`status` = 1 group 
by 
`pin`.`id` having `distance` < 380 order by `distance` asc limit 15

mysql php laravel eloquent laravel-5.2

来源：https://stackoverflow.com/questions/50267222/union-query-to-laravel-eloquent-query

2条答案

按热度按时间

hzbexzde1#

拉威尔雄辩的支持 union() 在这种情况下，进行另一个查询，但在本例中，将所有查询放在一个 DB::selectRaw() https://laravel.com/docs/5.6/queries#unions
https://laravel.com/docs/5.6/queries#raw-表达式

赞(0）回复(0）举报 2021-06-25

c6ubokkw2#

$pinData =  Pin::select(
        DB::raw("`pin`.*,`pin_reviews`.`review`,( 3959 * acos( cos( 
    radians('$lat') ) * cos( radians( lat ) ) * cos( radians( lng ) - 
    radians('$lng') ) + sin( radians('$lat') ) * sin( radians( lat ) ) 
    ) ) 
  AS distance,COUNT(`pin_reviews`.`pin_id`) as
`totalUserRating`,SUM(`pin_reviews`.`rating`) / 
(COUNT(`pin_reviews`.rating * 5)) as 
`rating`,`ads`.`product_name`,`ads`.`id` as 
'adId',`ads`.`notes`,`ads`.`add_status`,`ads`.`description` as 
'adsDescription',`c`.`name` as 'catName',`c`.`id` as 'catId',`sc`.`id` 
as 'subcatId', `sc`.`name` as 'subName',`ssc`.`id` as 
'SubsubId',`ssc`.`name` as 'subsubName' " ));

$pinData->LeftJoin('pin_reviews','pin_reviews.pin_id','=', 
'pin.id');
    $pinData->LeftJoin('ads','ads.pin_id','=', 'pin.id');
    $pinData->LeftJoin('category as c','c.id','=', 'ads.category_id');
    $pinData->LeftJoin('category as sc','sc.id','=', 
'ads.sub_category_id');
    $pinData->LeftJoin('category as ssc','ssc.id','=', 
'ads.sub_sub_category');
    //$pinData->where('pin.status',1);
    $pinData->groupBy('pin.id');
    $pinData->Having('distance','<',1870);
$qcon    = '';
    // if search keyword 
    if($search!='')
    {
        $qcon.= " AND (pin.pin_name LIKE '%".$search."%'";
        $qcon.= " OR pin.name_on_map LIKE '%".$search."%'";
        $qcon.= " OR pin.description LIKE '%".$search."%'";
     }
    if($adStatus!='')
    {
            $qcon.= " AND ads.add_status='". $adStatus."' ";
    }

    $pinData->whereRaw('( 1 '.$qcon.')'); 
    $pinData->OrderBy('distance');
    $pinData->limit($search_settings->pins_appear_limit);
    $pins   = $pinData->get();

赞(0）回复(0）举报 2021-06-25

我来回答

union查询到laravel雄辩查询

2条答案

相关问题

热门标签

最新问答