Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Issue on Join order and MySQL 5.7+ #395

Open
e-onux opened this issue Sep 28, 2016 · 6 comments

Comments

@e-onux
Copy link

commented Sep 28, 2016

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

@Adambean

This comment has been minimized.

Copy link

commented Nov 15, 2016

+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.

@Adambean

This comment has been minimized.

Copy link

commented Nov 15, 2016

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

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

@NiR- NiR- added the bug label Jun 11, 2017
@nicolasmure

This comment has been minimized.

Copy link
Contributor

commented Sep 4, 2018

Related to #477 .

@cybernet

This comment has been minimized.

Copy link

commented Oct 8, 2018

anything new on this ?

@spolischook

This comment has been minimized.

Copy link

commented Jun 4, 2019

got this bug on mysql but on MariaDb ver 10.1.38 it works well

@cybernet

This comment has been minimized.

Copy link

commented Jun 4, 2019

still open ...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
6 participants
You can’t perform that action at this time.