Laravel 4 - Union + orederBy results with the wrong query

Question

I try to pull data with union from 2 tables and order both of them by "created_at" DESC. yet for some reason writing the ->orderBy results with the wrong query - where I have the created_at within the first query and not outside of them.

code:

$recipes = DB::table("recipes")->select("id", "title", "user_id", "description", "created_at") ->where("user_id", "=", $id);
    $posts = DB::table("posts")->select("id", "title", "user_id", "content", "created_at")
                    ->where("user_id", "=", $id);

    $items = $posts->union($recipes);
    $items = $items->orderBy("created_at", "DESC")->get();

The query i get:

(select id, title, user_id, content, created_at from posts where user_id = '102' order by created_at desc) union (select id, title, user_id, description, created_at from recipes where user_id = '102')

The query i expected to get:

(select id, title, user_id, content, created_at from posts where user_id = '102') union (select id, title, user_id, description, created_at from recipes where user_id = '102') order by created_at desc

Any idea why?

My laravel version is: V4.2.8

related topics: https://github.com/laravel/framework/pull/3901

Thanks for the help!

Borna Borna · Accepted Answer · 2016-08-24T04:24:04

in controller

use DB;

in inside of controller function

$items=DB::select('select id, title, user_id, content, created_at from posts where user_id =? union  select id, title, user_id, description, created_at from recipes where user_id = ? order_by created_at desc',[$id,$id]);

Laravel 4 - Union + orederBy results with the wrong query

1 Answers