selectFrom query fails using manipulateQuery with HAVING clause #592

webdevilopers · 2014-07-31T11:14:55Z

This is the way I manipulate the query:

        $grid->getSource()->manipulateQuery(
            function ($query) use ($tableAlias) {
                $selectItems = 'SELECT GroupConcat(DISTINCT ciPtGr.name SEPARATOR \', \')
                                FROM Entity\ContractItem ci
                                JOIN ci.parttype ciPt
                                JOIN ciPt.parttypeGroup ciPtGr
                WHERE ci.contract = ' . $tableAlias . '.id';
        $query->addSelect('(' . $selectItems . ') AS parttype_group_name');

                $selectLastUpdate = 'SELECT MAX(s2.createdAt) FROM Entity\ContractState s2'
                            . ' WHERE s2.contract = ' . $tableAlias . '.id';
                $query->addSelect('(' . $selectLastUpdate . ') AS HIDDEN state_last_created');

                $having = 'state_last_created = _states.createdAt';
                $query->having($having);
            }

The generated query looks like this and works fine:

SELECT 
  c0_.state AS state0, 
  c1_.updated_at AS updated_at1, 
  f2_.username AS username2, 
  b3_.name AS name3, 
  c1_.id AS id4, 
  c0_.created_at AS created_at5, 
  c1_.number AS number6, 
  c1_.problemdescription AS problemdescription7, 
  (
    SELECT 
      GROUP_CONCAT(DISTINCT p4_.name SEPARATOR ', ') AS sclr9 
    FROM 
      contract_items c5_ 
      INNER JOIN parttypes p6_ ON c5_.parttype_id = p6_.id 
      INNER JOIN parttype_groups p4_ ON p6_.parttype_group_id = p4_.id 
    WHERE 
      c5_.contract_id = c1_.id
  ) AS sclr8, 
  (
    SELECT 
      MAX(c7_.created_at) AS dctrn__2 
    FROM 
      contract_states c7_ 
    WHERE 
      c7_.contract_id = c1_.id
  ) AS sclr10 
FROM 
  contracts c1_ 
  LEFT JOIN contract_states c0_ ON c1_.id = c0_.contract_id 
  LEFT JOIN fos_user f2_ ON c1_.project_manager_id = f2_.id 
  LEFT JOIN branches b3_ ON c1_.branch_id = b3_.id 
HAVING 
  sclr10 = c0_.created_at

Now I would like to populate the selectFrom for branches from query:

    /**
     * @ORM\ManyToOne(targetEntity="Branch", inversedBy="contracts")
         * @GRID\Column(field="branch.name", title="Branch", filter="select", selectFrom="query", defaultOperator="eq", operatorsVisible=false)
     */
    private $branch;

This results in the folloqing SQL:

SELECT DISTINCT _branch.name as branch::name FROM Plusquam\Bundle\ContractBundle\Entity\Contract _a
LEFT JOIN _a.states _states
LEFT JOIN _a.projectManager _projectManager
LEFT JOIN _a.branch _branch
WHERE _branch.name = ?123
HAVING state_last_created = _states.createdAt
ORDER BY _branch.name asc

This query will fail:
'state_last_created': Error: 'state_last_created' is not defined.

Without the HAVING clause everything will work fine.
But I think the problem is the remaining part of the manipulated query e.g. the $selectLastUpdate, right?

The text was updated successfully, but these errors were encountered:

Abhoryo · 2014-08-01T09:16:41Z

Your case is very tricky.
The populate function ignores the manipulate query function because the bundle don't want some manipulation compromise its functionality.

Can you try something:

https://github.com/Abhoryo/APYDataGridBundle/blob/2.1.13/Grid/Source/Entity.php#L421
Add:
$this->prepareQuery($this->querySelectfromSource);

Change this block with this one: https://github.com/Abhoryo/APYDataGridBundle/blob/2.1.13/Grid/Source/Entity.php#L595
Old block:

$result = $query->select($this->getFieldName($column, true))
                    ->distinct()
                    ->orderBy($this->getFieldName($column), 'asc')
                    ->setFirstResult(null)
                    ->setMaxResults(null)
                    ->getQuery()
                    ->getResult();

New block:

$query->resetDQLPart('where');
$result = $query->addSelect($this->getFieldName($column, true))
                    ->distinct()
                    ->orderBy($this->getFieldName($column), 'asc')
                    ->setFirstResult(null)
                    ->setMaxResults(null)
                    ->getQuery()
                    ->getResult();

webdevilopers · 2014-08-01T11:55:25Z

The error changed:

QueryException: [Semantical Error] line 0, col 707 near 'states::state': Error: 'states::state' is already defined.  +
[1/2] QueryException:```

```sql
SELECT
DISTINCT _states.state as states::state,
_a.updatedAt,
_projectManager.username as projectManager::username,
_branch.name as branch::name,
_a.id,
_states.createdAt as states::createdAt,
_a.number,
_a.problemDescription,
(
    SELECT GroupConcat(DISTINCT ciPtGr.name SEPARATOR ', ')
    FROM Entity\ContractItem ci
    JOIN ci.parttype ciPt
    JOIN ciPt.parttypeGroup ciPtGr
    WHERE ci.contract = _a.id
) AS parttype_group_name,
(
    SELECT MAX(s2.createdAt)
    FROM ContractState s2     
    WHERE s2.contract = _a.id
) AS HIDDEN state_last_created,
_states.state as states::state
FROM Entity\Contract _a
LEFT JOIN _a.states _states
LEFT JOIN _a.projectManager _projectManager
LEFT JOIN _a.branch _branch
HAVING state_last_created = _states.createdAt
ORDER BY _states.state asc

webdevilopers · 2014-08-01T12:07:37Z

I just updated to the latest dev-master.

The line $this->prepareQuery($this->querySelectfromSource); already exists.
I added your block.

The error has changed again:
Column not found: 1054 Unknown column 'c0_.created_at' in 'having clause'

Here is my generated clause:

SELECT 
  c0_.state AS state0, 
  c1_.updated_at AS updated_at1, 
  f2_.username AS username2, 
  b3_.name AS name3, 
  (
    SELECT 
      GROUP_CONCAT(DISTINCT p4_.name SEPARATOR ', ') AS sclr5 
    FROM 
      contract_items c5_ 
      INNER JOIN parttypes p6_ ON c5_.parttype_id = p6_.id 
      INNER JOIN parttype_groups p4_ ON p6_.parttype_group_id = p4_.id 
    WHERE 
      c5_.contract_id = c1_.id
  ) AS sclr4, 
  (
    SELECT 
      MAX(c7_.created_at) AS dctrn__2 
    FROM 
      contract_states c7_ 
    WHERE 
      c7_.contract_id = c1_.id
  ) AS sclr6 
FROM 
  contracts c1_ 
  LEFT JOIN contract_states c0_ ON c1_.id = c0_.contract_id 
  LEFT JOIN fos_user f2_ ON c1_.project_manager_id = f2_.id 
  LEFT JOIN branches b3_ ON c1_.branch_id = b3_.id 
HAVING 
  sclr6 = c0_.created_at

The error makes sense so far. The c0_.created_at aka states.createdAt is missing indeed.
But that itself is strange since it is configured in the annotations:

@GRID\Source(columns="id, createdAt, updatedAt, states.createdAt, states.state, number, problemDescription, items.parttype.parttypeGroup.name:GroupConcat:Distinct, projectManager.username, branch.name", groupBy={})

    /**
     * @ORM\OneToMany(targetEntity="Plusquam\Bundle\ContractBundle\Entity\ContractState", mappedBy="contract")
     * @ORM\OrderBy({"date" = "DESC"})
     * 
     * @GRID\Column(type="datetime", field="states.createdAt:Max", title="Last updated")
     * @GRID\Column(type="datetime", field="states.createdAt", title="Created at", visible=false)
     * @GRID\Column(type="text", field="states.state", title="Current State", filter="select", selectFrom="source", defaultOperator="eq", operatorsVisible=false)
     */
    private $states;

Maybe my query manipulation is wrong?

        $grid->getSource()->manipulateQuery(
            function ($query) use ($tableAlias) {
                $selectLastUpdate = 'SELECT MAX(s2.createdAt) FROM Entity\ContractState s2'
                            . ' WHERE s2.contract = ' . $tableAlias . '.id';
                $query->addSelect('(' . $selectLastUpdate . ') AS HIDDEN state_last_created');

                $having = 'state_last_created = _states.createdAt'; # WRONG ALIAS `_states` ?!
                $query->having($having);
        }

webdevilopers · 2014-08-01T12:30:59Z

I was missing the column indeed afterwars in my setColumnsOrder method:

        $grid->setColumnsOrder(array(
            'createdAt',
            'states.state',
                'states.createdAt',
                'updatedAt',
                'state_last_created',
            'parttype_group_name',
            'number_full',
                'projectManager.username',
            'branch.name'
        ), false);

My error now is:
Primary column doesn't exists

A typo BTW! :)

If I change the method by adding id manually:

        $grid->setColumnsOrder(array(
                'id',
            'createdAt',
            'states.state',
                'states.createdAt',
                'updatedAt',
                'state_last_created',
            'parttype_group_name',
            'number_full',
                'projectManager.username',
            'branch.name'
        ), false);

I get:
'states::state': Error: 'states::state' is already defined.

Based on the following DQL:

SELECT
DISTINCT _a.id,
_states.state as states::state,
_states.createdAt as states::createdAt,
_a.updatedAt,
_projectManager.username as projectManager::username,
_branch.name as branch::name,
_states.state as states::state
FROM Entity\Contract _a
LEFT JOIN _a.states _states
LEFT JOIN _a.projectManager _projectManager
LEFT JOIN _a.branch _branch
ORDER BY _states.state asc

Abhoryo · 2014-08-01T12:41:42Z

I don't know why you have twice the column.
Can you check the setColumnsOrder method?

webdevilopers · 2014-08-01T12:46:55Z

I removed it from my setColumnsOrder method and I get the same error for the branch.name:

        $grid->setColumnsOrder(array(
                'id',
            #'states.state',
                'states.createdAt',
                'updatedAt',
                'state_last_created',
            'parttype_group_name',
            'number_full',
                'projectManager.username',    
            'branch.name'
        ), false);

I removed the branch.name too. Though the query is displayed now the complete GRID columns branch.name and states.state no longer appear.

The problem seems to be produced inside the query building for selectFrom="query" using my example.

I will re-add the columns and check the query builder.

webdevilopers · 2014-08-01T12:57:13Z

I checked out the DQL inside
https://github.com/Abhoryo/APYDataGridBundle/blob/2.1.13/Grid/Source/Entity.php#L595-601

The DQL after cloning in line 593 looks correct:

SELECT
_a.id,
_states.state as states::state,
_states.createdAt as states::createdAt,
_a.updatedAt,
_projectManager.username as projectManager::username,
_branch.name as branch::name
FROM Entity\Contract _a LEFT JOIN _a.states _states LEFT JOIN _a.projectManager _projectManager LEFT JOIN _a.branch _branch

After running through the result it has changed to:

SELECT
DISTINCT _a.id,
_states.state as states::state,
_states.createdAt as states::createdAt,
_a.updatedAt,
_projectManager.username as projectManager::username,
_branch.name as branch::name,
_states.state as states::state
FROM Entity\Contract _a LEFT JOIN _a.states _states LEFT JOIN _a.projectManager _projectManager LEFT JOIN _a.branch _branch ORDER BY _states.state asc

and the duplicate column got added.

The reason for this is the change you recommended:

#$result = $query->select($this->getFieldName($column, true))
$result = $query->addSelect($this->getFieldName($column, true))

A possible workaround seems to be completely removing the (add)select part:

                $result = $query
                     #->select($this->getFieldName($column, true))
                    ->distinct()
                    ->orderBy($this->getFieldName($column), 'asc')
                    ->setFirstResult(null)
                    ->setMaxResults(null)
                    ->getQuery()
                    ->getResult()
                    ;

My GRID correctely displays the columns and populates the filters.

But I can't tell what consequences this may have on the general functionality, @Abhoryo .

Abhoryo · 2014-08-01T19:48:22Z

The performance will drop if you keep the whole query.

I think that what you do is overload.

Why find the max date ? Just perform an order on this column. You already perform a groupConcat so you should have only the data of the good row.
Then the groupconcat can also be performed without the queryManiplator

webdevilopers · 2014-08-02T10:31:42Z

Thanks @Abhoryo , next week I will give a variation of my SQL a final chance.
But I still believe that the MAX subquery and HAVING clause is required to make the SQL work the way expected - it is based on the example I posted at the end of issue #590 .

Without it and just a simple MAX and ORDER and GROUP By MySQL will not return the row of a joined table where a column is ordered by maximum date. It is a typical MySQL behaviour.

Hard to explain without a direct comparison. I will post the different queries and results in a GIST next week and maybe I'm lucky and the GRID can solve it. :)

webdevilopers · 2014-08-11T08:32:02Z

Hi @Abhoryo ,
I tried to break down the MySQL problematic with "joining a row to get latest change in one single row" here:
https://gist.github.com/webdevilopers/3a82175f6f4743e707b0

I hope you get the idea of my HAVING solution:

SELECT c.id, c.number, cs.createdAt, cs.stateName,
(
  SELECT MAX(cs2.createdAt)
  FROM `contract_states` `cs2`
  WHERE `cs2`.`contract_id` = `c`.`id`
) AS lastCreatedAt
FROM `contracts` `c`
JOIN `contract_states` `cs` ON `c`.`id` = `cs`.`contract_id`
WHERE 1
HAVING cs.createdAt = lastCreatedAt

But I also realized that there is a better method which is simply using a WHERE condition for the MAX date query:

SELECT c.id, c.number, cs.createdAt, cs.stateName
FROM `contracts` `c`
JOIN `contract_states` `cs` ON `c`.`id` = `cs`.`contract_id`
WHERE cs.createdAt = (
  SELECT MAX(cs2.createdAt)
  FROM `contract_states` `cs2`
  WHERE `cs2`.`contract_id` = `c`.`id`
)

I tried the following solution for this:

    $grid->getSource()->manipulateQuery(
            function ($query) use ($tableAlias) {
                $selectItems = 'SELECT GroupConcat(DISTINCT ciPtGr.name SEPARATOR \', \')
                                FROM Entity\ContractItem ci
                                JOIN ci.parttype ciPt
                                JOIN ciPt.parttypeGroup ciPtGr
                WHERE ci.contract = ' . $tableAlias . '.id';
        $query->addSelect('(' . $selectItems . ') AS parttype_group_name');

                $selectLastUpdate = 'SELECT MAX(s2.createdAt)'
                            . ' FROM Entity\ContractState s2'
                            . ' WHERE s2.contract_id = ' . $tableAlias . '.id';
                $query->andWhere('_states.createdAt = :lastCreatedAt')
                      ->setParameter('lastCreatedAt', $selectLastUpdate);
            }
        );

But for some reason the alias in the condition is named wrong:

SELECT 
  c0_.id AS id0, 
  c1_.state AS state1, 
  c1_.created_at AS created_at2, 
  c0_.updated_at AS updated_at3, 
  c0_.problemdescription AS problemdescription4, 
  c0_.number AS number5, 
  f2_.username AS username6, 
  b3_.name AS name7, 
  (
    SELECT 
      GROUP_CONCAT(DISTINCT p4_.name SEPARATOR ', ') AS sclr9 
    FROM 
      contract_items c5_ 
      INNER JOIN parttypes p6_ ON c5_.parttype_id = p6_.id 
      INNER JOIN parttype_groups p4_ ON p6_.parttype_group_id = p4_.id 
    WHERE 
      c5_.contract_id = c0_.id
  ) AS sclr8 
FROM 
  contracts c0_ 
  LEFT JOIN contract_states c1_ ON c0_.id = c1_.contract_id 
  LEFT JOIN fos_user f2_ ON c0_.project_manager_id = f2_.id 
  LEFT JOIN branches b3_ ON c0_.branch_id = b3_.id 
WHERE 
  c1_.created_at = ? 
LIMIT 
  20

Parameters: ['SELECT MAX(s2.createdAt) FROM Plusquam\Bundle\ContractBundle\Entity\ContractState s2 WHERE s2.contract_id = _a.id']

_a.id instead of c0_ - any idea?

webdevilopers · 2014-08-11T09:30:55Z

The error occurs beacuse you cannot use a subquery inside parameters, it's more simple:

$selectLastUpdate = 'SELECT MAX(s2.createdAt)'
    . ' FROM Entity\ContractState s2'
    . ' WHERE s2.contract = ' . $tableAlias . '.id';
$query->andWhere('_states.createdAt = (' . $selectLastUpdate . ')');

For those that like to use querybuilders for DQL:

$query2 = $query->getEntityManager()->createQueryBuilder();
$query2->select($query2->expr()->max('s2.createdAt'))
    ->from('Entity\ContractState', 's2')
    ->where('s2.contract = ' . $tableAlias . '.id');
$lastCreatedAtDql = $query2->getDQL();
$query->andWhere('_states.createdAt = (' . $lastCreatedAtDql . ')');

webdevilopers · 2015-02-04T13:42:28Z

Using subqueries as parameters seems to be possible by now, see this similar example:https://gist.github.com/webdevilopers/9f182b113c9130b2dc68#file-offeradmin_configuredatagridfilters_solved-php

This should successfully work with this bundle too.

webdevilopers mentioned this issue Aug 11, 2014

Notice: unserialize(): Error when using GroupConcat #590

Closed

webdevilopers closed this as completed Feb 4, 2015

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

selectFrom query fails using manipulateQuery with HAVING clause #592

selectFrom query fails using manipulateQuery with HAVING clause #592

webdevilopers commented Jul 31, 2014

Abhoryo commented Aug 1, 2014

webdevilopers commented Aug 1, 2014

webdevilopers commented Aug 1, 2014

webdevilopers commented Aug 1, 2014

Abhoryo commented Aug 1, 2014

webdevilopers commented Aug 1, 2014

webdevilopers commented Aug 1, 2014

Abhoryo commented Aug 1, 2014

webdevilopers commented Aug 2, 2014

webdevilopers commented Aug 11, 2014

webdevilopers commented Aug 11, 2014

webdevilopers commented Feb 4, 2015

selectFrom query fails using manipulateQuery with HAVING clause #592

selectFrom query fails using manipulateQuery with HAVING clause #592

Comments

webdevilopers commented Jul 31, 2014

Abhoryo commented Aug 1, 2014

webdevilopers commented Aug 1, 2014

webdevilopers commented Aug 1, 2014

webdevilopers commented Aug 1, 2014

Abhoryo commented Aug 1, 2014

webdevilopers commented Aug 1, 2014

webdevilopers commented Aug 1, 2014

Abhoryo commented Aug 1, 2014

webdevilopers commented Aug 2, 2014

webdevilopers commented Aug 11, 2014

webdevilopers commented Aug 11, 2014

webdevilopers commented Feb 4, 2015