OroCRM Forums

Covering OroCRM topics, including community updates and company announcements.

Forums Forums OroCRM SQL queryes into Datagrid

This topic contains 8 replies, has 4 voices, and was last updated by  William Radi 1 year, 10 months ago.

Starting from March 1, 2020 the forum has been switched to the read-only mode. Please head to StackOverflow for support.

  • Creator
  • #37391

    William Radi

    Hello Oro friends!

    I’d like to use this SQL query into the contact datagrid (datagrid.yml file):

    I need a new column with the result.

    How can I do it?

    would be something like that?

    Thank you so much guys!

Viewing 8 replies - 1 through 8 (of 8 total)
  • Author
  • #37442

    William Radi


    Any news?

    Andrey Yatsenko
    Andrey Yatsenko

    Hi William,

    I don’t get what are you trying to fetch there with this complex select clause,
    as I see t2 there is representing contact entity (orocrm_contact AS t2), so you can display the contact full name much easier.
    What is the main idea of that query?

    Andrey Yatsenko
    Andrey Yatsenko

    You can extend the main datagrid query from any bundle just by providing datagrids.yml with required changes.
    Here is an example of adding contact full name to the grid

    Just put this to Resources/config/oro/datagrids.yml in your bundle



    But we can build any custom SQL, or only a subset of “allowed” commands?

    In the “Resources/config/oro/datagrids.yml”, for example, We tried to make it:

    But this did not work. The query itself works and give back the correct result,
    but when we copy+paste it in this file, it not works well.

    • This reply was modified 1 year, 10 months ago by  spyout98.


    Basically, we want to add a column in datagrid, showed in “/contact” page (that list all contacts), called “Family responsible name”.
    The SQL that get this result for each contact is this below:

    But we don’t know exactly how to insert this custom SQL as a new column in datagrid. We are trying to insert it via datagrids.yml and Datagrid EventListener. We need help for this to make it works.

    Related issue: https://forum.oroinc.com/orocrm/topic/sql-query-into-datagrid-eventlistener


    William Radi



    1. Please clarify whether you want to add some new column to the existing contacts grid (Andrey’s example above shows how to do that) or you are trying to create some new grid.

    2. Grid configuration files do not use SQL – they use DQL (see https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/dql-doctrine-query-language.html).

    3. Queries in the grid configuration files are structured. For example, the names of the columns you want to select, go to ‘select’ node, the tables go to ‘from’ node, etc. In you code you are trying to add the full query (with SELECT, FROM, JOIN and WHERE clauses) to the ‘select’ node. It won’t work like this.

    4. It is hard to guess what you are trying to achieve by looking at some random SQL query (e.g. why are there some hardcoded contact ID and what is all the DISTINCT sub-query about). It will be much easier for others to help you, if you explain your data structure (e.g. post the output of SHOW CREATE TABLE of relevant tables), provide some example data, and show what kind of data you want to get in the result set.


    William Radi

    Hi Michael!

    Thanks for listening and for the explanation.

    I wanted to add a new column with the query result.

    After some tests and read some datagrids Oro, I was able to find the correct syntax of the datagrid.
    And now, knowing that they use DQL, it will get easier when I need it again.

    Thanks again! :)

Viewing 8 replies - 1 through 8 (of 8 total)

The forum ‘OroCRM’ is closed to new topics and replies.

You will be redirected to [title]. Would you like to continue?

Yes No