如何在laravel /雄辩的MySQL 8中做通用表表达式？ [英] How to do common table expressions in laravel / eloquent for mysql 8?

问题描述

在laravel 5.7中执行CTE公共表表达式的正确方法是什么？

what is the proper way of doing CTE common table expressions in laravel 5.7?

请考虑以下sql：

WITH
SearchTerm AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY created_at) AS search_term_order
    , jobs.*
  FROM jobs
  WHERE description LIKE '%programmer%'
),
PremiumAdd AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY created_at DESC) AS premium_order
    , jobs.*
  FROM jobs
  WHERE is_premium = 1
),
FinalOrder AS (
  SELECT
    id
    , description
    , created_at
    , is_premium
    , MOD(search_term_order + 1, 2) + FLOOR((search_term_order + 1) / 2) * 3 - 2 AS position
  --  , MOD(search_term_order + 2, 3) + FLOOR((search_term_order + 2) / 3) * 4 - 3 AS position
  FROM SearchTerm
  UNION ALL
  SELECT
    id
    , description
    , created_at
    , is_premium
    , premium_order * 3
  --  , premium_order * 4
  FROM PremiumAdd
  ORDER BY position
)
SELECT
  id, description, created_at, is_premium FROM FinalOrder
;

推荐答案

Laravel不支持CTE。您可以使用原始查询并自行合并模型：

Laravel has no CTE support. You can use a raw query and hydrate the models yourself:

$rows = DB::select($sql, $bindings);

$jobs = Job::hydrate($rows);

我为常见的表表达式创建了一个包： https://github.com/staudenmeir/laravel-cte

I've created a package for common table expressions: https://github.com/staudenmeir/laravel-cte

class Job extends Model
{
    // Required in Laravel 5.5–5.7:
    // use \Staudenmeir\LaravelCte\Eloquent\QueriesExpressions;
}   

$jobs = Job::from('FinalOrder')
    ->withExpression('SearchTerm', 'SELECT ROW_NUMBER() [...]')
    ->withExpression('PremiumAdd', 'SELECT ROW_NUMBER() [...]')
    ->withExpression('FinalOrder', 'SELECT id, description [...]')
    ->select('id', 'description', 'created_at', 'is_premium')
    ->get();

这篇关于如何在laravel /雄辩的MySQL 8中做通用表表达式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！