Home > OS >  eloquent query with orderBy subquery
eloquent query with orderBy subquery

Time:05-05

How to convert the following query to eloquent

DB::select("SELECT fp.* 
FROM forum_posts fp 
ORDER BY (
    SELECT( CASE WHEN MAX(fa.created_at) >  fp.created_at THEN MAX(fr.created_at) ELSE fp.created_at END ) 
    FROM forum_answers fa
    WHERE fp.id_post = fa.id_post
)
DESC");

Just to exemplify what I'm trying to achieve

Post::orderBy(
  'SELECT( CASE WHEN MAX(fa.created_at) >  fp.created_at THEN MAX(fr.created_at) ELSE 
  fp.created_at END )', 'DESC'
)->get()

CodePudding user response:

@josei, Eloquent order method supports subqueries and there is a number of ways you can use to achieve the same result

Post::select('forum_posts.*')->orderByRaw('...')
// or 
Post::query()->select('forum_posts.*')->orderByDesc(
    Post::where() ...
)
// or 
Post::query()->select('forum_posts.*')->orderBy(DB::raw('...'))

Hope this helps, good luck!

  • Related