I’ve found a solution by adapting this DQL example to DBAL. The trick is to get the raw SQL of the subquery, wrap it in brackets, and join it. Parameters used in the subquery must be set in the main query: Important it’s the createQueryBuilder of connection not the one of the entity manager. $subSelect … Read more
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
With CakePHP 3.x it’s now necessary to either indicate the data type, which for an array of values need to have [] appended to the type: $query = $articles ->find() ->where([‘id’ => $ids], [‘id’ => ‘integer[]’]); or to explicitly make use of the IN keyword: $query = $articles ->find() ->where([‘id IN’ => $ids]); See also … Read more
I’m still not completely sure about your table relationships but from my guess, I came up with the following solution, first create the relationships using Eloquent models: User Model (for usres table): namespace App; use App\Course; use Illuminate\Notifications\Notifiable; use Illuminate\Foundation\Auth\User as Authenticatable; class User extends Authenticatable { use Notifiable; public function courses() { return $this->hasMany(Course::class); … Read more
A big thanks to @AdrienCarniero for his alternative query structure for sorting the highest version with a simple JOIN where the entity’s timeMod is less than the joined table timeMod. Alternative Query SELECT view_version.* FROM view_version #inner join to get the best version LEFT JOIN view_version AS best_version ON best_version.viewId = view_version.viewId AND best_version.timeMod > … Read more
As already mentioned by Vishal Gajjar in the comments, you are using the reserved keyword desc for your column name, hence the error, it’s not bakes fault, it’s yours. In order to be able to use such reserved words, the column name needs to be quoted properly, however CakePHP 3 doesn’t auto-quote by default anymore, … Read more
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
The same alias cannot be defined 2 times in the same query $qb = $this->_em->createQueryBuilder(); $qb2 = $qb; $qb2->select(‘m.id’) ->from(‘Custom\Entity\MembreService’, ‘ms’) ->leftJoin(‘ms.membre’, ‘m’) ->where(‘ms.id != ?1’); $qb = $this->_em->createQueryBuilder(); $qb->select(‘mm’) ->from(‘Custom\Entity\Membre’, ‘mm’) ->where($qb->expr()->notIn(‘mm.id’, $qb2->getDQL()) ); $qb->setParameter(1, $service); $query = $qb->getQuery(); return $query->getResult(); Ideally you should use many-to-many relation for your entity, in this case your … Read more
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’));
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