php - Laravel 查询生成器 - 使用特殊数据类型进行查询

Question

我有一张这样的 table -

因此，我有一个用于 user_location 的特殊数据类型 (POINT)。

RAW选择查询是这样的-

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();