AnsweredAssumed Answered

what is the suggested way of getting linked beans with conditions???

Question asked by Ron Lesh on Jul 24, 2016
Latest reply on Oct 30, 2016 by Ron Lesh

Hi all,

 

I have been always having this question since the early days when i start use sugar.

It is suggested that we should use sugar bean instead of SQL all the time. However, i do find sometime use SQL is way faster than use sugar bean in case that there are a very big numbers of records in the one to many relationships.

 

e.g. in my databse each client may have a lot of rows of linked products which status could be active or inactive, type ids could be very different and even more conditions. I want to find out the active, type A products for a particular client.

 

as far as i know, there are 3 way using sugar bean:

Option 1:  (* clean but it takes a lot of resource when the script need to be called often)

$focus = new Account();

$focus->retrieve('my record id');

$focus->load_relationship('products');

 

Option 2: (* a little better but i can only use one condition here as far as i know)

$focus = new Account();

$options = array(

                'lhs_field' => 'product_type',

                'operator' => ' = ',

                'rhs_value' => $type_value,

                );

$products = $focus->get_linked_beans('products', 'Product', array(), 0, -1, 0, $options);

 

Option 3: (*it is told that get_full_list is slow and may trigger process record logic hook)

$product = BeanFactory::newBean("Products");

            //base active products and BGL Query and Portal of BGL

            $where = "products.account_id = '".$client_id."' and "

                    . "products.status = 'active' and "

                    . "type_id = '132456789test987654321'";

            $client_base_products = $product->get_full_list('',$where);

 

and 4th way of using SQL:

Option 4:

Another way of use SQL is to use dbmanager:

$sql = "SELECT * FROM products WHERE deleted = 0 and status = 'active' and type_id='xxxxxxxxxx'";
$result = $GLOBALS['db']->query($sql);
while($row = $GLOBALS['db']->fetchByAssoc($result) )
{

.....

}

 

 

Can anyone share with me which way is the sugar suggested way of achieving this purpose? thanks a lot. Alex Nassi

 

 

Outcomes