Eloquent For Advanced Querying

Laravel Serbia meetup "222 Artisans"

Transcript

1. Eloquent for Advanced Querying Nemanja Marić maki10
2. About me • PHP Developer at System Inc • Member of PHP Serbia Community • Co-organizer Laravel Serbia Meetup’s • Working with PHP since 2014 • In Laravel world from 2016 • Open source contributor • Co-owner Laravel open source Package `Laravel admin panel` • Contributing to the Laravel Framework • And most !important: Husband and father of two little angels
3. Interesting part Share your knowledge with others to get some knowledge
4. And story begins...
5. “Fictional Job”
6. The job • Simple bootstrap layout • Pagination ( 200 users per page ) • Image, Name, Company, Birthday, last interactions • Must be fast • Must be over until 10 am GMT time zone, more than 6 hours left • Be ready for additional features that we are will provide you
7. Job done • Deployed after one hour of working • Easy cash • Wife calling to order some chinese food and opening champagne • Im am the king
8. Disaster In 72 different language this means same. When you get this message, everything is clear. You f**ked up something. Spending next half hour explaining client that, that is temporarily fix only.
9. 1. Users table for Auth 2. 10k Customers 3. 1 to 1 relation between Customer and Company 4. 1 to many relation between Customer and Interactions 5. Every customer have 30 Interactions
10. Debugging
11. Tools Everything that need for debugging is Only one package `Laravel Debugbar`. Made by Barry vd. Heuvel @barryvdh. Powerful tools with more than 6500 Stars. Following installation process and we are ready. For this talk we will only look at Query tab, memory, and Server response time. `
12. First reading (omg)
13. Ok, we are hit `N + 1` issue Basically `N+1` issue is type 1-to-many relationship It is much faster to get 1 query which returns 100 results than to get 100 queries which each return 1 result.
14. Let Eager load Companies
15. But how to implement Eager load? It is so simple, just add with(‘some relationship’ )
16. Add Last interaction date via eager load
18. But why eager load make so much memory leak?
19. It didn’t. Our approach is wrong.
20. Let’s try get last interaction via database query
21. Ok this is better but far from perfect. Still have too much query, and response time is more than 350ms. Can we make this to be better?
22. Yes, via subquery.
23. Add local scope and subQuery
24. Clear view and rerun the results
25. This is working great, but we use DB Query builder in our model. Can we make it look pretty and use Eloquent instead?
26. Yes, via Macro.
27. Let’s add macro then
28. New task arrived. Can we add `type` for Last Interaction?
30. Add new scope
31. Add type to view and rerun results again Ok, response is below 500 ms for 200 results, we can close one eye and this is still good result. Right?
32. No don’t close your eyes, just try making it better.
33. Let’s try eager load relationship
34. Update controller, view and rerun results
35. Ok, what happened now? Should we worry? - Our response time is lower, - Small memory increase, - And get extra query. Looks good to me.
37. Add “order by field”
39. Clickable table header
40. Add order by company Or we can use Macro for this job. Let’s add them.
41. Add in AppServiceProvider Update customer model as well
42. Add “order by birthday”
43. Add “order by last interaction date” scope
44. Add search for company or person name
45. Add scope for search
46. If your Sql have ability for full text search, consider using them Results for search params “ltd aida”
47. Add “birthday this weekend” filter
48. Update controller
49. Add scope for birthday this week We use Carbon to get date for weekends and make collection of dates in format ‘md’. Then via sql get results.
50. Limit view for moderators or admins
51. Pretty standard request. We can use policy which I think is great, or limiting access in blade view, or just tiny scope for eloquent.
52. Let’s use tiny scope
53. Update controller
54. Add visible to scope
55. Stored procedure
56. Definition of stored procedures A stored procedure is a segment of declarative SQL statements stored inside the database catalog. A stored procedure can be invoked by triggers, other stored procedures, and applications such as Java, Python, PHP. Advantages: 1. Typically stored procedures help increase the performance of the applications. 2. Stored procedures help reduce the traffic between application and database server 3. Stored procedures are secure. Disadvantages: 1. Stored procedure constructs are not designed for developing complex and flexible business logic 2. It is difficult to debug stored procedures 3. It is not easy to develop and maintain stored procedures.
57. Simple stored procedure
58. Simple?
59. Absolutely not!
60. Wrap up 1. Eloquent have built in support for stored procedure 2. The secret behind fast app, is memory usage and server response time 3. Always try to optimize your app. 4. Minimize query call. 5. Try to avoid using cache on N+1 issue 6. Use scopes because can be expanded with model or relationship. 7. Use naming conventions. 8. Chinese food doesn’t delivery to my place. 9. This talk is my point of view on Laracon 2018 talk by Jonathan Reinink @reinink
61. Open source contributors? Like i mentioned, we are build simple “laravel admin panel“, and feel free to give us review or PR is just fine, everyone like PR :) We have 59 stars and laravel news mention us in their post few times ;P Repo: https://github.com/systeminc/laravel-admin Hacky?
62. Materials Introduce Stored procedures Jonathan Reinink Docs
63. Questions? Leave review: https://joind.in/user/maki10 Slides: https://speakerdeck.com/maki10 Twitter: https://twitter.com/NemanjaMaki10