Forums

Covering OroCommerce, OroCRM, OroPlatform topics, including community updates and company announcements.

Forums OroCRM SQL queryes into Datagrid

This topic contains 8 replies, has 4 voices, and was last updated by  William Radi 3 months, 2 weeks ago.

  • Creator
    Topic
  • #37391

    William Radi
    Participant

    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
    Replies
  • #37442

    William Radi
    Participant

    Hi!

    Any news?

    #37445
    Andrey Yatsenko
    Andrey Yatsenko
    Moderator

    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?

    #37447
    Andrey Yatsenko
    Andrey Yatsenko
    Moderator

    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

    #37458

    spyout98
    Participant

    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 3 months, 3 weeks ago by  spyout98.
    #37461

    spyout98
    Participant

    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

    #37462

    William Radi
    Participant

    ?

    #37463
    Michael
    Michael
    Keymaster

    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.

    #37474

    William Radi
    Participant

    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)

You must be logged in to reply to this topic.

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

Yes No
sso for www.magecore.comsso for oroinc.comsso for oroinc.desso for oroinc.fr