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
