Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Eloquent For Advanced Querying

Eloquent For Advanced Querying

Laravel Serbia meetup "222 Artisans"

Nemanja Maric

February 22, 2018

More Decks by Nemanja Maric

Other Decks in Programming


  1. 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
  2. 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
  3. Job done • Deployed after one hour of working •

    Easy cash • Wife calling to order some chinese food and opening champagne • Im am the king
  4. 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.
  5. 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
  6. 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. `
  7. 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.
  8. But how to implement Eager load? It is so simple,

    just add with(‘some relationship’ )
  9. 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?
  10. This is working great, but we use DB Query builder

    in our model. Can we make it look pretty and use Eloquent instead?
  11. 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?
  12. Ok, what happened now? Should we worry? - Our response

    time is lower, - Small memory increase, - And get extra query. Looks good to me.
  13. Add order by company Or we can use Macro for

    this job. Let’s add them.
  14. If your Sql have ability for full text search, consider

    using them Results for search params “ltd aida”
  15. 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.
  16. Pretty standard request. We can use policy which I think

    is great, or limiting access in blade view, or just tiny scope for eloquent.
  17. 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.
  18. 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
  19. 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?