Query with join

SuiteCRM Forum - English Language Developer Collaboration
#1

Hi,
I created a module A in Studio which must contain the result of a query on two other modules B and C
in view.list of module A I entered the query:

    $db = DBManagerFactory :: getInstance();
    $query = "SELECT B.data, C.name FROM B INNER JOIN C ON B.id = C.id_b";
    $Result = $ db-> query($query);

but the query loops. If I do a simple query, like
SELECT data FROM B
it is OK.
Where am I wrong? Is it right to enter the query in view.list? It’s okay to use
DBManagerFactory and $ db-> query ($ query)?

Thanks

#2

I replaced

$db = DBManagerFactory :: getInstance();

con

global $db;

the result does not change.
At least know if it is the correct way? The right way to proceed? Please.

#3

Do you really have all those spaces between $ and the variable names? And around the ::?

That way it won’t work. But my guess is that you’re using Google Translate and that adds the spaces.

When you assign a variable you can inspect it’s value to know if the assignment worked. Use a debugger or output it’s value onto the screen or into the logs.

#4

No no, the spaces have been added by google translate.
The problem is the query and the inner join, it takes a long time, continues to run. For this reason I was wondering if the way of acting is correct, is the use of
$ result = $ db-> query ($ query);
is the fact of having placed it in
view.list.php

#5

Are using an IDE? Debugging through the code really helps.

If not, then use the log to output some information. This will help you know how often your query is running, for example. I don’t think single query would take long, you probably have some problem that it is getting run for every row or even for every field in the list view.

#6

No, I don’t use an IDE. The Suitecrm log doesn’t give me any errors

#7

I meant that you use a PHP command to log your own message. If you don’t use an IDE, you need some way to have visibility into what it going on.

I’d love to help better but I think you need to work on your developer and PHP skills - these are not skills specific to SuiteCRM work.

Good luck!

#8

I improved the situation by indexing the db, but now I would like the data read by the SELECT to be stored in the form:

$ result = $ db-> query ($ query);
while ($ row = $ db-> fetchByAssoc ($ result)) {
 bean = BeanFactory :: newBean ( "qty_w_works"); // DESTINATION MODULE
   $ bean-> id = $ row [$ id];
   $ bean-> id_activity = $ row ['id_activity'];
   $ bean-> date = $ row ['date'];
   $ bean-> quantity1 = $ row ['quantity1'];
   $ bean-> quantity2 = $ row ['quantity2'];
   $ bean-> quantity3 = $ row ['quantity3'];
   $ Bean-> save ();
}

but it gives me this error:
Warning: Creating default object from empty value
Call to undefined method stdClass :: save ()

which is a known problem, we recommend that you enter:

if (! isset ($ module-> mbvardefs) || is_null ($ module-> mbvardefs)) {
                $ module-> mbvardefs = new stdClass ();
 }

but in my code it is already inserted.
So I disabled the display_errors in php.ini, but no saving is made and no error is shown on the screen, not even in suitecrm.log. The query is executed, because if I do echo $ row [‘id_activity’] inside the loop, it is shown

Some idea?

#9

Ok, sorry, the error was in the module name