Laravel Fluent Query Builder Join with subquery

by

Ok for all of you out there that arrived here in desperation searching for the same problem. I hope you will find this quicker then I did ;O.

This is how it is solved. JoostK told me at github that “the first argument to join is the table (or data) you’re joining.”. And he was right.

Here is the code. Different table and names but you will get the idea right? It t

DB::table('users')
        ->select('first_name', 'TotalCatches.*')

        ->join(DB::raw('(SELECT user_id, COUNT(user_id) TotalCatch,
               DATEDIFF(NOW(), MIN(created_at)) Days,
               COUNT(user_id)/DATEDIFF(NOW(), MIN(created_at))
               CatchesPerDay FROM `catch-text` GROUP BY user_id)
               TotalCatches'), 
        function($join)
        {
           $join->on('users.id', '=', 'TotalCatches.user_id');
        })
        ->orderBy('TotalCatches.CatchesPerDay', 'DESC')
        ->get();

More Related Contents:

  1. Laravel redirect back to original destination after login
  2. Migration: Cannot add foreign key constraint
  3. Laravel Eloquent groupBy() AND also return count of each group
  4. Eloquent Collection: Counting and Detect Empty
  5. Laravel – Route::resource vs Route::controller
  6. How to install Laravel 4 to a web host subfolder without publicly exposing /app/ folder?
  7. How can I resolve “Your requirements could not be resolved to an installable set of packages” error?
  8. Laravel Redirect Back with() Message
  9. global variable for all controller and views
  10. Eloquent eager load Order by
  11. How can I remove a package from Laravel using PHP Composer?
  12. multiple mail configurations
  13. Laravel. Use scope() in models with relation
  14. laravel with() method versus load() method
  15. Laravel 4: how to run a raw SQL?
  16. Rollback one specific migration in Laravel
  17. Laravel Controller Subfolder routing
  18. How to use paginate() with a having() clause when column does not exist in table
  19. Where can I set headers in laravel
  20. What is the difference between queue:work –daemon and queue:listen
  21. Validation of array form fields in laravel 4 error
  22. Best Practices for Laravel 4 Helpers and Basic Functions?
  23. Laravel 4: Failed to open stream: Permission denied
  24. Laravel save / update many to many relationship
  25. First Or Create
  26. Laravel: Returning the namespaced owner of a polymorphic relation
  27. Laravel: Change base URL?
  28. Polymorphic Eloquent relationships with namespaces
  29. Laravel 4 : Route to localhost/controller/action
  30. Clone an Eloquent object including all relationships?

Leave a Comment