Eloquent for Advanced Querying Nemanja Marić maki10 

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 

Interesting part Share your knowledge with others to get some knowledge 

And story begins... 

“Fictional Job” 

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 

Job done ● Deployed after one hour of working ● Easy cash ● Wife calling to order some chinese food and opening champagne ● Im am the king 

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. 

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 

Debugging 

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. ` 

First reading (omg) 

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. 

Let Eager load Companies 

But how to implement Eager load? It is so simple, just add with(‘some relationship’ ) 

Add Last interaction date via eager load 

No content

But why eager load make so much memory leak? 

It didn’t. Our approach is wrong. 

Let’s try get last interaction via database query 

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? 

Yes, via subquery. 

Add local scope and subQuery 

Clear view and rerun the results 

This is working great, but we use DB Query builder in our model. Can we make it look pretty and use Eloquent instead? 

Yes, via Macro. 

Let’s add macro then 

New task arrived. Can we add `type` for Last Interaction? 

No content

Add new scope 

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? 

No don’t close your eyes, just try making it better. 

Let’s try eager load relationship 

Update controller, view and rerun results 

Ok, what happened now? Should we worry? - Our response time is lower, - Small memory increase, - And get extra query. Looks good to me. 

No content

Add “order by field” 

No content

Clickable table header 

Add order by company Or we can use Macro for this job. Let’s add them. 

Add in AppServiceProvider Update customer model as well 

Add “order by birthday” 

Add “order by last interaction date” scope 

Add search for company or person name 

Add scope for search 

If your Sql have ability for full text search, consider using them Results for search params “ltd aida” 

Add “birthday this weekend” filter 

Update controller 

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. 

Limit view for moderators or admins 

Pretty standard request. We can use policy which I think is great, or limiting access in blade view, or just tiny scope for eloquent. 

Let’s use tiny scope 

Update controller 

Add visible to scope 

Stored procedure 

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. 

Simple stored procedure 

Simple? 

Absolutely not! 

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 

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? 

Materials Introduce Stored procedures Jonathan Reinink Docs 

Questions? Leave review: https://joind.in/user/maki10 Slides: https://speakerdeck.com/maki10 Twitter: https://twitter.com/NemanjaMaki10