Laravel Polymorphic Many-to-Many Performance: Query Pivot Table

nice tip, thanks.

Wherehas is usually an indicator to me that a query needs to be optimized.

I guess, in your solution there are to much repeatiations in the column "assignable_type". It's a more cleaner design If there is a separate assignment-table for each type. to merge these different assignment-tables back into one normalized presentation (if needed) you can simply create a db-view "assignables". In this view can declared how the different assignment-tables (virtual) merged back into one normalized presentation (by union). Also it's a good idea to create multiple views for multiple use-cases - prevent godviews. like in oop you can combine "abstract" views to more specialized ones. benefits are: use much less storagespace, much better index usage and maintaining (remember: "or" is an index-killer), less table-locks at read/write/delete operations, allows using of foreign-keys to protect data-consitence and use traversal update/delete ... views hides complexity

all together results in:
much better read/write performance especialy if count of assignments grows up over the time (over many years),
query-optimizer can cache queryplans,
separation of concerns and decoupling (no tech debt -> all types must not share the same assignment-structure, but can normalized to a common structure by view).
better overview and documentation for old and new team members

I know, maybe its sounds greatly exaggerated for this simple example. But it's so important, to invest in a good architectur at the beginning of a softwareproject.
Try to avoid "i refactor this part later" because often "later equals never"

"quick and dirty" must have a very good reason - it can be the beginning of software-degeneration and can be so expensive in the future (tech dept). "there was no time" is a lame excuse!!! building good architectures needs time / same like write good books needs time. at the end you have the responsibility for your work - and maybe you must maintain it for a long time.

You should be proud about what your have created - you shouldn't have to be ashamed of it

And please use a uid for every datarow (auto-id, uuid ...)! Also every book has page-numbers ;)

Just use joins, not whereHas. And performance will be even better. Try it.

Truthfully this is why I generally prefer to use Eloquent more as a query builder than an ORM.

Thanks

Great Stuff, and performance gains are huge!

I think have solution will be better and the solution just make index on the feild you want search by

Awesome!

Great tip! I’ve learned something new 🎉

Great, but for an better performance don't use assignable_type as a string make it integer and with adding an combined index with task_id and assignable_id and assignable_type u will get a more better performance query

Nice ❤

wonderful and easy to understand thanks a lot sir

There is no such thing as a many-to-many relationship from the eyes of a true software architect. There is only one-to-many relationships - even if that means creating pivot models.

Great 👍 , Isn't even faster if we move the repetition of the condition where assignable_id =... outside to be and whereIn('assignable_id',[auth()->id, auth()->user()->group_id, auth->user()->position_id])?

Nice.

Good advice 😊

Amazing, the problem is with the sub queries with orWhereHas relation. It's a problem common across ORMs. Query performance and getting the best methods to use in order to avoid performance issues.

cool vids