0

Trying to figure out how to fetch two related models(obviously united) of my Many To Many(Polymorphic) relationship.

What we have:

  • 3 models: Bucket, Template and DesignPack.
  • Bucket has Many-To-Many(Polymorphic) relationship with Template and DesignPack(It means we have pivot table bucketables).In essence Bucket can have(be related with) both: Template and DesignPack.
  • Laravel 6.*

What I want to get:

  • I want to get a Bucket templates and design packs united in one collection and paginated!

Please check one of the solutions I've tried: 

$templates = Bucket::find($bucket_id)->templates()->select(['id', 'file_name as name', 'size', 'preview']);        
$design_packs = Bucket::find($bucket_id)->dps()->select(['id', 'name', 'size', 'preview']);

$all = $templates ->union($design_packs )->paginate(10);

Unfortunately that solution throws me the error(thought I checked what each request returns and it returns the same fields, not different):

"SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: (select `id`, `size`, `preview`, `bucketables`.`bucket_id` as `pivot_bucket_id`, `bucketables`.`bucketable_id` as `pivot_bucketable_id`, `bucketables`.`bucketable_type` as `pivot_bucketable_type` from `design_packs` inner join `bucketables` on `design_packs`.`id` = `bucketables`.`bucketable_id` where `bucketables`.`bucket_id` = 3 and `bucketables`.`bucketable_type` = App\DesignPack and `design_packs`.`deleted_at` is null) union (select `id`, `size`, `preview` from `templates` inner join `bucketables` on `templates`.`id` = `bucketables`.`bucketable_id` where `bucketables`.`bucket_id` = 3 and `bucketables`.`bucketable_type` = App\Template and `templates`.`deleted_at` is null))"

Are there any different way to get what I want? May be examples, documentation links or any helpful ideas?

Will be so grateful guys for any help! Thank you!

Taras Chernata
  • 371
  • 8
  • 17

1 Answers1

1

You can pass closure to queries:

$templates = Bucket::whereHas('templates', function($query) use $bucket_id {
       $query->where('bucket_id', $bucket_id);
    })->get();

$designPacks = Bucket::whereHas('dps', function($query) use $bucket_id {
       $query->where('bucket_id', $bucket_id);
    })->get();

then merge 2 eloquent collections:

$mergedCollections = $templates->merge($designPacks);

now you have a collection of both results, you can select specific fields, limit the results or etc. you may want take a look at Laravel collection helpers.

also if you insist to use the union, you may want to take a look at this treat: The used SELECT statements have a different number of columns (REDUX!!)

Danial Panah
  • 111
  • 3
  • Hey Danial! Thank you very much I've seen your example but it's merged collections, not paginated. It means it will take all the templates and design pack which can load server too much in the long run. So that's no correct approach mate :) See you second idea - it was really helpful, will check it out more deeply soon. Thanks – Taras Chernata Mar 17 '20 at 20:49
  • Sure mate. No of course we can't paginate on collections as it's for eloquent collections and queries. But if you want paginate on a merged collection you may want take a look at this: https://stackoverflow.com/questions/30420505/how-can-i-paginate-a-merged-collection-in-laravel-5 – Danial Panah Mar 17 '20 at 20:59
  • 1
    Thanks bro! but it's the same actually, we still get all possible database rows and then paginate them. Although we return paginated rows to our front-end but the fact we get all the rows from database is not still good. It loads MySQL server – Taras Chernata Mar 18 '20 at 06:58
  • Unfortunately you can't. Pagination manipulates the query as it is based on the Eloquent ORM. You can manually create pagination based on a collection. Have a look at https://laravel.com/docs/6.x/pagination#manually-creating-a-paginator – Dimitri Mostrey Mar 18 '20 at 10:28
  • Sure, no problem bro, did you solved it as you needed ? – Danial Panah Mar 18 '20 at 13:16
  • 1
    for paginate collection see this : https://stackoverflow.com/a/56142349/6901246 – Rohallah Hatami Dec 28 '20 at 14:47