0

Using Symfony with Sql Server and from what I've read, it seems that the connection to the database is not stable.

As soon as I use the orderBy method I get an error :

Here's an example :

  $qStores =
        $this->getManager()
             ->createQueryBuilder()
             ->select('rpdv')
             ->from('MainBundle:PointDeVenteReference', 'rpdv')
             ->andWhere( 'rpdv.partenaireClient = :id_partner ' )
                 ->setParameter( 'id_partner', $this->getUser()->getPartenaire()->getIdPartenaire() )
             ->orderBy( 'rpdv.idPointDeVenteReference' , 'DESC' )
             ->setFirstResult( 0 )
             ->setMaxResults( 30 );

  $stores = new Paginator( $qStores, FALSE );

And the error :

An exception has been thrown during the rendering of a template ("An exception occurred while executing
'SELECT DISTINCT TOP 30 id_point_de_vente_reference0 
 FROM ( SELECT p0_.id_point_de_vente_reference AS id_point_de_vente_reference0, 
               p0_.reference AS reference1, 
               p0_.date_derniere_modification AS date_derniere_modification2, 
               p0_.blocage AS blocage3 
        FROM point_de_vente_reference p0_ 
        WHERE p0_.id_partenaire_client = ? 
        ORDER BY p0_.id_point_de_vente_reference DESC ) dctrn_result 
 ORDER BY id_point_de_vente_reference0 DESC' 
 with params [2829]:SQLSTATE[42000]: 
 [Microsoft][SQL Server Native Client 11.0][SQL Server]
 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, 
 unless TOP, OFFSET or FOR XML is also specified.") in MainBundle:Default:store/list.html.twig at line 79.

I tried to change the class SQLServerPlatform with corrections found on the net, without success.

Do you have any idea?

Thx !

Edit :

This bug appears to be related to the Paginator with the second parameter to true. Passing it to false, I have no error

Neufman
  • 3
  • 3
  • Well, did you read the exception? It obviously states that you're using `ORDER BY` in the wrong context: `ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified ` – kix Oct 08 '14 at 07:50
  • Ok but this query is generated by doctrine. My first block of code seems correct to me... – Neufman Oct 08 '14 at 07:52
  • Well, the code *could* be correct, but the underlying storage you're using does not seem to allow ordering. – kix Oct 08 '14 at 08:12
  • What do you mean by "underlying storage" ? SQL server accepts the "Order by" and happily. The trouble seems to be that doctrine built a wrong query. – Neufman Oct 08 '14 at 09:31
  • You cannot use `ORDER BY` with a view, a derived table, a subquery etc. That's the error returned by your SQL server. Did you try tracing its origins? – kix Oct 08 '14 at 09:36

1 Answers1

0

The dctrn_result is a derived table. From the error message, you can not use an order by. I do not know symfony2, but the code going to the database engine is invalid.

  • Craftydba
CRAFTY DBA
  • 14,351
  • 4
  • 26
  • 30