Support the ongoing development of Laravel.io →
Laravel Eloquent Database
Last updated 2 years ago.
0

I'll try to explain better:

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

$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 LINE 5: id

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...

Last updated 7 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

marymvlg28 marymvlg28 Joined 19 Jan 2015

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.