Query for new SubPanel

SuiteCRM Forum - English Language Developer Collaboration
1

Hi,
I created a new subpanel in the module created in Studio, a new query is associated with this subpanel, everything works, but what I would like is:

  1. the query currently refers to the table of the created module, but instead I would like to use another table in the DB, I tried to replace the name of another table in the SELECT, but it gives me an error
  2. the list generated in the subpanel: when I click on a row, this row expands with “+” to show more info
  3. I wish that at the end of each column there was a sum of the data above. Example:
Date                      N pieces produced                 N pieces discarded                    N pieces delivered
2020-02-03                     100                                                    5                                                        95
2020-02-06                     150                                                    0                                                       150
2020-03-10                     120                                                  10                                                      110
TOT                                       370                                                  15                                                      355

these are my files

subpaneldefs.php

    <?php
    $layout_defs['TEST'] = array(
        'subpanel_setup' => array(

            'test_activity' => array(
                'order' => 30,
                'module' => 'TEST',
                'subpanel_name' => 'default',
                'get_subpanel_data' => 'function:get_query,
              
                'title_key' => 'LBL_TEST',
    			 'function_parameters' => array(
                    'import_function_file' => 'custom/modules/TEST/customSubpanel.php',
                    'id' => $this->_focus->id,
                    'return_as_array' => 'true'
                ),
                'top_buttons' => array(
                ),
            ),
    	),
    );
    ?>

customSubpanel.php

    <?php 
    function get_query($params) {	
    	global $app;
            $controller = $app->controller;
            $bean = $controller->bean;
    		
    	 $query = "
                SELECT *
                FROM test //I would like to replace this table with another, but not with another module
                WHERE test.id = '$bean->id'
                   ";
            return $query;
    } 
    ?>

Thanks for your help

2

nobody can help me? Please

3

When you are using direct SQL you can do any query whatsoever. you can test your query first in phpMyAdmin and then put it in the PHP code once you know it’s working.

If you are getting an SQL error please share the Query and the exact error and I can try to help you out.

4

Yes, that’s what I did … the query works in phpmyadmin.
As I said above, if I insert the module table on which I am operating, there is no problem, but if I want to query a different table, this is the error in suitecrm.log:

Error retrieving TEST list: Query Failed: (
SELECT *
FROM activity
WHERE activity.id = ‘33’
) ORDER BY test.id asc LIMIT 0,10: MySQL error 1054: Unknown column ‘test.id’ in ‘order clause’

5

Your “order by” clause is still referencing the “test” table, fix that.

6

Eh, the fact is that I have not entered any order clause, this is what the .log returned to me

7

Ah, sorry. I understand your problem now - that clause is getting added by the code handling what your function returns.

Try a different technique:

This should allow you to go around that limitation.

8

Yes, I had already tried it, but in this case the query doesn’t work at all, even if I use the module table goes wrong

9

This is the mistake if i use the method you suggested:

Mysqli_query failed.
03/17/20 16:35:36 [8180][1][FATAL] Error running count query for TEST List: Query Failed: (): MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘)’ at line 1
03/17/20 16:35:36 [8180][1][FATAL] Exception handling in C:\xampp\htdocs\SuiteCRM\include\MVC\Controller\SugarController.php:400

10

And what is the exact Query that corresponds to that error?

11

taking the code of the example you posted to me:

<?php
global $app;
        $controller = $app->controller;
        $bean = $controller->bean;
 
$return_array['select'] = " SELECT activity.*";
    $return_array['from'] = " FROM activity ";
    $return_array['where'] = " WHERE activity.id = '$bean->id'";

return $return_array;
?>
12

Can you please turn your log level up to DEBUG and then get the final generated query from your logs, after it executes?

13

I’m sorry, I didn’t understand what and how I should do it

14

  1. Admin / System Settings / Log level change to DEBUG

  2. Make the code run, by displaying the subpanel

  3. Check your logs for that query, search for " FROM activity ", for example

  4. Tell me what the entire query looks like.

Thanks

15

[DEBUG] Hook called: TEST2_ATTIVITA::before_retrieve
03/17/20 17:14:27 [8180][1][DEBUG] Retrieve TEST2_ATTIVITA : SELECT test2_attivita.* FROM test2_attivita WHERE test2_attivita.id = ‘100’ AND test2_attivita.deleted=0
03/17/20 17:14:27 [8180][1][DEBUG] Limit Query:SELECT test2_attivita.* FROM test2_attivita WHERE test2_attivita.id = ‘100’ AND test2_attivita.deleted=0 Start: 0 count: 1
03/17/20 17:14:27 [8180][1][INFO] Query:SELECT test2_attivita.* FROM test2_attivita WHERE test2_attivita.id = ‘100’ AND test2_attivita.deleted=0 LIMIT 0,1
03/17/20 17:14:27 [8180][1][INFO] Query Execution Time:0.00078392028808594

16

do not pay attention to the table name, the name is TEST

17

But I don’t think any of those queries matches this error here :point_up:

You don’t even have a COUNT, or a parenthesis…

:question:

18

The query is the one, without count … also, trying to launch the query again, ORDER BY TEST.ID (name of the module) is proposed, while in the query there is the name of the table (activity):

03/17/20 18:16:17 [8180][1][DEBUG] process_union_list_query: (Array) ORDER BY TEST.id asc

19

Sorry, I’m lost. I don’t understand what’s happening there.

I suggest that you try to get the example from that site working exactly as it is, just to confirm you get it right, and it works. Then you start changing it bit by bit until you get the effect you want.

20

actually I started from that, as it was, but it didn’t work, then I used what I posted to you at the beginning and it worked…
Sorry,
I would like to ask you, is it possible to use a complex query and make it appear in a subpanel? Of the type:
select a.id, a.date, a.type
when 1 then ‘first’
when 2 then ‘second’
end as type,
(select pg.n_pc where type = 1) as qty_first,
(select pg.n_pc where type = 2) as qty_second,
from pieces as pg, activity as a
where
(pg.id = 33) and a.id = pg.id

and how to make it appear in the subpanel with custom header:

Date First Second