CODEWITHSUNDEEP
symfonydoctrine-orm
Majdi Taleb
Majdi Taleb

Reputation: 127

symfony2 createQueryBuilder ORDER BY clause is not in SELECT list

Using this code within my repository:

 public function getListAccount($nombreParPage, $page) {
    if ((int) $page < 1) {
        throw new \InvalidArgumentException('L\'argument $page ne peut être inférieur à 1 (valeur : "' . $page . '").');
    }


    $qb = $this->createQueryBuilder('u');
    $qb->join('u.account', 'a');
    $qb->join('a.company','c');
    $qb->where($qb->expr()->like('u.roles', ':param'))
        ->orWhere($qb->expr()->like('u.roles',':param1'))
        ->OrderBy('a.name')
        ->setParameter('param', 'param1')
        ->setParameter('param1','param2');
    $qb->setFirstResult(($page - 1) * $nombreParPage)
        ->setMaxResults($nombreParPage);
    return new Paginator($qb);
}

and in my twig

{% for user in users%}
                        <tr>
                            {#   <th scope="row" class="column1"><input type="checkbox"/></th>#}
                            <td class="selectaccount"><input type="checkbox" name="itemlist" value="{{ user.id }}"/>
                            </td>
                            <td>{{ user.account.id }}</td>
                            <td>{{ user.account.company.name }}</td>
                            <td>{{ user.username }}</td>
                            <td>{{ user.account.name }}</td>
                            <td>{{ user.account.lastname }}</td>
                            <td>{{ user.account.credit }}</td>
                            <td>{{ user.account.dateCreation|date('Y-m-d H:i:s') }}</td>
                            <td>{{ user.account.dateExpiration|date('Y-m-d H:i:s') }}</td>

Result is:

**

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

**

An exception has been thrown during the rendering of a template ("An exception occurred while executing 'SELECT DISTINCT id_0 FROM (SELECT u0_.id AS id_0, u0_.username AS username_1, u0_.password AS password_2, u0_.salt AS salt_3, u0_.FieldMail AS FieldMail_4, u0_.FieldFax AS Fie

I'm using mysql 5.7 and I don't khnow what I should do: this script work for me with previous mysql version but not with mysql 5.7 and mysql 7

Upvotes: 0

Views: 649

Answers (2)

Clement Majdanski
Clement Majdanski

Reputation: 3

In your querybuilder, right after your ->join() :

$qb = $this->createQueryBuilder('u');
$qb->join('u.account', 'a');
$qb->addSelect('a')  /* add account to your selection */
$qb->join('a.company','c');
$qb->where($qb->expr()->like('u.roles', ':param'))
    ->orWhere($qb->expr()->like('u.roles',':param1'))
    ->OrderBy('a.name')
    ->setParameter('param', 'param1')
    ->setParameter('param1','param2');
$qb->setFirstResult(($page - 1) * $nombreParPage)
    ->setMaxResults($nombreParPage);

Upvotes: 0

Cristian Bujoreanu
Cristian Bujoreanu

Reputation: 1167

Try adding the column a.name: 

$qb = $this->createQueryBuilder('u');
$qb->select('u, a.name');

Upvotes: 1

Related Questions