KnpPaginatorBundle Issue on Join order and MySQL 5.7+

I'm using knp paginator And we have order issue about join query we get error like this:

Cannot select distinct identifiers from query with LIMIT and ORDER BY on a column from a fetch joined to-many association. Use output walkers.

then we use this parameter: 'wrap-queries' => true After that we get this error:

SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'dctrn_result.value_37' which is not in SELECT list; this is incompatible with DISTINCT

I am using MySQL 5.7.10

Sep 28 '16 11:09 e-onux

+1, this paginator component is manipulating the query in a way that is not compatible with MySQL 5.7+.

Example situation: Entities "Product" and "Taxonomy" are linked by bridge entity "TaxonomyToProduct", because a custom sort order is needed. This sort order is an integer inside the "TaxonomyToProduct".

Then when it comes to displaying products in a taxonomy one would use the following repository function:

public function findByTaxonomySorted(Taxonomy $taxonomy, $siteId = 0) {

   $qb = $this->createQueryBuilder("p")
       ->select("p")
       ->leftJoin("p.product_to_taxonomies", "p2t")
       ->leftJoin("p2t.taxonomy", "t")
       ->leftJoin("t.children", "tc")
       ->andWhere("t = :taxonomy OR tc.parent = :taxonomy")
       ->setParameter("taxonomy", $taxonomy)
       ->andWhere("p.status = :statusEnabled")
       ->setParameter("statusEnabled", Product::STATUS_ENABLED)
   ;

   if (($siteId = intval($siteId)) >= 1) {
       $qb
           ->leftJoin("p.sites", "s")
           ->andWhere("s.id = :siteId")
           ->setParameter("siteId", $siteId)
       ;
   }

   $qb
       ->addOrderBy("p2t.sort", "ASC")
   ;

   $query = $qb->getQuery();
   return $query;

}

This works fine when being called by a controller. However the paginator is doing some DISTINCT stuff around the query preventing it from working with the sort order. I've also tried sorting products by their created date via $qb->addOrderBy("p.created", "DESC"); instead but even that causes the same error as OP's message.

SQL query from repository function's getSql() call before paginator fiddled with it:

SELECT p0_.id AS id_0, p0_.name AS name_1, p0_.slug AS slug_2, p0_.description AS description_3, p0_.meta_keywords AS meta_keywords_4, p0_.meta_description AS meta_description_5, p0_.model AS model_6, p0_.sku AS sku_7, p0_.retail_price AS retail_price_8, p0_.wholesale_price AS wholesale_price_9, p0_.sort AS sort_10, p0_.quantity AS quantity_11, p0_.image AS image_12, p0_.heavy_or_large AS heavy_or_large_13, p0_.status AS status_14, p0_.created AS created_15, p0_.modified AS modified_16, p0_.tax_rate_id AS tax_rate_id_17 FROM product p0_ LEFT JOIN product_site p2_ ON p0_.id = p2_.product_id LEFT JOIN site s1_ ON s1_.id = p2_.site_id LEFT JOIN taxonomy_to_product t3_ ON p0_.id = t3_.product_id LEFT JOIN taxonomy t4_ ON t3_.taxonomy_id = t4_.id LEFT JOIN taxonomy t5_ ON t4_.id = t5_.parent_id WHERE ( t4_.id = ? OR t5_.parent_id = ? ) AND p0_.status = ? AND s1_.id = ? ORDER BY t3_.sort ASC

SQL query after the paginator fiddled with it:

SELECT DISTINCT id_0 FROM ( SELECT p0_.id AS id_0, p0_.name AS name_1, p0_.slug AS slug_2, p0_.description AS description_3, p0_.meta_keywords AS meta_keywords_4, p0_.meta_description AS meta_description_5, p0_.model AS model_6, p0_.sku AS sku_7, p0_.retail_price AS retail_price_8, p0_.wholesale_price AS wholesale_price_9, p0_.sort AS sort_10, p0_.quantity AS quantity_11, p0_.image AS image_12, p0_.heavy_or_large AS heavy_or_large_13, p0_.status AS status_14, p0_.created AS created_15, p0_.modified AS modified_16, t1_.id AS id_17, s2_.id AS id_18, t3_.sort AS sort_19 FROM product p0_ LEFT JOIN product_site p4_ ON p0_.id = p4_.product_id LEFT JOIN site s2_ ON s2_.id = p4_.site_id LEFT JOIN taxonomy_to_product t3_ ON p0_.id = t3_.product_id LEFT JOIN taxonomy t1_ ON t3_.taxonomy_id = t1_.id LEFT JOIN taxonomy t5_ ON t1_.id = t5_.parent_id WHERE ( t1_.id = ? OR t5_.parent_id = ? ) AND p0_.status = ? AND s2_.id = ? ORDER BY t3_.sort ASC ) dctrn_result ORDER BY sort_19 ASC LIMIT 12 OFFSET 0

For some reason the paginator adding an extra ORDER BY condition that isn't possible because it hasn't been selected, and also completely redundant what with the inner query already handling the sort.

Nov 15 '16 11:11 Adambean

Oh yeah -- As per #358 I've also tried setting options "distinct" to false, and also removed the addOrderBy() from the repository function and as "defaultSortFieldName" => "p2t.sort" instead.

We've also always been using "wrap-queries" => true otherwise we get