Error: not a single-group group function

[pietro2328]

I've a entity with field, for example: id | id_country (fk) | name | surname

Then in my Entity:

• @GRID\Source(columns="id, id_country.name, surname, name", groups="List" )

I've problem only when I add the field id_country.name. The problem is for the "underscore"? How can I fix that?

[DonCallisto]

Try to change the annotation at field level as follows

@GRID\Column(field="country.name")

and at entity level

@GRID\Source(columns="id, country.name, surname, name", groups="List" )

This should resolve your issue but I'm not 100% sure

[pietro2328]

I can't change the name of the field, because it is used in other systems. So my query, to know how I can use a field when it has "underscore", for example "id_county". Thank you very much for your help.

[DonCallisto]

@pietro2328 you're not changing field name, but annotation field name. Give it a try and let me know

[pietro2328]

It is a part of the entity.

Maybe I don't understand how to make the annotation have a different name.

/**

• Source\AdminBundle\Entity\SG_VENDOR
• @ORM\Table()
• @ORM\Entity(repositoryClass="Source\AdminBundle\Entity\SG_VENDORRepository")
• @GRID\Source(columns="id, id_country.id, date, time, comments", groups="List" ) */

class SG_VENDOR

• @GRID\Column(field="id_country.name", title="Country", operatorsVisible=false, filterable=false)
  */ private $id_country;

When I switch to:

• @GRID\Source(columns="id, id_country.id, date, time, comments", groups="List" )
• @GRID \ Column (field = "idcountry.name", title = "Country", operatorsVisible = false, filterable = false)
  • /

I have that problem. But only with the fields with "underscore". Thank you very much for your help, since it has stopped me.

[pietro2328]

Is not that. I've tested it in another environment, changing the field (id_country by country), and the problem continues. The error, which throws me:

ORA-00937: not a single-group group function

            <div>
                <strong>500</strong> Internal Server Error - <abbr title="Doctrine\DBAL\Driver\OCI8\OCI8Exception">OCI8Exception</abbr>
            </div>

I've always worked MySql (with this Bundle) and there was no problem, but now I'm with Oracle. Any ideas?

[DonCallisto]

And this error is shown even when you have _?

Could you paste your class code without underscore? I would to be sure that no errors are in there.

[pietro2328]

I made the change without "_", but it throws the same error: ORA-00937: not a single-group group function

The class would be:

/**

Source\AdminBundle\Entity\SG_VENDOR
@ORM\Table()
@ORM\Entity(repositoryClass="Source\AdminBundle\Entity\SG_VENDORRepository")
@GRID\Source(columns="id, country.name, date, time, comments", groups="List" )
*/

class SG_VENDOR

/** * @var integer $id * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") * @GRID\Column(field="id", title="id", operatorsVisible=false, filterable=false)
*/ private $id;

/**
 * @ORM\ManyToOne(targetEntity="SG_MASTER", inversedBy="SG_VENDOR")
 * @ORM\JoinColumn(name="country", referencedColumnName= "id")
 * @GRID\Column(field="country.name", title="Country",  operatorsVisible=false, filterable=false)            
 */
private $country;

I really do not know what the error is, it's the first time I work with this bundle in Oracle, with MySql this error doens't appaer. The error only occurs with the grid, not in a CRUD. Thank you very much for your help.

[DonCallisto]

@hmert do you have any idea?

[hmert]

I guess it's about Oracle's column selector.

@pietro2328 What is the SQL querys of this grid?

[DonCallisto]

@hmert problem seems about aggregate functions (min, max, sum, etc) that are not completed with group by statement.

[pietro2328]

Here it goes:

OCI8Exception ::fromErrorInfo (array('code' => '937', 'message' => 'ORA-00937: not a single-group group function', 'offset' => '40', 'sqltext' => 'SELECT count(DISTINCT s0_.id) AS sclr0, s1_.country AS country1 FROM SG_VENDOR s0_ LEFT JOIN SG_COUNTRY s1_ ON s0_.id_country = s1_.id'))

<br />

in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\system\vendor\doctrine-dbal\lib\Doctrine\DBAL\Driver\OCI8\OCI8Statement.php at line 171&nbsp;

The problem I see is that it does a count () without a group by. Obviously the query in SqlPlus does not work. But this query is created automatically (I do not know how to correct it).

In order for the grid to work, I had to work with a Vector and to pass it a query (with a create_query). But that way it loses a lot of potentiality that the bundle has.

Thank you very much for your help.

[DonCallisto]

Try to add this to your Source annotation

groupBy={"id"}

So

@GRID\Source(groupBy={"id"}, columns="id, id_country.name, surname, name", groups="List" )

[pietro2328]

Yes, I had tried that, but the error it throws is: ORA-00979: not a GROUP BY expression

at OCI8Exception ::fromErrorInfo (array('code' => '979', 'message' => 'ORA-00979: not a GROUP BY expression', 'offset' => '39', 'sqltext' => 'SELECT a.* FROM (SELECT s0_.id AS id0, s1_.country AS country1 FROM SG_VENDOR s0_ LEFT JOIN SG_COUNTRY s1_ ON s0_.id_country = s1_.id GROUP BY s0_.id) a '))

Then, with {groupBy='id, id_country.name'} this query in SqlPlus works, but in my code throws: Cannot group by undefined identification variable id_country.name

I think the error is related to Oracle. Because in another database engine is very simple.

[DonCallisto]

From your query is pretty clear that id_country.name does not exists. You should put {groupBy='id, country'} as group by attribute. Could you give it a try?

[pietro2328]

Yes, try it and same error. But I think the ORM has to solve it directly. No need to do GroupBy. It seems very strange to me.

[DonCallisto]

Sorry, I ran out of ideas :(

[pietro2328]

Thank you very much for your help and time. What I think is that in the first query is doing a "count ()" of more:

SELECT count (DISTINCT s0_.id) AS sclr0, s1_.country AS country1 FROM SG_VENDOR s0_ LEFT JOIN SG_COUNTRY s1_ ON s0_.id_country = s1_.id '))

In Oracle thats produces the error. But I don't know how to avoid it. Tks!