SQL queryes into Datagrid

Hello Oro friends!

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

PgSQL

SELECT COALESCE(t2.first_name, ' ') || ' ' || COALESCE(t2.last_name, '') FROM ibv_household_member AS t1 JOIN orocrm_contact AS t2 ON t2.id = t1.contact_id WHERE t1.household_id IN (SELECT DISTINCT t3.household_id FROM ibv_household_member AS t3 WHERE t3.contact_id = 8) AND t1.responsible = 't' LIMIT 1

1 2 3 4 5 6 7

SELECT COALESCE(t2.first_name, ' ') || ' ' || COALESCE(t2.last_name, '') FROM ibv_household_member AS t1 JOIN orocrm_contact AS t2 ON t2.id = t1.contact_id WHERE t1.household_id IN (SELECT DISTINCT t3.household_id                            FROM ibv_household_member AS t3                            WHERE t3.contact_id = 8) AND t1.responsible = 't' LIMIT 1

I need a new column with the result.

How can I do it?

would be something like that?

YAML

source: type: orm query: select: - c.id - c.firstName - c.lastName - c.createdAt - c.updatedAt - c.fax - SELECT COALESCE(t2.first_name, ' ') || ' ' || COALESCE(t2.last_name, '').....

1 2 3 4 5 6 7 8 9 10 11

source:    type: orm       query:          select:             - c.id             - c.firstName             - c.lastName             - c.createdAt             - c.updatedAt             - c.fax             - SELECT COALESCE(t2.first_name, ' ') || ' ' || COALESCE(t2.last_name, '').....

Thank you so much guys!

Topic

[William RadiParticipant]

Hi!

Any news?

[Andrey YatsenkoModerator]

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 YatsenkoModerator]

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

YAML

datagrids: contacts-grid: source: query: select: - CONCAT_WS(' ', c.firstName, c.lastName) AS fullContactName columns: fullContactName: label: oro.contact.full_name.label

1 2 3 4 5 6 7 8 9

datagrids:     contacts-grid:         source:             query:                 select:                 - CONCAT_WS(' ', c.firstName, c.lastName) AS fullContactName         columns:             fullContactName:                 label:         oro.contact.full_name.label

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

[spyout98Participant]

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:

YAML

datagrids: contacts-grid: source: query: select: - SELECT COALESCE(t2.first_name, ' ') || ' ' || COALESCE(t2.last_name, '') FROM ibv_household_member AS t1 JOIN orocrm_contact AS t2 ON t2.id = t1.contact_id WHERE t1.household_id IN (SELECT DISTINCT t3.household_id FROM ibv_household_member AS t3 WHERE t3.contact_id = 8) AND t1.responsible = 't' LIMIT 1 columns: fullContactName: label: oro.contact.full_name.label

1 2 3 4 5 6 7 8 9

datagrids:     contacts-grid:         source:             query:                 select:                   - SELECT COALESCE(t2.first_name, ' ') || ' ' || COALESCE(t2.last_name, '') FROM ibv_household_member AS t1 JOIN orocrm_contact AS t2 ON t2.id = t1.contact_id WHERE t1.household_id IN (SELECT DISTINCT t3.household_id                            FROM ibv_household_member AS t3                            WHERE t3.contact_id = 8) AND t1.responsible = 't' LIMIT 1         columns:             fullContactName:                 label:         oro.contact.full_name.label

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 5 years, 7 months ago by  spyout98.

[spyout98Participant]

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:

PgSQL

SELECT COALESCE(t2.first_name, ' ') || ' ' || COALESCE(t2.last_name, '') FROM ibv_household_member AS t1 JOIN orocrm_contact AS t2 ON t2.id = t1.contact_id WHERE t1.household_id IN (SELECT DISTINCT t3.household_id FROM ibv_household_member AS t3 WHERE t3.contact_id = [CONTACT-ID-IS-HERE]) AND t1.responsible = 't' LIMIT 1

1 2 3 4 5 6 7

SELECT COALESCE(t2.first_name, ' ') || ' ' || COALESCE(t2.last_name, '') FROM ibv_household_member AS t1 JOIN orocrm_contact AS t2 ON t2.id = t1.contact_id WHERE t1.household_id IN (SELECT DISTINCT t3.household_id                            FROM ibv_household_member AS t3                            WHERE t3.contact_id = [CONTACT-ID-IS-HERE]) AND t1.responsible = 't' LIMIT 1

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 RadiParticipant]

?

[MichaelKeymaster]

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 RadiParticipant]

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! :)

[Author]

Replies