Query for new SubPanel

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

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

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

  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

[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

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

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:

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

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.

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

Probably not. At least I don’t know how.

You could probably reduce most of that complexity to a simpler query, and then work on the formatting details and some simple conditions when handling the output:

You can also change the data displayed with that technique - not just the formatting.

Hi

  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

You can’t use another table which is not the module’s table in select query directly.

Table Name depends on subpaneldefs “module” parameter.

Your custom table must have a relation with the Main Module Table and you need to write a join query between them.

  1. I wish that at the end of each column there was a sum of the data above.

You can achieve this by doing some customization with Logic Hook and Jquery

I hope it will help you.

Thanks

1 Like

ah ok, I understand, thanks… the problem is that I need to import a table with its data, but if I create a form, this creates a table with pre-existing fields that I cannot delete (even the ID that cannot be empty), then I had thought of creating a form, but of using, in the query, a different table that I had imported into the DB.
So from what I understand, I can’t do it, I still have to use the form table

@fab you really should be trying to do things “the SuiteCRM way”. Try to see how the app models data (including related tables) and do things as they are expected. This will make things a lot easier for you. External tables and strange queries will complicate your work.

I don’t really want to do strange and complex queries, but I need to manipulate the data in the way described above and if you have any suggestions for getting this simple, I listen to you

Make Activities a Module, make Pieces a module, create the correct relationship between them, import data appropriately, etc.

That is the general idea, I know sometimes it is not evident the best way to design database., I am just trying to give you some tips. :+1:

So I can’t work on tables that aren’t modules, right?
I just wanted a module that would then manipulate data from other tables.
As you say, I should create a module for each table, but then these will be visible as modules

1 Like

It’s not the only way to do it; you could do something more similar to what you were, trying, but you would have to get away from things based on definition files (vardefs, viewdefs, etc) and go for pure PHP, overriding view classes. In there you are free to do whatever.

But normally people who aren’t familiar with SuiteCRM programming find it harder to work from that sort of place.

Good luck!

Unfortunately I have to get that info, “go for pure PHP” is also ok, but I have to know how:
use the same files that SuiteCRM uses (vardef, viewdefs …), naturally customized? Or is it necessary to reconstruct a new path and structure for that module (hard work and I don’t even know how)?

I take this post for an update.
Let’s say that I reached the original goal, I put this solution of mine so that it could be useful to someone:
I created my module and used the procedure posted at the beginning, with subpaneldefs.php and customSubpanel.php, at this point I modified the default.php file:

$module_name = 'TEST';
$subpanel_layout = array(
    'top_buttons' => array(
        /* array('widget_class' => 'SubPanelTopCreateButton'),
        array('widget_class' => 'SubPanelTopSelectButton', 'popup_module' => $module_name), */
    ),

    'where' => '',

    'list_fields' => array(
        'date' => array(
            'vname' => 'Date',
            'widget_class' => 'SubPanelDetailViewLink',
            'width' => '45%',
        ),
		
		'n_piece_produced' => array(
            'vname' => 'N pieces produced',
			'name' => 'n_piece_produced',
			'widget_class' => 'SubPanelNewField',
			'custom_link_only' => true,
			'displayHeaderCell' => false, 
            'width' => '10%',
        ),	
'n_piece_discarded' => array(
            'vname' => 'N pieces discarded',
			'name' => 'n_piece_discarded',
			'widget_class' => 'SubPanelNewField',
			'custom_link_only' => true,
			'displayHeaderCell' => false, 
            'width' => '10%',
        ),	
);

and in the SubPanelNewField file there will be the query that returns the total I wanted.
Everything works as it should, the problem is that everything is very very slow. Apparently, for each row, the query is performed, also it is also performed for the number of fields present (in this case N pieces produced, N pieces discarded).
ideas to improve or should I change my approach?