query-builder

How to select from subquery using Laravel Query Builder?

In addition to @delmadord’s answer and your comments: Currently there is no method to create subquery in FROM clause, so you need to manually use raw statement, then, if necessary, you will merge all the bindings: $sub = Abc::where(..)->groupBy(..); // Eloquent Builder instance $count = DB::table( DB::raw(“({$sub->toSql()}) as sub”) ) ->mergeBindings($sub->getQuery()) // you need to … Read more

How to limit contained associations per record/group?

What you are looking for, is a solution to the greatest-n-per-group problem. You didn’t mention any specific RDBMS, but nonetheless see also http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html A library solution For those who are a little bit adventurous, I’ve developed some custom associations that transparently integrate into the ORM layer, and allow for basic limit per group for hasMany … Read more

How to filter by conditions for associated models?

Use Query::matching() or Query::innerJoinWith() When querying from the Contacts table, then what you are looking for is Query::matching() or Query::innerJoinWith(), not (only) Query::contain(). Note that innerJoinWith() is usually preferred in order to avoid problems with strict grouping, as matching() will add the fields of the association to the select list, which can cause problems as … Read more

convert mysql query to laravel query builder

\DB::table(‘cars’) ->join(‘garage’, ‘garage.id’, ‘=’, ‘cars.garage’) ->where(‘garage.name’, ‘main’) The above solves the garage and cars part, but you never specified what is aliased to p. If p is a different table then you need to add another call to join() and making the following \DB::table(‘cars’) ->join(‘garage’, ‘garage.id’, ‘=’, ‘cars.garage’) //<Other table join goes here for table … Read more