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
