👩‍💻 Check out our latest job positions here 👈 or send us a PM for more info.

How to load relationship AOS_Quotes and AOS_PRODUCT_QUOTES

Hello all

I am trying to build a custom function to obtain the id’s of the lines of products in an AOS_QUOTE record.

The function I am creating is

public static function QuoteLines($quote)
{
// Load relationshiop Quote<->aos_product_quote
$quote->load_relationship(‘aos_quotes_aos_product_quotes’);
$lineas_ids = $quote->aos_quotes_aos_product_quotes->get();

    if ($lines_ids!=null) {return $lines_ids;}
    else return null;
}

However it seems that I am introducing the name of the relationship incorrectly. I got it from Studio, but dont know if it is the right one.

Any ideas about what can I change to fix this?

Thanks a lot!

jlabuelo,

Not sure if this is what your needing but when I needed to connect the line items back to quotes and accounts I wrote this method to get retrieve all line items in a quote. This method is called in the logic hooks for AOS_Quotes. There are some custom fields in this code that you may note have one specifically is bm_service_id_c.

     /**
     * Get quotes Product Detail
     */
    public function getQuoteLineItems($id)
    {
        $quoteLineItems = array();
        $query = "SELECT " .
            " q.`id` AS `quotes_id`, q.`name` AS `quotes_name`, q.`billing_account_id` AS `quotes_billing_account_id`, q.`billing_address_street` AS `quotes_billing_address_street`, q.`billing_address_city` AS `quotes_billing_address_city`, q.`billing_address_state` AS `quotes_billing_address_state`, q.`billing_address_postalcode` AS `quotes_billing_address_postalcode`, q.`billing_address_country` AS `quotes_billing_address_country`, q.`shipping_address_street` AS `quotes_shipping_address_street`, q.`shipping_address_city` AS `quotes_shipping_address_city`, q.`shipping_address_state` AS `quotes_shipping_address_state`, q.`shipping_address_postalcode` AS `quotes_shipping_address_postalcode`, q.`shipping_address_country` AS `quotes_shipping_address_country`, q.`expiration` AS `quotes_expiration`, q.`number` AS `quotes_number`, q.`opportunity_id` as `quotes_opportunity_id`, q.`total_amt` AS `quotes_total_amt`, q.`total_amt_usdollar` AS `quotes_total_amt_usdollar`, q.`subtotal_amount` AS `quotes_subtotal_amount`, q.`subtotal_amount_usdollar` AS `quotes_subtotal_amount_usdollar`, q.`discount_amount` AS `quotes_discount_amount`, q.`discount_amount_usdollar` AS `quotes_discount_amount_usdollar`, q.`tax_amount` AS `quotes_tax_amount`, q.`tax_amount_usdollar` AS `quotes_tax_amount_usdollar`, q.`shipping_amount` AS `quotes_shipping_amount`, q.`shipping_amount_usdollar` AS `quotes_shipping_amount_usdollar`, q.`shipping_tax` AS `quotes_shipping_tax`, q.`shipping_tax_amt` AS `quotes_shipping_tax_amt`, q.`shipping_tax_amt_usdollar` AS `quotes_shipping_tax_amt_usdollar`, q.`total_amount` AS `quotes_total_amount`, q.`total_amount_usdollar` AS `quotes_total_amount_usdollar`, q.`currency_id` AS `quotes_currency_id`, q.`stage` AS `quotes_stage`, q.`term` AS `quotes_term`, q.`terms_c` AS `quotes_terms_c`, q.`approval_status` AS `quotes_approval_status`, q.`invoice_status` AS `quotes_invoice_status`, q.`subtotal_tax_amount` AS `quotes_subtotal_tax_amount`, q.`subtotal_tax_amount_usdollar` AS `quotes_subtotal_tax_amount_usdollar`," .
            " pq.`id` AS `product_quotes_id`, pq.`name` AS `product_quotes_name`, pq.`part_number` AS `product_quotes_part_number`, pq.`item_description` AS `product_quotes_item_description`, pq.`number` AS `product_quotes_number`, pq.`product_qty` AS `product_quotes_product_qty`, pq.`product_cost_price` AS `product_quotes_product_cost_price`, pq.`product_cost_price_usdollar` AS `product_quotes_product_cost_price_usdollar`, pq.`product_list_price` AS `product_quotes_product_list_price`, pq.`product_list_price_usdollar` AS `product_quotes_product_list_price_usdollar`, pq.`product_discount` AS `product_quotes_product_discount`, pq.`product_discount_usdollar` AS `product_quotes_product_discount_usdollar`, pq.`product_discount_amount` AS `product_quotes_product_discount_amount`, pq.`product_discount_amount_usdollar` AS `product_quotes_product_discount_amount_usdollar`, pq.`discount` AS `product_quotes_discount`, pq.`product_unit_price` AS `product_quotes_product_unit_price`, pq.`product_unit_price_usdollar` AS `product_quotes_product_unit_price_usdollar`, pq.`vat_amt` AS `product_quotes_vat_amt`, pq.`vat_amt_usdollar` AS `product_quotes_vat_amt_usdollar`, pq.`product_total_price` AS `product_quotes_product_total_price`, pq.`product_total_price_usdollar` AS `product_quotes_product_total_price_usdollar`, pq.`vat` AS `product_quotes_vat`, pq.`parent_type` AS `product_quotes_parent_type`, pq.`parent_id` AS `product_quotes_parent_id`, pq.`product_id` AS `product_quotes_product_id`, pq.`group_id` AS `product_quotes_group_id`," .
            " p.`id` AS product_id, p.`name` AS `product_name`, p.`part_number` AS `product_part_number`, p.`category` AS `product_category`, p.`type` AS `product_type`, p.`cost` AS `product_cost`, p.`cost_usdollar` AS `product_cost_usdollar`, p.`currency_id` AS `product_currency_id`, p.`price` AS `product_price`, p.`price_usdollar` AS `product_price_usdollar`, p.`aos_product_category_id` AS `product_aos_product_category_id`," .
            " c.`bm_serviceid_c`," .
            " a.`id` AS `account_id`, a.`name` AS `account_name`,  a.`description` AS `account_description`, a.`phone_fax` AS `account_phone_fax`, a.`billing_address_street` AS `account_billing_address_street`, a.`billing_address_city` AS `account_billing_address_city`, a.`billing_address_state` AS `account_billing_address_state`, a.`billing_address_postalcode` AS `account_billing_addess_postalcode`, a.`billing_address_country` AS `account_billing_address_country`, a.`rating` AS `account_rating`, a.`phone_office` AS `account_phone_office`, a.`phone_alternate` AS `account_phone_alternate`, a.`website` AS `account_website`, a.`ownership` AS `account_ownership`, a.`employees` AS `account_employees`, a.`shipping_address_street` AS `account_shipping_address_street`, a.`shipping_address_city` AS `account_shipping_address_city`, a.`shipping_address_state` AS `account_shipping_address_state`, a.`shipping_address_postalcode` AS `account_shipping_address_postalcode`, a.`shipping_address_country` AS `account_shipping_address_country`, a.`parent_id` AS `account_parent_id`, a.`sic_code` AS `account_sic_code`, a.`campaign_id` AS `account_campaign_id`," .
            " ac.`invday_c` AS `account_invday_c`" .
            " FROM aos_quotes q" .
            " LEFT JOIN aos_products_quotes pq ON pq.parent_id=q.id" .
            " LEFT JOIN aos_products p ON p.id=pq.product_id" .
            " LEFT JOIN aos_products_quotes_cstm c ON c.id_c=pq.id" .
            " LEFT JOIN accounts a ON a.id=q.billing_account_id" .
            " LEFT JOIN accounts_cstm ac ON ac.id_c=a.id" .
            " WHERE q.id='{$id}'";
      
        $result = $GLOBALS['db']->query($query);
        while ($row = $GLOBALS['db']->fetchByAssoc($result)) {
            $quoteLineItems[] = $row;
        }
        return $quoteLineItems;
    }

Hope this helps.

Tony

Hello Tony

Thanks a lot for your reply. Yes that is a perfect workaround, but I was trying to avoid using SQL directly, that is why I am trying to load the relationship the way I showed in the case. I know that the issue is related to the right “name” of the relationship I am using (which I think is not the correct one). If I dong get to find the right name to proceed this way I will use your suggestion for sure :-).

Thanks once again!!

Hi Jlabuelo,

You will need to use the link field name not the relationship name in the load_relationship and for getting IDs. Following is the updated code which will server your purpose:

$quote = BeanFactory::getBean(‘AOS_Quotes’,$q_id);
$quote->load_relationship(“aos_products_quotes”);

$lineas_ids = $quote->aos_products_quotes->get();

$line_item = BeanFactory::getBean(‘AOS_Products_Quotes’,$q_id);

print_r($lineas_ids);