query-builder

How to paginate associated records?

by

The paginator doesn’t support paginating associations, you’ll have to read the associated records manually in a separate query, and paginate that one, something along the lines of this: $product = $this->Products ->findBySlug($slug_prod) ->contain([‘Metas’, ‘Attachments’]) ->first(); $categoriesQuery = $this->Products->Categories ->find() ->innerJoinWith(‘Products’, function (\Cake\ORM\Query $query) use ($product) { return $query->where([ ‘Products.id’ => $product->id, ]); }) ->group(‘Categories.id’); $paginationOptions … Read more

Cakephp-3.x: How to change the data type of a selected alias?

by

As of CakePHP 3.2 you can use Query::selectTypeMap() to add further types, which are only going to be used for casting the selected fields when data is being retrieved. $query = $table ->find() ->select([‘alias’ => ‘actual_field’, /* … */]); $query ->selectTypeMap() ->addDefaults([ ‘alias’ => ‘integer’ ]); You can use any of the built-in data types, … Read more

Select entries between dates in doctrine 2

by

You can do either… $qb->where(‘e.fecha BETWEEN :monday AND :sunday’) ->setParameter(‘monday’, $monday->format(‘Y-m-d’)) ->setParameter(‘sunday’, $sunday->format(‘Y-m-d’)); or… $qb->where(‘e.fecha > :monday’) ->andWhere(‘e.fecha < :sunday’) ->setParameter(‘monday’, $monday->format(‘Y-m-d’)) ->setParameter(‘sunday’, $sunday->format(‘Y-m-d’));

Laravel Eloquent vs DB facade: Why use Eloquent and decrease performance? [closed]

by

Eloquent is Laravel’s implementation of Active Record pattern and it comes with all its strengths and weaknesses. Active Record is a good solution for processing a single entity in CRUD manner – that is, create a new entity with filled properties and then save it to a database, load a record from a database, or … Read more

How to select from subquery using Laravel Query Builder?

by

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?

by

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?

by

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

tech