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

[@juanpineda-montoya]

Great Stuff, and performance gains are huge!

[@GringoDotDev]

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

[@adilizm704]

wonderful and easy to understand thanks a lot sir

[@killionaire175]

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.

[@UwU-uq9pq]

dammm I dont understand.... why it's that?

[@killionaire175]

@@UwU-uq9pq A many-to-many relationship has an intermediary (pivot) table that holds the foreign IDs. Rather than ignore the pivot that sits between the relationship, you should see the database blueprint from the perspective if including the pivot model. At that level, you are always dealing with one-to-many (or has-many) relationships.

[@UwU-uq9pq]

@@killionaire175 by saying that the laravel model many to many relationship actually has a pivot table to do that? even if we didnt create a pivot table? or do handle many - many relationship we better to use a pivot table?

[@KG-id3hk]

​@killionaire175 In an ideal world, you are right, but, when working with sensitive data, its inevitable and you cant afford to lose links between data because of a silly detachable mistake.

[@MickDavies]

Great tip! I’ve learned something new 🎉

[@mahmoudadel8313]

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

[@insaneskullgaming]

I prefer this, no need to store model with namespace, just make it types and map them when you need to query further.

[@mahmoudadel8313]

@@insaneskullgaming it's all about database not what we prefer when using polymorphic relationship model type when be string and index it's not what working fine in another hand integer working perfectly with index

[@free2idol1]

@@mahmoudadel8313 Hi,
if making the assignable_type as integer, then how can we map them? by creating constant or Enum?
Can you please elaborate?
Thanks.

[@cellocarlos]

Awesome

[@InMemoryOfNeo]

nice tip, thanks.

[@francescoleto2823]

Good advice 😊

[@GergelyCsermely]

Thanks

[@EliasGikonyoMogul]

Awesome!

[@wadecodez]

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

[@ricohertha3537]

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 ;)

[@cellocarlos]

The created db-view "assignables" would be a union of the other tables? In terms of performance, querying this view is better than querying the polymorphic table?

[@bulent2435]

Nice.

[@user-fy2hq3xb2z]

Nice ❤

[@resort16]

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

[@zikriendisyahmunandar4037]

cool vids

[@mohamadcheaib]

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])?

[@andreivirgillungeanu5542]

I had the same idea, probably is.

[@me1ales]

but you will have wrong records same id like group_id in positions or users table, same id like user_id in groups or positions etc

[@AndersFloor]

If auth()->id() = 1 and auth-()>user()->position_id = 2, your solution would also return the tasks of the user with auth()->id() of 2.

[@mohamadcheaib]

I am not a fan of polymorphic, because it forces the database structure to follow Laravel models, i prefer to make the db structure independent of the framework that i am working on.

[@user-tb4ig7qh9b]

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