Merge two HasMany Relations

Hi all.

In my Wallet Model I have :

public function sourceTransactions()
{
    return $this->hasMany(Transaction::class,'source_wallet_id');
}

public function targetTransactions()
{
    return $this->hasMany(Transaction::class,'target_wallet_id');
}

  public function myTransactions()
{
    return $this->sourceTransactions()->union($this->targetTransactions()->toBase());
}

so the query will be this :

(select * from `transactions` where `transactions`.`source_wallet_id` = ? and `transactions`.`source_wallet_id` is not null) union (select * from `transactions` where `transactions`.`target_wallet_id` = ? and `transactions`.`target_wallet_id` is not null)

It works perfectly. Event the paginator is working well.

The problem is when adding where close :

$wallet->myTransactions()->where('id','>',20);

It applied only with the first relation !! so the SQL will be :

(select * from `transactions` where `transactions`.`source_wallet_id` = ? and `transactions`.`source_wallet_id` is not null and `id` > ?) union (select * from `transactions` where `transactions`.`target_wallet_id` = ? and `transactions`.`target_wallet_id` is not null)

How to make the where close applied with all relations ??

muathye

4 years ago

Best Answer

Level 41

Hi @alnouirah The UNION operator is used to combine the [result-set] of two or more SELECT statements.

Your UNION operator should follow the following conditions:

Every SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in every SELECT statement must also be in the same order

You are trying to add union in your model which will work on first collection and it will not work in your case even if you use MySQL statement because you are looking for multiple results.

You may use union after get() like following:

$source = $wallet->sourceTransactions();
$my_transaction = $wallet->targetTransactions()->union($source)->get();

dd($my_transaction);

Ref: SQL UNION Operator

2 likes

alnouirah

4 years ago

Level 2

@muathye I get it here :

The UNION operator is used to combine the [result-set] of two or more SELECT statements.

So, The right way is to filter them separately like :

public function sourceTransactions($id = null)
{
    return $this->hasMany(Transaction::class,'source_wallet_id')
->when($id,fn($query,$id)=>$query->where('id','>',$id));
}

public function targetTransactions($id = null)
{
    return $this->hasMany(Transaction::class,'target_wallet_id')
when($id,fn($query,$id)=>$query->where('id','>',$id));
}

  public function myTransactions($id = null)
{
    return $this->sourceTransactions($id)->union($this->targetTransactions($id)->toBase());
}

The SQL will be like this:

(select * from `transactions` where `transactions`.`source_wallet_id` = ? and `transactions`.`source_wallet_id` is not null and `id` > ?) union (select * from `transactions` where `transactions`.`target_wallet_id` = ? and `transactions`.`target_wallet_id` is not null and `id` > ?)

Thanks a lot for your help :).

2 likes

haritsinh.gohil

1 year ago

Level 5

Hey @alnouirah, it's little bit late, but I have faced the same problem as yours, and I have found a better way to do this; In your wallet model use merge method instead of union then you don't need to change your code for where condition and where method will also work as it should without any changes, see the code:

 public function sourceTransactions()
{
    return $this->hasMany(Transaction::class,'source_wallet_id');
}

public function targetTransactions()
{
    return $this->hasMany(Transaction::class,'target_wallet_id');
}

  public function myTransactions()
{
    return $this->sourceTransactions->merge($this->targetTransactions);
}

Just use merge and use relationship as variable instead of calling it as function, i.e. write as $this->sourceTransactions and you are good to go, and all methods like where will also work on this merged relationship.

Please or to participate in this conversation.