Custom subpanels -> but suite changes the given sql queries

[jasmin]

Hi everyone.

I have the problem that in the history subpanel of Accounts only notes of accounts where shown, but not the notes of the linkes contacts.

I searched the forum and found some help in how to create a custom subpanel with a custom SQL . See (Creating custom subpanel on Users module - #2 by pgr)

With a simple SQL statement that workes like a charm.
But in some cases suitecrm changes the queries… and than everything crashes.

I am not a programmer and not used to php, so I know there would be nicer ways to write my query… but before makeing it nice i would prefer making it work.

My function

public function buildQuery($param) {
       global $app;
       $controller = $app->controller;
       $bean = $controller->bean;
       $query =   "" .
"(SELECT notes.id, notes.name, notes.date_entered " .
"FROM notes," .
"WHERE notes.parent_type = 'Accounts' ".
"AND notes.parent_id = '$bean->id' ".
"AND notes.deleted = 0) ".

"UNION ".

"(SELECT notes.id, notes.name, notes.date_entered " .
"FROM notes " .
"WHERE notes.parent_type = 'Contacts' ".
"AND notes.deleted = 0 ".
"AND notes.parent_id in ( ".
"  SELECT contact_id ".
"  FROM accounts_contacts ".
"  WHERE account_id = '$bean->id'))";

     return $query;
}

When i use the query against the database it works.
My problem is that Suitecrm is changing the queries.
if i use just the first part of the sql → it works.
if i use just the second part of the sql → it woks.
if i use the union → suite changes query select part of the first subquery in some kind of count.

My log shows:
[FATAL] Error running count query for Account List: Query Failed: ((SELECT count(*) c FROM notes, accounts WHERE notes.parent_type = 'Accounts' AND accounts.id = '909005ac-4832-eb18-4557-622ee4367460' AND notes.parent_id = '909005ac-4832-eb18-4557-622ee4367460' AND notes.deleted = 0) UNION (SELECT notes.id, notes.name, notes.date_entered FROM notes, accounts WHERE notes.parent_type = 'Contacts' AND accounts.id = '909005ac-4832-eb18-4557-622ee4367460' AND notes.deleted = 0 AND notes.parent_id in ( SELECT contact_id FROM accounts_contacts WHERE account_id = '909005ac-4832-eb18-4557-622ee4367460'))): MySQL error 1222: The used SELECT statements have a different number of columns

When I look at that query shown in the log it is totally clear that it must crashes… but why did it change my query and where can i supress it or what can i do to fix it?

Any Advice would be welcome.
Thanks in advance.

[rsp]

Try out this:

SELECT SUM(c) as total_count FROM (
  (SELECT COUNT(*) AS c 
   FROM notes, accounts 
   WHERE notes.parent_type = 'Accounts' 
     AND accounts.id = '...' 
     AND notes.parent_id = '...' 
     AND notes.deleted = 0)
  UNION ALL
  (SELECT COUNT(*) AS c 
   FROM notes, accounts 
   WHERE notes.parent_type = 'Contacts' 
     AND accounts.id = '...' 
     AND notes.deleted = 0 
     AND notes.parent_id IN (
       SELECT contact_id FROM accounts_contacts WHERE account_id = '...'))
) AS total;

[jasmin]

thanks rsp,

but I don’t want a count at all!

I want to fill the subpanel with the notes information. Therefore I need all rows and fielddata. My SQL query is correct, the SuiteCRM just rewrites my given query, removes my first select statement and replaces it with a count statement. Than of course the count and the second select have different columns and it crashes.

Since a normal SQL seems to work and only the union seems to force the rewrite process, I tried my best to manipulate my statement to remove the union.

"SELECT distinct notes.*, (CASE " .
" WHEN notes.parent_type = 'Contacts' THEN contacts.last_name ".
" WHEN notes.parent_type = 'Accounts' THEN accounts.name END) as contact_name ".
"FROM notes, contacts, accounts " .
"WHERE notes.deleted = 0 ".
"AND ".
"(notes.parent_type = 'Accounts' ".
"AND notes.parent_id = '$acc_id' ".
"AND accounts.id = notes.parent_id )".
" OR ".
"(notes.parent_type = 'Contacts' ".
"AND contacts.id = notes.parent_id ".
"AND notes.parent_id in (".
" SELECT contact_id ".
" FROM accounts_contacts ".
" WHERE account_id = '$acc_id')) ";

Thats not nice and not performant but it works more or less.

the funny part is:
When I try it against my database it gives me back 8 notes with data.
When I try it (same account id of cource) against the system it populates the new subpanel with the correct 8 rows and data.
BUT it said display 1-10 of 1985 Notes. So somehow the pagination seems to have problems with the distinct function. I have exactly 1985 Accounts… And when i trie to move to next one it just gives me empty lines…

If i may not use union and not distinct i have no ideas how i should get the correct data out of the database.
Has anyone a idea ?

[jasmin]

I have had some more time for testing.
SuiteCRM is creating error whenever I use “distinct” or “union”. Even with a “join” in a plain SQL statement it seems to break the statement.

I was wondering, cause I have seen examples with joins in stevens post here:

Adding records to History Subpanel?

So I’ve followed this: Add custom subpanel in Accounts module I can write a Task subpanel and I can write a cutom function to get tasks that are both related to the Account or the Opportunity. So now move on to get more complicated, to also add calls and meetings, I’m trying to follow the existing structure similare to how emails are added to Activities. I just can’t get it to work. Has anyone ever tried extending the activities and the history to include other related records? Here’s wh…

the only difference is that i tries to have all in a sql statement and there it is splited in an array.
so I changed my code to this:

$return_array['select'] =   "SELECT notes.*";
$return_array['from']   =   "FROM notes";
$return_array['where']  =   "WHERE notes.deleted = 0 AND ( ".
                            " (notes.parent_id = '{$acc_id}' AND notes.parent_type = 'Accounts') ".
                            " OR ".
                            " (notes.parent_id IN (".
                            "    SELECT contacts.id FROM contacts JOIN accounts_contacts ON contacts.id = accounts_contacts.contact_id ".
                            "    WHERE accounts_contacts.account_id = '{$acc_id}' AND contacts.deleted = 0 AND accounts_contacts.deleted = 0 ".
                            "    ) AND notes.parent_type = 'Contacts') ".
                            " )";

This works. At least I got 8 notes displayed and 8 notes shown in the pagination info.

But how can I use this with more complex queries? I would like to receive informations of more than one table?

I tried pumpihg up the select statement like this:

$return_array['select'] =   "SELECT notes.*, 'test' as contact_name";

But the System sees to ignore it.
Than I tried to get a hand on more than 1 table with changeing from and where part.

$return_array['from']   =   "FROM notes, contacts";

but i get the error thet unknown columns where used in the where part. so the from seems not to work on more than 1 Column?

Anyone any Ideas?