Define a relationship as a union

In several parts of my code I have for example these lines:

$user->movements()->get();
...
$user->movements()->where(...)->get();
...
$user->movements()->where(...)->select(... sum, count, avg ...)->get();
...

But now I'm facing an important change on the movements table structure. Now I need two tables with a similar structure (the data HAVE to be in separate tables), movements and ticket_movements.

These tables are consulted by two system, one of then needs the data to be separated and the other needs the data to be as in one table.

So, in one of the systems, I would like to define the relationship movements() as an union of movements and ticket_movements tables.

So, having the relationship movements defined as:

public function movements()
{
        $movements = $this->hasMany('App\Model\Movement')
            ->select(\DB::raw("
                id,
                user_id,
                movement_type_id,
                amount,
                description
            "));

        $tickets_movements = $this->hasMany('App\Model\TicketMovement')
            ->select(\DB::raw("
                id,
                user_id,
                movement_type_id,
                amount,
                description
            "));

        return $movements->union($tickets_movements->getQuery());
    }

If I do this:

$user->movements()
    ->whereIn('movement_type_id', [1, 2])
    ->select(\DB::raw('
        SUM(CASE WHEN movement_type_id = 1 THEN 1 ELSE 0 END) as credit,
        SUM(CASE WHEN movement_type_id = 2 THEN 1 ELSE 0 END) as debit
    '))
    ->first();

The query I get is:

SELECT 
SUM(CASE WHEN movement_type_id = 1 THEN 1 ELSE 0 END) as credit, 
SUM(CASE WHEN movement_type_id = 2 THEN 1 ELSE 0 END) as debit 
FROM "movements" 
WHERE "movements"."user_id" = 2 
AND "movements"."user_id" is not null 
AND "movement_type_id" in (1, 2)

UNION

SELECT id, user_id, movement_type_id, amount, description
FROM "ticket_movements" 
WHERE "ticket_movements"."user_id" = 2 
AND "ticket_movements"."user_id" is not null limit 1

Besides it's not the query I need, it give me an error because of the columns:

Syntax error: 7 ERROR: 
each UNION query must have the same number of columns

The query I need is something like this:

SELECT 
SUM(CASE WHEN movement_type_id = 1 THEN 1 ELSE 0 END) as credit, 
SUM(CASE WHEN movement_type_id = 2 THEN 1 ELSE 0 END) as debit 

FROM (

SELECT id, user_id, movement_type_id, amount, description
FROM "movements" 

UNION

SELECT id, user_id, movement_type_id, amount, description
FROM "ticket_movements" ) as movements

WHERE "movements"."user_id" = 2 
AND "movements"."movement_type_id" in (1, 2)

Without modifying each line where I do $user->movements()...

I don't know is that is possible...

via Chebli Mohamed