Laravel查询生成器-特殊数据类型的查询

Question

我有一张这样的桌子-

因此，我有一个特殊的数据类型（点）用于用户位置。

原始select查询如下所示-

SELECT
  id,
  full_name,
  website,
  X(user_location) AS "latitude",
  Y(user_location) AS "longitude",
  (
    GLength(
      LineStringFromWKB(
        LineString(
          user_location, 
          GeomFromText('POINT(51.5177 -0.0968)')
        )
      )
    )
  )
  AS distance
FROM users
  ORDER BY distance ASC;

结果是-

我想在带有查询生成器的Laravel中使用它，以便（51.5177-0.0968）这2个点可以来自用户输入。

我所做的是-

DB::table('users')
   ->select(
             id,
             full_name,
             website,
             DB::raw('X(user_location) AS "latitude"'),
             DB::raw('Y(user_location) AS "longitude"'),
             DB::raw('(
                        GLength(
                          LineStringFromWKB(
                            LineString(
                              user_location, 
                              GeomFromText('POINT(51.5177 -0.0968)')
                            )
                          )
                        )
                      )
                      AS distance')
             )
  ->orderBy('distance', 'asc')
  ->get();

但它不起作用。

有人能帮忙吗？

Answer 1

我觉得你很接近了。试试这个：

DB::table('users')
    ->select(
        'id',
        'full_name',
        'website',
        DB::raw('X(user_location) as latitude'),
        DB::raw('Y(user_location) as longitude'),
        DB::raw('(
                GLength(
                  LineStringFromWKB(
                    LineString(
                      user_location,
                      GeomFromText(POINT(51.5177 - 0.0968))
                    )
                  )
                )
              )
              as distance')
    )
    ->where('status', '<>', 1)
    ->orderBy('distance', 'asc')
    ->get();