Custom subpanels -> but suite changes the given sql queries

[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?